Re: Making a unique constraint deferrable?

On 02/28/2017 08:50 AM, Ivan Voras 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?

https://www.postgresql.org/docs/9.6/static/sql-altertable.html

"ALTER CONSTRAINT

This form alters the attributes of a constraint that was previously
created. Currently only foreign key constraints may be altered.
"

>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com