Making a unique constraint deferrable?
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?
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
constraintThe 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras <ivoras@gmail.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
constraintThe 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.
On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras <ivoras@gmail.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
constraintThe 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.
On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras <ivoras@gmail.com> wrote:
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
A more clear error message would be:
EROR: cannot alter non-foreign key constraint "foo_a_b_key" of relation
"foo"
Though I'm not sure how that meshes with the error message style guides...
David J.
On 28 February 2017 at 18:03, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras <ivoras@gmail.com> wrote:
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
constraintA more clear error message would be:
EROR: cannot alter non-foreign key constraint "foo_a_b_key" of relation
"foo"Though I'm not sure how that meshes with the error message style guides...
Any idea what underlying technical reason prohibits marking non-fk
constraints as deferrable?
On Tue, Feb 28, 2017 at 10:05 AM, Ivan Voras <ivoras@gmail.com> wrote:
On 28 February 2017 at 18:03, David G. Johnston <
david.g.johnston@gmail.com> wrote:On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras <ivoras@gmail.com> wrote:
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
constraintA more clear error message would be:
EROR: cannot alter non-foreign key constraint "foo_a_b_key" of relation
"foo"Though I'm not sure how that meshes with the error message style guides...
Any idea what underlying technical reason prohibits marking non-fk
constraints as deferrable?
Not off hand - but a unique (and PK by extension) constraint is implemented
by creating an underlying unique index and the ALTER CONSTRAINT command
probably doesn't want to go messing around with that. While the columns
involved in a FK constraint can also be indexed the two concepts are not
physically linked together.
David J.