PRIMARY KEY (key) DEFERRABLE INITIALLY DEFERRED means: Here the constraint is evaluated when you perform a COMMIT. ![]() The foreign keys can be configured like this. It can be used to make foreign key constraints deferrable and to set the constraints within a transaction. A collection of properties related to deferrable constraints. How is NOT DEFERRABLE different from DEFERRABLE INITIALLY IMMEDIATE? In both cases, it seems, any constraints are checked after each individual statement.ĭeferrable()-> object. In the latter case, the check will be made immediately after each statement. In the former case, checking will be deferred to just before each transaction commits. ![]() ![]() Postgres offers an even more elegant solution: it’s possible to combine the two INSERT statements into a single statement using a common table expression.īecause it’s a single statement, there is no need to define the constraints as deferrable, as the constraints are checked at the end of the statement.We follow the keyword DEFERRABLE by either INITIALLY DEFERRED or INITIALLY IMMEDIATE. If inserting depending rows is the exception rather then the common case, it is recommended to define the constraints as INITIALLY IMMEDIATE and only make them deferred when really needed. Setting a constraint to deferrable (either as INITIALLY DEFERRED or through a SET CONSTRAINTS statement), incurs a memory overhead that might be substantial when processing many rows. After inserting the employees all foreign keys are correct and the COMMIT will succeed. But the database does not complain, because the foreign key will only be checked when the COMMIT is issued. The employee_id 42 doesn’t exist when the department is inserted. So with the following script, we can insert the new department and all employees without an error:īegin transaction set constraints all deferred insert into department ( id, name, manager_id ) values ( 1, 'Maintenance', 42 ) insert into employee ( id, name, department_id ) values ( 1, 'Arthur', 1 ), ( 2, 'Ford', 1 ), ( 42, 'Marvin', 1 ) commit With that command it’s either possible to do this for a named constraint instead or for all constraints that allow to be deferred. This is done using the SET CONSTRAINTS command: The constraint can be defined as “always deferred” or you can turn the “deferrable” behaviour on or off when you need it: alter table departmentĪdd foreign key (manager_id) references employeeĪdd foreign key (department_id) references departmentĪfter creating the constraints like that, they behave exactly like any other constraint: they are evaluated when running the DML statement (because of the INITIALLY IMMEDIATE option).īut the deferrable attribute allows us to defer the check until the end of the transaction. Precisely for this situation, Postgres (and Oracle) allow constraints to be defined as “deferrable” which enables just that: an evaluation of the constraint at COMMIT time. But when the first INSERT is executed, the referenced values for the second table don’t exists (and vice versa).Ī solution to this problem is a constraint that is checked when the transaction is committed, rather then after each INSERT statement. The problem stems from the fact that the database evaluates the constraints at the end of the INSERT statement. With modern SQL, we do however have two approaches to insert departments and employees that reference each other. Running two independent inserts won’t work as we can’t insert into the department table without a manager_id and we can’t insert into the employee table without a department_id. Note that in reality, I would expect at least one of those columns to be nullable though. Create table department ( id integer primary key, name varchar ( 50 ) not null unique manager_id integer not null ) create table employee ( id integer primary key, name varchar ( 50 ) not null, department_id integer not null ) alter table department add foreign key ( manager_id ) references employee alter table employee add foreign key ( department_id ) references department Īs both foreign key columns are defined as NOT NULL, the usual approach of first inserting a NULL value first and update the row later with an existing value is not applicable.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |