Postgresql – How to add a constraint to a table using a partial index

alter-tableconstraintindexpostgresqlpostgresql-9.6

I have a simple table:

create table item (
  lan      text not null,
  disabled date
);

My aim is to ensure uniqueness for the lan column over all rows, where disabled is null.

I created a partial index:

create unique index lan_idx on item (lan) where disabled is not null;

Now I tried to add the index to the table, but I am struggling with the syntax. (I took the code from this tutorial)

alter table item add constraint lan_idx using index lan_idx;

This throws an syntax error before "using".

I also tried the following:

alter table item add constraint lan_idx unique using index lan_idx;

But this throws the error, that a unique constraint can not be created with a partial index.

Can anybody tell me what I did wrong?

Best Answer

You can't define a unique (or primary key) constraint based on a partial index.

However, to enforce uniqueness of the lan column for all non-deleted rows, the partial unique index you already have is enough.

There is no need to add a constraint.

Related Solutions

Mysql – Creating a partial unique constraint for MySQL

The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'Cannot insert this item due to unique constraint', 
         MYSQL_ERRNO = 1001;
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

But pre 5.5, you would do this something like this:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SET NEW='Error: Cannot insert this item due to unique constraint';
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

Postgresql 9.3: Add Unique constraint using an existing unique index

You don't specify the column for the unique constraint. That's not necessary because the column list is defined through the index:

ALTER TABLE package ADD CONSTRAINT 
     unique_package_id UNIQUE USING INDEX package_tmp_id_idx;

There is an example for that in the manual:
http://www.postgresql.org/docs/current/static/sql-altertable.html

You need to scroll down to the end of the page

Best Answer

Related Solutions

Mysql – Creating a partial unique constraint for MySQL

Postgresql 9.3: Add Unique constraint using an existing unique index

Related Question