I won't say much nonsense, let's just look at the examples~
postgres=# create table t1(a int primary key,b text,c date);
CREATE TABLE
postgres=# create table t2(a int primary key,b int references t1(a),c text);
CREATE TABLE
postgres=# insert into t1 (a,b,c) values(1,'aa',now());
INSERT 0 1
postgres=# insert into t1 (a,b,c) values(2,'bb',now());
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (1,1,'aa');
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (2,2,'aa');
INSERT 0 1
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | text | | |
c | date | | |
Indexes:
"t1_pkey" PRIMARY KEY, btree (a)
Referenced by:
TABLE "t2" CONSTRAINT "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
postgres=# \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | |
c | text | | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
"t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
postgres=#
Suppose we want to load some data into the table through a script. Because we don't know the order of loading in the script, we decided to disable the foreign key constraint on table t2 and load the foreign key constraint after the data is loaded:
postgres=# alter table t2 disable trigger all; ALTER TABLE postgres=#
It may seem a bit strange here, but it does disable foreign key constraints. If there are other foreign key constraints, of course it will be disabled.
Let’s take a look at Table t2:
postgres=# \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | |
c | text | | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
"t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
Disabled internal triggers:
"RI_ConstraintTrigger_c_75213" AFTER INSERT ON t2 FROM t1 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"()
"RI_ConstraintTrigger_c_75214" AFTER UPDATE ON t2 FROM t1 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_upd"()
postgres=#
The keyword all also disables other internal triggers on the table, and requires a superser to execute successfully.
postgres=# create user abce with login password 'abce'; CREATE ROLE postgres=# \c postgres abce You are now connected to database "postgres" as user "abce". postgres=> create table t3 ( a int primary key, b text, c date); CREATE TABLE postgres=> create table t4 ( a int primary key, b int references t3(a), c text); CREATE TABLE postgres=> alter table t4 disable trigger all; ERROR: permission denied: "RI_ConstraintTrigger_c_75235" is a system trigger postgres=>
So as an ordinary user, how should I disable the trigger?
postgres=> alter table t4 disable trigger user;
The specific syntax is:
DISABLE TRIGGER [ trigger_name | ALL | USER ]
Return to the t1 and t2 tables.
postgres=# select * from t1; a | b | c ---+----+------------ 1 | aa | 2020-11-04 2 | bb | 2020-11-04 (2 rows) postgres=# select * from t2; a | b | c ---+---+---- 1 | 1 | aa 2 | 2 | aa (2 rows) postgres=# insert into t2 (a,b,c) values (3,3,'cc'); INSERT 0 1 postgres=#
A record that does not match in t1 is inserted here, but the insertion is successful.
postgres=# alter table t2 enable trigger all;
ALTER TABLE
postgres=# \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | |
c | text | | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
"t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
postgres=# alter table t2 validate constraint t2_b_fkey;
ALTER TABLE
postgres=#
Amazingly, PostgreSQL has no records that are not matching. Why?
Check out a pg_constraint:
postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname | t2_b_fkey
connamespace | 2200
contype | f
condeferrable | f
condeferred | f
convalidated | t
conrelid | 75202
contypid | 0
conindid | 75200
conparentid | 0
confrelid | 75194
confupdtype | a
confdeltype | a
confmatchtype | s
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {2}
confkey | {1}
conpfeqop | {96}
conppeqop | {96}
conffeqop | {96}
conexclop |
conbin |
consrc |
postgres=#
The value of the convalidated field is t, indicating that the foreign key constraint is still valid.
Even if we disable it again, it will still show that it is valid:
postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname | t2_b_fkey
connamespace | 2200
contype | f
condeferrable | f
condeferred | f
convalidated | t
conrelid | 75202
contypid | 0
conindid | 75200
conparentid | 0
confrelid | 75194
confupdtype | a
confdeltype | a
confmatchtype | s
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {2}
confkey | {1}
conpfeqop | {96}
conppeqop | {96}
conffeqop | {96}
conexclop |
conbin |
consrc |
postgres=#
This indicates that when we enable internal triggers, PostgreSQL will not validate the constraints, so it will not verify whether there will be conflicts in the data, because the state of the foreign key constraints is always valid.
What we need to do is to make it invalid first:
postgres=# alter table t2 alter CONSTRAINT t2_b_fkey not valid;
ERROR: ALTER CONSTRAINT statement constraints cannot be marked NOT VALID
## You need to delete the foreign key first, then rebuild the foreign key constraint and set its status to invalid
postgres=# alter table t2 drop constraint t2_b_fkey;
ALTER TABLE
postgres=# delete from t2 where a in (3);
DELETE 1
postgres=# alter table t2 add constraint t2_b_fkey foreign key (b) references t1(a) not valid;
ALTER TABLE
postgres=# \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | |
c | text | | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
"t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a) NOT VALID
Now, you can see that the status is invalid:
postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname | t2_b_fkey
connamespace | 2200
contype | f
condeferrable | f
condeferred | f
convalidated | f
conrelid | 75202
contypid | 0
conindid | 75200
conparentid | 0
confrelid | 75194
confupdtype | a
confdeltype | a
confmatchtype | s
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {2}
confkey | {1}
conpfeqop | {96}
conppeqop | {96}
conffeqop | {96}
conexclop |
conbin |
consrc |
postgres=#
Continue to insert data:
postgres=# insert into t2(a,b,c) values (3,3,'cc'); ERROR: insert or update on table "t2" violates foreign key constraint "t2_b_fkey" DETAIL: Key (b)=(3) is not present in table "t1". postgres=#
Are you even more surprised? An invalid constraint was created, just notifying PostgreSQL
Do not scan the entire table to verify that all row records are valid. For newly inserted or updated rows, the constraints are still checked, which is why the above insert failed.
What should we do?
1. Delete all foreign keys
2. Loading data
3. Recreate the foreign key, but set its status to invalid, thus avoiding scanning the entire table. After that, the new data will be verified
4. Turn on the constraints when the system load is low
Another way is:
postgres=# alter table t2 alter constraint t2_b_fkey deferrable; ALTER TABLE postgres=# begin; BEGIN postgres=# set constraints all deferred; SET CONSTRAINTS postgres=# insert into t2 (a,b,c) values (3,3,'cc'); INSERT 0 1 postgres=# insert into t2 (a,b,c) values (4,4,'dd'); INSERT 0 1 postgres=# insert into t1 (a,b,c) values (3,'cc',now()); INSERT 0 1 postgres=# insert into t1 (a,b,c) values (4,'dd',now()); INSERT 0 1 postgres=# commit; COMMIT
The bad aspect of doing this is that it only works on the next commit, so you need to put all the work into one transaction.
The key point in this article is that the following assumptions will verify that your data is wrong:
postgres=# alter table t2 disable trigger all; ALTER TABLE postgres=# insert into t2 (a,b,c) values (5,5,'ee'); INSERT 0 1 postgres=# alter table t2 enable trigger all; ALTER TABLE postgres=#
This will only verify the new data, but does not guarantee that all data will meet the constraints:
postgres = # insert into t2 (a,b,c) values (6,6,'ff'); ERROR: insert or update on table "t2" violates foreign key constraint "t2_b_fkey" DETAIL: Key(b) = (6) is not present in table "t1". postgres = # select * from t2 where b = 5; a | b | c ---+---+---- 5 | 5 | ee (1 row) postgres = # select * from t1 where a = 5; a | b | c ---+---+--- (0 rows)
Finally, there is another way to solve it, directly modify the pg_constraint directory table. But it is recommended that users do this!
postgres=# delete from t2 where b = 5; DELETE 1 postgres=# delete from t2 where b = 5; DELETE 1 postgres=# alter table t2 disable trigger all; ALTER TABLE postgres=# insert into t2 values (5,5,'ee'); INSERT 0 1 postgres=# alter table t2 enable trigger all; ALTER TABLE postgres=# update pg_constraint set convalidated = false where conname = 't2_b_fkey' and conrelid = 't2'::regclass; UPDATE 1 postgres=# alter table t2 validate constraint t2_b_fkey; ERROR: insert or update on table "t2" violates foreign key constraint "t2_b_fkey" DETAIL: Key (b)=(5) is not present in table "t1". postgres=#
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.