Josh Otwell has a passion to study and grow as a SQL Developer and blogger. To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!īe sure and visit the “Best Of” page for a collection of my best blog posts. Have I mentioned how much I love a cup of coffee?!?! Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Please share your findings here, with someone else you know who would get the same value out of it as well. I truly hope you discovered something interesting and enlightening. Thank you for taking the time to read this post. I hope through this blog post and the linked article, you can change existing FOREIGN KEY‘s in your tables as needed.
#POSTGRESQL ADD FOREIGN KEY UPDATE#
"pipe_kind_k_pipe_id_fkey" FOREIGN KEY (k_pipe_id ) REFERENCES pipe (pipe_id ) ON UPDATE CASCADE ON DELETE CASCADE "kind_fk" FOREIGN KEY (k_kind_id ) REFERENCES kind_type (kind_id ) ON UPDATE CASCADE ON DELETE CASCADE "k_pipe_fk" FOREIGN KEY (k_pipe_id ) REFERENCES pipe (pipe_id ) ON UPDATE CASCADE ON DELETE CASCADE postgres=# alter table fp alter CONSTRAINT fp_id_fkey deferrable Column | Type | Collation | Nullable | Default Set the transaction after starting the transaction to finish the deferred judgement. create table fp(id int references pr(id) INITIALLY DEFERRED, c1 int) ģ) Deferred judgment is allowed. Postgres=# create table fp(id int references pr(id), c1 int) ĮRROR: insert or update on table "fp" violates foreign key constraint "fp_id_fkey"ĭETAIL: Key (id)=(1) is not present in table "pr".Ģ) Allow the deferred constraint judgment and set rules for deferral to end the transaction. postgres=# create table pr(id int primary key, c1 int) Set rules for deferred constraint judgment in transactions: SET CONSTRAINTS Examplesġ) Deferred constraint judgment is not allowed. You can alter the constraint check time with the SET CONSTRAINTS command. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement, which is the default. If a constraint is deferrable, this clause specifies the default time to check the constraint. Referred judgment rules: End of statement or transaction Note that you cannot use deferrable constraints as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause. NOT NULL and CHECK constraints are not deferrable. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. Checking of deferrable constraints can be postponed until the end of the transaction (using the SET CONSTRAINTS command). A constraint that is not deferrable will be checked immediately after every command.
This controls whether you can defer the constraint. Whether deferred constraint judgment is allowed: You can set rules by modifying the constraint definition or set directly in the transaction.
Set rules for deferred judgment to determine the constraint at the end of a statement or a transaction. You can specify this feature when creating tables or constraints and modify them later. Deferred constraint judgment is allowed. Therefore, you can avoid dumping failures after creating constraints.Ģ) PostgreSQL allows the deferred detection of constraints. To solve the constraint- and dependency-related problem, PostgreSQL usually adopts the following methods:ġ) When data is imported, constraints are usually created after all the data is written. If both the data types are interdependent, it will be even more difficult to import the data. You must first transfer the dependent data and then transfer the relevant data. It will be very inconvenient to import data if you cannot defer the detection. Is it at the end of the statement or during the insert tuple? Can you defer the constraint detection? Can you defer it until the end of the statement or transaction? Assume that a table has constraints and understand when these constraints are detected.
0 kommentar(er)
