CODEWITHSUNDEEP
postgresqlindexingconstraintsprimary-keyunique
Fulano da Silva Sauro
Fulano da Silva Sauro

Reputation: 51

Custom made constraint in PostgreSQL

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:

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!

Upvotes: 0

Views: 46

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions