Custom made constraint in PostgreSQL

Question

I have a table with the following fields and sample records:

sample_date  customer   active
 25/12/2010   12356       N
 01/12/2012   12356       N
 05/11/2013   12356       N
 05/07/2014   12356       Y

I want to enforce 2 constraints:

Just one "ACTIVE" set to "Y" per CUSTOMER (but, several "N" are allowed)
Just one SAMPLE_DATE per CUSTOMER

So that it refuses to save any other line for this customer with a ACTIVE value of "Y" unless the old one is first set to 'N'.

Any idea on how to achieve that?

Thanks a lot!

Note: how about boolean for your active column? postgresql.org/docs/current/static/datatype-boolean.html — pozs
– pozs, Commented Jul 28, 2014 at 13:41

score 2 · Accepted Answer · 2014-07-28 13:46:17Z

2

Just one "ACTIVE" set to "Y" per CUSTOMER (but, several "N" are allowed)

create unique index ix_only_one_active 
   on the_table (customer) 
   where active = 'Y';

This will allow any number of rows to be inserted for a customer with an active flag other than 'Y'. You might want to also add a check constraint on that column

Just one SAMPLE_DATE per CUSTOMER

create unique index ix_unique_sample_date_customer 
     on the_table (customer, sample_date);

^{P.S.: I agree with pozs' comment: it would be better/cleaner to define the active column as boolean}

edited Jul 28, 2014 at 13:46

answered Jul 28, 2014 at 12:54

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Fulano da Silva Sauro Over a year ago

Perfect! Thanks! I use the field as boolean, I just didn't show in the sample to keep it simple. But the 2 different constraints worked perfectly to what I wanted to do! Thanks a lot!

Collectives™ on Stack Overflow

Custom made constraint in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related