Thread: table constraint on two columns
Postgres 9.2.4. I have two columns, approved and comments. Approved is a boolean with no default value and comments is a character varying (255) and nullable. I am trying to create a constraint that will not allow a row to be entered if approved is set to false and comments is null. This does not work. yada, yada, yada... add constraint "chk_comments' check (approved = false and comments is not null). The constraint is successfully added, but does not work as I expected. -- View this message in context: http://postgresql.1045698.n5.nabble.com/table-constraint-on-two-columns-tp5764645.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On 07/22/2013 04:05 PM, ldrlj1 wrote: > Postgres 9.2.4. > > I have two columns, approved and comments. Approved is a boolean with no > default value and comments is a character varying (255) and nullable. > > I am trying to create a constraint that will not allow a row to be entered > if approved is set to false and comments is null. CHECK constraints work on positives, so restate your condition that way. A row is permissible if approved is true or the comments are not null, correct? So... ...add constraint chk_comments (approved or comments is not null)... > This does not work. yada, yada, yada... add constraint "chk_comments' check > (approved = false and comments is not null). The constraint is successfully > added, but does not work as I expected. That's not the same check as what you described.
On 07/22/2013 07:16 AM, Vik Fearing wrote: > On 07/22/2013 04:05 PM, ldrlj1 wrote: >> Postgres 9.2.4. >> >> I have two columns, approved and comments. Approved is a boolean with no >> default value and comments is a character varying (255) and nullable. >> >> I am trying to create a constraint that will not allow a row to be entered >> if approved is set to false and comments is null. > > CHECK constraints work on positives, so restate your condition that > way. A row is permissible if approved is true or the comments are not > null, correct? So... > > ...add constraint chk_comments (approved or comments is not null)... > >> This does not work. yada, yada, yada... add constraint "chk_comments' check >> (approved = false and comments is not null). The constraint is successfully >> added, but does not work as I expected. > > That's not the same check as what you described. An additional comment, did you put the check constraint on a column or the table? From the docs: http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html: .. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns... > > -- Adrian Klaver adrian.klaver@gmail.com
DOH! Complete brain fart. Thank you for re-educating me :)! Worked like a charm. -- View this message in context: http://postgresql.1045698.n5.nabble.com/table-constraint-on-two-columns-tp5764645p5764649.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.