Q: Do I have to disable constraints for the insert to happen?
A: In Oracle, no, not if the foreign key constraints are DEFERRABLE
(see example below)
[EDIT]
A: In Microsoft SQL Server, you can't defer foreign key constraints like you can in Oracle. Disabling and re-enabling the foreign key constraint is an approach, but I shudder at the prospect of 1) performance impact (the foreign key constraint being checked for the ENTIRE table when the constraint is re-enabled), 2) handling the exception if (when?) the re-enable of the constraint fails.
With SQL Server, a better approach is to remove the NOT NULL constraint, and allow for a NULL as temporary placeholder while rows are being inserted/updated.
For SQL Server:
-- (with NOT NULL constraint removed from Departments.EmployeeID)
insert into Departments values ('foo',NULL)
go
insert into Employees values ('bar','foo')
go
update Departments set EmployeeID = 'bar' where DepartmentID = 'foo'
go
[/EDIT]
For Oracle:
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO Departments values ('foo','dummy');
INSERT INTO Employees values ('bar','foo');
UPDATE Departments SET EmployeeID = 'bar' WHERE DepartmentID = 'foo';
COMMIT;
Let's unpack that:
- (autocommit must be off)
- defer enforcement of the foreign key constraint
- insert a row to Department table with a "dummy" value for the FK column
- insert a row to Employee table with FK reference to Department
- replace "dummy" value in Department FK with real reference
- re-enable enforcement of the constraints
NOTES: disabling a foreign key constraint takes effect for ALL sessions, DEFERRING a constraint is at a transaction level (as in the example), or at the session level (ALTER SESSION SET CONSTRAINTS=DEFERRED;
)
Oracle has allowed for foreign key constraints to be defined as DEFERRABLE for at least a decade. I define all foreign key constraints (as a matter of course) to be DEFERRABLE INITIALLY IMMEDIATE. That keeps the default behavior as everyone expects, but allows for manipulation without requiring foreign keys to be disabled.
see AskTom: http://www.oracle.com/technology/oramag/oracle/03-nov/o63asktom.html
see also: http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_12.shtml