Now we have the desired state and we can insert our data: "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a) NOT VALID Postgres=# alter table t2 add constraint t2_b_fkey foreign key (b) references t1(a) not valid Postgres=# delete from t2 where a in (3,4) Postgres=# alter table t2 drop constraint t2_b_fkey The correct way of doing it is to drop the foreign key and then re-create it with status invalid: Seems this is not the correct way of doing it. Postgres=# alter table t2 alter CONSTRAINT t2_b_fkey not valid ĮRROR: ALTER CONSTRAINT statement constraints cannot be marked NOT VALID What we really need to do for getting the constraint validated is to invalidate it before: That implies that PostgreSQL will not validate the constraint when we enable the internal triggers and PostgreSQL will not validate all the data as long as the status is valid.
It is even validated if we disable it once more: Postgres=# select convalidated from pg_constraint where conname = 't2_b_fkey' and conrelid = 't2'::regclass Why is that? If we ask the pg_constraint catalog table the constraint is recorded as validated: Surprise, surprise, PostgreSQL does not complain about the invalid row. Postgres=# alter table t2 validate constraint t2_b_fkey Postgres=# alter table t2 enable trigger all There clearly is no matching parent for this row in the t1 table but the insert succeeds, as the foreign key is disabled. Postgres=# insert into t2 (a,b,c) values (3,3,'cc') As the foreign key currently is disabled we can insert data into the t2 table that would violate the constraint: Coming back to our initial t1 and t2 tables. Postgres=> alter table t4 disable trigger user Īs I do not have any triggers it of course does not make much sense. What you could do as a regular user to do disable the user triggers: Postgres=> alter table t4 disable trigger all ĮRROR: permission denied: "RI_ConstraintTrigger_c_16484" is a system trigger Postgres=> create table t4 ( a int primary key Postgres=> create table t3 ( a int primary key You are now connected to database "postgres" as user "u1".
POSTGRES ON UPDATE CASCADE PASSWORD
Postgres=# create user u1 with login password 'u1' Trying that with a normal user will fail:
One restriction of the “ALL” keyword is, that you need to be superuser for doing that. “ALL” means, please also disable the internal triggers that are responsible for verifying the constraints. "RI_ConstraintTrigger_c_16461" AFTER UPDATE ON t2 FROM t1 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_upd"() "RI_ConstraintTrigger_c_16460" AFTER INSERT ON t2 FROM t1 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"() It becomes more clear when we look at the table again: The syntax might look a bit strange but it actually does disable the foreign key and it would have disabled all the foreign keys if there would have been more than one. Postgres=# alter table t2 disable trigger all As we do not know the ordering of the data in the script we decide to disable the foreign key constraint on the t2 table and validate it after the load: Lets assume we want to load some data provided by a script. "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a) TABLE "t2" CONSTRAINT "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a) Postgres=# insert into t2 (a,b,c) values (2,2,'aa') Ĭurrently the two tiny tables look like this:Ĭolumn | Type | Collation | Nullable | Default Postgres=# insert into t2 (a,b,c) values (1,1,'aa') Postgres=# insert into t1 (a,b,c) values(2,'bb',now()) Postgres=# insert into t1 (a,b,c) values(1,'aa',now()) Postgres=# create table t2 ( a int primary key Postgres=# create table t1 ( a int primary key Validation of the constraints is deferred until all your data is there.Īs always lets start with a simple test case, two tables, the second one references the first one: In such a case it is required to either drop the constraints or to disable them until the data load is done. The reason could also be, that you need to load data and you do not know if the data is ordered in such a way that all foreign keys will validate for the time the data is loaded.
The reason could be performance related because it is faster to validate the constraints at once after a data load.
While constraints are essentials there are situations when it is required to disable or drop them temporarily. Constraints are in important concept in every realtional database system and they guarantee the correctness of your data.
0 kommentar(er)
