Обсуждение: Disable unique constraint in Postgres
Hi All,
We have an alter command to disable any constraints in the oracle db. Similarly do we have any command to disable the UNIQUE constraint in postgres. Most of the postgres db forum suggests dropping the UNIQUE constraint. But, I don't want to drop the constraint here instead just disable the unique constraint.
Need your inputs on the same.
Regards,
Nikhil Ingale
On Fri, 2022-11-25 at 12:48 +0530, Nikhil Ingale wrote: > We have an alter command to disable any constraints in the oracle db. Similarly do we have any > command to disable the UNIQUE constraint in postgres. Most of the postgres db forum suggests > dropping the UNIQUE constraint. But, I don't want to drop the constraint here instead just > disable the unique constraint. > > Need your inputs on the same. The db forums were right: you cannot disable a unique constraint in PostgreSQL. Yours, Laurenz Albe
On Fri, Nov 25, 2022 at 08:46:22AM +0100, Laurenz Albe wrote:
> On Fri, 2022-11-25 at 12:48 +0530, Nikhil Ingale wrote:
> > We have an alter command to disable any constraints in the oracle db. Similarly do we have any
> > command to disable the UNIQUE constraint in postgres. Most of the postgres db forum suggests
> > dropping the UNIQUE constraint. But, I don't want to drop the constraint here instead just
> > disable the unique constraint.
> >
> > Need your inputs on the same.
>
> The db forums were right: you cannot disable a unique constraint in PostgreSQL.
Hi,
hope, that I've understood the problem correctly...
At least since v13.x dropping a unique constraint seems to work while
dropping the related index:
-- two constraints added:
np=# create table a (i integer unique constraint hu check(i<100));
CREATE TABLE
np=# \d+ a
Table "public.a"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
Indexes:
"a_i_key" UNIQUE CONSTRAINT, btree (i)
Check constraints:
"hu" CHECK (i < 100)
Access method: heap
np=# insert into a values(1);
INSERT 0 1
np=# insert into a values(1);
ERROR: duplicate key value violates unique constraint "a_i_key"
DETAIL: Key (i)=(1) already exists.
np=# insert into a values(2);
INSERT 0 1
np=# insert into a values(101);
ERROR: new row for relation "a" violates check constraint "hu"
DETAIL: Failing row contains (101).
-- So far, so good. It works as expected.
-- Now:
np=# alter table a drop constraint a_i_key;
ALTER TABLE
np=# \d+ a
Table "public.a"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
Check constraints:
"hu" CHECK (i < 100)
Access method: heap
-- only constraint i<100 left:
np=# insert into a values(1);
INSERT 0 1
np=# insert into a values(101);
ERROR: new row for relation "a" violates check constraint "hu"
DETAIL: Failing row contains (101).
np=# select * from a;
i
---
1
2
1
-- have to check that against different postgresql versions and docs...
cheers
/np
>
> Yours,
> Laurenz Albe
>
Hi Nikhil,
Important point is why you want to disable a unique constraint.
- If you want to add some duplicate rows into a table, you try to do something fundamentally wrong.
- If you want to violate a constraint temporarily or postpone validity checking of a constraint, it is possible. It is called DEFERRABLE. You can delay validation of a constraint until the end of the transaction or until the end of the statement execution. I'm adding links to related documents for more details.
Best regards.
Samed YILDIRIM
On Fri, 25 Nov 2022 at 09:19, Nikhil Ingale <niks.bgm@gmail.com> wrote:
Hi All,We have an alter command to disable any constraints in the oracle db. Similarly do we have any command to disable the UNIQUE constraint in postgres. Most of the postgres db forum suggests dropping the UNIQUE constraint. But, I don't want to drop the constraint here instead just disable the unique constraint.Need your inputs on the same.Regards,Nikhil Ingale
> On Nov 27, 2022, at 9:51 AM, Samed YILDIRIM <samed@reddoc.net> wrote: > > Important point is why you want to disable a unique constraint. > • If you want to add some duplicate rows into a table, you try to do something fundamentally wrong. Even more so: why is disabling superior to dropping??? It is at most an extremely minor convenience to be able to re-enable it without having to re-create it, in that you can,presumably, just re-enable by name without specifying the constraint details. In other words, in my opinion, it is avirtually worthless feature. (Typical "enterprise software" feature creep.)
On 11/27/22 10:58, Scott Ribe wrote:
Being an "enterprise dba", IMNSHO "disable constraint" (and more specifically "disable index") is a great feature for maintaining tables. For example, if you need to purge a lot of records into a table, disable all indices except the index supporting your WHERE CLAUSE, delete the data, and then re-enable the indices. Bonus points if the REENABLE commands can be done in parallel.
Sure, you can dig around for all of the CREATE INDEX statements, but that leads to possible errors: "oops, forgot to recreate one of them", or "typo caused one to fail". OTOH, DISABLE INDEX and REENABLE INDEX are idiot-proof, and can be automated.
On Nov 27, 2022, at 9:51 AM, Samed YILDIRIM <samed@reddoc.net> wrote: Important point is why you want to disable a unique constraint. • If you want to add some duplicate rows into a table, you try to do something fundamentally wrong.Even more so: why is disabling superior to dropping??? It is at most an extremely minor convenience to be able to re-enable it without having to re-create it, in that you can, presumably, just re-enable by name without specifying the constraint details. In other words, in my opinion, it is a virtually worthless feature. (Typical "enterprise software" feature creep.)
Being an "enterprise dba", IMNSHO "disable constraint" (and more specifically "disable index") is a great feature for maintaining tables. For example, if you need to purge a lot of records into a table, disable all indices except the index supporting your WHERE CLAUSE, delete the data, and then re-enable the indices. Bonus points if the REENABLE commands can be done in parallel.
Sure, you can dig around for all of the CREATE INDEX statements, but that leads to possible errors: "oops, forgot to recreate one of them", or "typo caused one to fail". OTOH, DISABLE INDEX and REENABLE INDEX are idiot-proof, and can be automated.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On 11/27/22 16:23, Ron wrote:
Being an "enterprise dba", IMNSHO "disable constraint" (and more specifically "disable index") is a great feature for maintaining tables. For example, if you need to purge a lot of records into a table, disable all indices except the index supporting your WHERE CLAUSE, delete the data, and then re-enable the indices. Bonus points if the REENABLE commands can be done in parallel.
Sure, you can dig around for all of the CREATE INDEX statements, but that leads to possible errors: "oops, forgot to recreate one of them", or "typo caused one to fail". OTOH, DISABLE INDEX and REENABLE INDEX are idiot-proof, and can be automated.--
Angular momentum makes the world go 'round.
You are aware that in Oracle "ALTER TABLE ENABLE CONSTRAINT" rebuilds the index used to enforce the constraint? You will not save any time by disabling and re-enabling constraints.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
On 11/27/22 15:56, Mladen Gogala wrote:
Eliminating mistakes and reducing DBA work are the point of DISABLE and ENABLE INDEX, not saving clock time during the maintenance window.
On 11/27/22 16:23, Ron wrote:
Being an "enterprise dba", IMNSHO "disable constraint" (and more specifically "disable index") is a great feature for maintaining tables. For example, if you need to purge a lot of records into a table, disable all indices except the index supporting your WHERE CLAUSE, delete the data, and then re-enable the indices. Bonus points if the REENABLE commands can be done in parallel.
Sure, you can dig around for all of the CREATE INDEX statements, but that leads to possible errors: "oops, forgot to recreate one of them", or "typo caused one to fail". OTOH, DISABLE INDEX and REENABLE INDEX are idiot-proof, and can be automated.--
Angular momentum makes the world go 'round.
You are aware that in Oracle "ALTER TABLE ENABLE CONSTRAINT" rebuilds the index used to enforce the constraint? You will not save any time by disabling and re-enabling constraints.
Eliminating mistakes and reducing DBA work are the point of DISABLE and ENABLE INDEX, not saving clock time during the maintenance window.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Hi Ron,
Dropping and creating indexing can also be automated, and it will prevent any kind of human error caused by typing errors or forgetting one of indexes. I support the idea of things that make our life easier. However, I don't think this is one of the nice-to-have features.
To get back to the topic, if you want to achieve such a task and prevent human error on definition of indexes or typing error or something like that, there is a cool function in Postgresql that returns index's create statement :)
postgres=# create table t1 (id serial primary key, c1 int);
CREATE TABLE
postgres=*# create index t1_test_idx on t1 (c1);
CREATE INDEX
postgres=*# select pg_get_indexdef('t1_test_idx'::regclass);
pg_get_indexdef
--------------------------------------------------------
CREATE INDEX t1_test_idx ON public.t1 USING btree (c1)
(1 row)
CREATE TABLE
postgres=*# create index t1_test_idx on t1 (c1);
CREATE INDEX
postgres=*# select pg_get_indexdef('t1_test_idx'::regclass);
pg_get_indexdef
--------------------------------------------------------
CREATE INDEX t1_test_idx ON public.t1 USING btree (c1)
(1 row)
Best regards.
Samed YILDIRIM
On Sun, 27 Nov 2022 at 23:23, Ron <ronljohnsonjr@gmail.com> wrote:
On 11/27/22 10:58, Scott Ribe wrote:On Nov 27, 2022, at 9:51 AM, Samed YILDIRIM <samed@reddoc.net> wrote: Important point is why you want to disable a unique constraint. • If you want to add some duplicate rows into a table, you try to do something fundamentally wrong.Even more so: why is disabling superior to dropping??? It is at most an extremely minor convenience to be able to re-enable it without having to re-create it, in that you can, presumably, just re-enable by name without specifying the constraint details. In other words, in my opinion, it is a virtually worthless feature. (Typical "enterprise software" feature creep.)
Being an "enterprise dba", IMNSHO "disable constraint" (and more specifically "disable index") is a great feature for maintaining tables. For example, if you need to purge a lot of records into a table, disable all indices except the index supporting your WHERE CLAUSE, delete the data, and then re-enable the indices. Bonus points if the REENABLE commands can be done in parallel.
Sure, you can dig around for all of the CREATE INDEX statements, but that leads to possible errors: "oops, forgot to recreate one of them", or "typo caused one to fail". OTOH, DISABLE INDEX and REENABLE INDEX are idiot-proof, and can be automated.--
Angular momentum makes the world go 'round.