![]() 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. You can run the script once.Constraints are in important concept in every realtional database system and they guarantee the correctness of your data. 'ALTER TABLE %s ALTER CONSTRAINT %s DEFERRABLE INITIALLY DEFERRED ',Īnd conrelid = any(array::regclass)Īnd connamespace = 'ssp2_pcat'::regnamespace ![]() You can generate a script to alter foreign keys to DEFERRABLE INITIALLY DEFERRED in this way: select format( There are more options, however, so read about deferrable constraints in the documentation: It's enough when you define foreign keys as deferrable initially deferred and execute all inserts in a single transaction. You don't need to drop and recreate foreign keys. Can someone please keep me posted if I'm missing any join from any other data dictionary tables? UPPER(cs.TABLE_NAME) = 'ADDITIONAL_RULES' ANDīut unfortunately this query is not giving the desired result, looks like I'm missing something as its especially when selecting the c.COLUMN_NAMEfield instead of the references field it's giving all the available fields in the table, also it's not giving the name of the Parent_table cs.TABLE_NAME rather it's giving the same table_name. WHERE cs.CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND ' REFERENCES ' ||cs.TABLE_NAME || ' (' || cs.CONSTRAINT_NAME || ') ' || ' ON UPDATE ' || rc.UPDATE_RULE || ' ON DELETE ' || rc.DELETE_RULEįROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.TABLE_CONSTRAINTS CS, INFORMATION_SCHEMA.COLUMNS C ' ADD CONSTRAINT ' ||rc.CONSTRAINT_NAME || Now I'm working on a similar query to ADD the constraints as follows, (that are dropped in the first step), SELECT DISTINCT 'ALTER TABLE ' || cs.TABLE_NAME || Used the WITH clause with STRING_AGG function by enabling the DBlinkĮxtensions in the POSTGRES similarly using Dynamic SQL so that it gives SQL for multiple tables at one shot. Mentioned above truncate is done as its very straight forward. The output of above SQL is going to be as follows,ĪLTER TABLE SSP2_PCAT.RATES DROP CONSTRAINT fk_rate UPPER(CONSTRAINT_SCHEMA) = 'SSP2_PCAT' AND 'ALTER TABLE SSP2_PCAT.' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ' 'įROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS I created the following SQL using dynamic SQL to genarate a SQL DROP foreign key constraint so that I can execute for multiple tables I'm working on, SELECT ADD the constraints that are dropped at the first step.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |