AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Postgres add column with foreign key8/6/2023 ![]() 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=# 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 Comments
Read More
Leave a Reply. |