CODEWITHSUNDEEP
sqlpostgresql
UlfR
UlfR

Reputation: 4395

Howto create limited unique constraint

I have a table similar to this:

CREATE TABLE members (
  member_id SERIAL PRIMARY KEY,
  email text,
  archived boolean DEFAULT FALSE,
  CONSTRAINT members_email_key UNIQUE(email)
);

I need the unique-constraint to consider only the active members (that are note archived: archived=False). So the following would be valid:

insert into members(name, email) values('ulf','[email protected]');
insert into members(name, email) values('foo','[email protected]');
insert into members(name, email) values('baz','[email protected]');
update members set archived = True where name = 'ulf';
insert into members(name, email) values('Newulf','[email protected]');

But this should fail:

insert into members(name, email) values('foo','[email protected]');
insert into members(name, email) values('Anotherfoo','[email protected]');

So I know I need to remove the existing constraint like this:

ALTER TABLE members DROP CONSTRAINT members_email_key;

But how do I create a new constraint that does what I want? I have been looking at the EXCLUDE-clous but do not fully understand how to apply it. I have tried some variations of this without success:

ALTER TABLE members ADD CONSTRAINT email_for_valid_members EXCLUDE USING gist (email with =, ...

Documentation about EXCLUDE:

I'm using postgresql 9.6.

Upvotes: 0

Views: 21

Answers (1)

user330315
user330315

Reputation:

Create a partial unique index:

create unique index only_one_active_email
   on members(email)
where not archived;

Upvotes: 2

Related Questions