Insert 2 rows that are related in both directions in SQL

Question

I have two tables kinda like this:

// Person.Details Table:
PersonID int [PK] | EmployeeCreatorID int [FK] | FirstName varchar | OtherInfo...

// Employee.Details Table:
EmployeeID int [PK] | PersonID int [FK] | IsAdmin bit | OtherInfo...

Each table is related to the other:

[Employee.Details.PersonID]===>[Person.Details.PersonID] AND
[Person.Details.EmployeeCreatorID]===>[Employee.Details.EmployeeID]

through their foreign keys.

The problem is that it is not possible to create the first person/Employee without removing one of the foreign keys constraints, inserting the rows, then adding the constraint back in (which is pretty lame).

The obvious God-paradox here is that the first "Employee" doesn't exist to create itself (the "Person").

Is there a way to simultaneously insert data into two tables? This created-is-the-creator scenario will only need to happen once. If I cannot insert data into two tables simultaneously are there any other methods you SO geniuses suggest?

CLARIFICATIONS

There are other tables that are related to the "Persons" table...like "Students" and "Guardians." A person cannot switch types (Employee cannot switch to Student or Guardian, and visa-versa). The paradox is similar to an Employee table that has a ManagerID FK; except in my case the tables have been separated.

SOLUTION thanks to Remus Rusanu and b0fh

--/*seeds database with first employee*/
BEGIN TRAN
GO
INSERT INTO Person.Details
    (EmployeeCreatorID, FirstName, Active)
VALUES
    (@@Identity, 'Admin', 1)
DECLARE @Identity int;
SET @Identity = @@Identity;
INSERT INTO Employee.Details 
    (PersonID, IsAdmin, Email, Password) 
VALUES
    (@Identity, 1, 'admin', 'admin')
UDPATE
    Person.Details
SET
    EmployeeCreatorID = @@Identity
WHERE
    PersonID = @Identity

IF(@@ERROR <> 0)
    ROLLBACK TRAN
ELSE
    COMMIT TRAN

Answer 1

Not sure about SQL server, but in other DBMS I know, you can do it as long as the two steps are within a single transaction. Just wrap the two statements between BEGIN; and COMMIT;.

Answer 2

Model looks flawed to me, but I think you maybe just need to explain the requirements better. So far:

• Every Person is created by an Employee.
• An Employee is a Person.

Are there Persons that are not Employees? Why are they in the system? Is it possible for a Person to switch from being an Employee to not am Employee, or vice-versa?

Although I think a better answer may come when you can answer the above question.. one suggestion is just to change EmployeeCreaterId to PersonCreatorId, and have a secondary (not enforced though database schema) requirement that PersonCreatorId is an employee.

Answer 3

I'd rather change the db design. Tables shouldn't be related in such way imho.

Answer 4

A NULL key will pass the foreign key constraint. You can insert an Person with a NULL CreatorID, and this becomes the grand daddy of the entire hierachy.

You can also insert disable the constraint, insert a first pair (Person, Employee) that points to each other, then enable back the constraint and from now on the system is seeded.

Answer 5

You only have to have one employee to create people whom may or may not be employees. The only problem is the employee can't create himself. So I would propose before entering the first person into the database you make sure the employeecreatorid fk is not a required column. Then create the person. Then create the employee record that for that related person. Since you have a person you can require the personID FK in the employee table and it will exist. Then Update the original Person record setting the EmployeeCreatorId equal to the newly created EmployeeID Next set the EmployeeCreatorID FK column in the person table back to required.
Now you must make sure that any new people are created only by current employees, which will not be the person who is being created.

This will avoid all needs to drop and recreate keys every time a new person is created. Good luck.