Re: [GENERAL] 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.klaver@aklaver.com