Re: Making a unique constraint deferrable?

On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras <ivoras(at)gmail(dot)com> wrote:

> Hello,
>
> If I'm interpreting the manual correctly, this should work:
>
> ivoras=# create table foo(a integer, b integer, unique(a,b));
> CREATE TABLE
> ivoras=# \d foo
> Table "public.foo"
> Column | Type | Modifiers
> --------+---------+-----------
> a | integer |
> b | integer |
> Indexes:
> "foo_a_b_key" UNIQUE CONSTRAINT, btree (a, b)
>
> ivoras=# insert into foo(a,b) values(1,2);
> INSERT 0 1
> ivoras=# insert into foo(a,b) values(1,2);
> ERROR: duplicate key value violates unique constraint "foo_a_b_key"
> DETAIL: Key (a, b)=(1, 2) already exists.
> ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
> ERROR: constraint "foo_a_b_key" of relation "foo" is not a foreign key
> constraint
>
> The manual says this for SET CONSTRAINTS:
>
> Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE
> constraints are affected by this setting. NOT NULL and CHECK constraints
> are always checked immediately when a row is inserted or modified (not at
> the end of the statement). Uniqueness and exclusion constraints that have
> not been declared DEFERRABLE are also checked immediately.
>
>
> I'm puzzled by the "...is not a foreign key constraint" error message.
> Doesn't "deferrable" also work on unique constraints?
>
>
​The error is pointing out the documented behavior that only FK constraints
can be altered.

​https://www.postgresql.org/docs/9.6/static/sql-altertable.html (see ALTER
CONSTRAINT note)

So, while you can make a PK constraint deferrable it must be done as part
of the initial constraint construction and not via ALTER CONSTRAINT.

David J.