CODEWITHSUNDEEP
sqlsql-server
Diskdrive
Diskdrive

Reputation: 18825

Adding a row to Table A if it has a required foreign key to Table B which has a required foreign key to Table A

This might sound complicated, so I'll give an example.

Say, I have two tables Instructor and Class.

Instructor has a required field called PreferredClassID which has a foreign key against Class.

Class has a required field called CurrentInstructorID which is a foreign key against Instructor

Is it possible to insert a row to either of these tables?

Cause if I insert a row to Instructor, I won't be able to as I'll need to supply a PreferredClassID, but I can't create a Class row either because it needs a CurrentInstructorID.

If I can't do this, how would I solve this problem? Would I just need to make one of those fields non-required (even if business requirements specifies it really should be required?)

Upvotes: 1

Views: 81

Answers (3)

mikurski
mikurski

Reputation: 1363

Something to consider (as per user7396598's answer) is looking at how normal forms apply to your data as it fits within your relational model.

In this case, it might be worth looking at the following:

With your Instructor table, is the PreferredClassID a necessary component? Does an instructor -need- to have a preferred class, or is it okay to say "Hey, I'm creating an entry for a new instructor, I don't know their preferred class."

(if they're new, they might not have a preferred class that your school offers)

This is a case where you definitely want to have a foreign key, but it should be okay to say 'I don't necessarily know the value I want to put there.'

In a similar vein, does a Class need to have an instructor when it's created? Is it possible to create a Class that an instructor has not been assigned to yet?

Again, both of these points are really a case of 'I don't know what I want to put here, but when I do, it should be a specific instance that exists in another table.'

Upvotes: 0

user7396598
user7396598

Reputation: 1289

If you find yourself here, reevaluate your data relation model.

In this case, you could simply have a lookup table called PreferredCourse with courseId and instructorId.

This will enforce that both the course and instructor exist before adding the row to the PreferredCourse lookup. Maintaining business model requirements without bending the rules of database model requirements.

While it may seem excessive to have another table, it will prevent a whole lot of maintenance overhead in both your database procedures and jobs, and your application code. Circular references create nothing but headaches and are easily solved with small lookup tables and JOINs.

The Impaler gave an example of how to accomplish this with your current data structure. Please note, that you have to 1: make a key nullable in at least one of the tables, and then 2: Perform INSERTs in a specified order. Or, 3: disable the constraints, 4: perform INSERTS, 5: reenable constraints, 6: roll back transaction if constraints are now broken.

There is a whole lot that can go wrong, simply fix the relation model now before things get out of hand.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

As long as one of those foreign keys allows a null value, you're good. So you:

  1. Insert the row that accepts the null value first (say Instructor), with a null value on the FK. Get the ID of the inserted row.

  2. Insert in the other table (say Class). In the FK you use the ID you got from step #1. Once inserted, you get the ID of this new row.

  3. Update the FK on the first row (Instructor) with the ID you got from step #2.

  4. Commit.

Alternatively, if both FKs are NOT NULL then you have a bit of a problem. The options I see for this last case are:

  • Use deferrable FK integrity check. Some databases do allow you to insert without checking integrity until the COMMIT happens. This is really tricky, and enabling this is looking for trouble.

  • Disable the FK for a short period of time. Some databases allow you to enable/disable constraints. You are not deleting them, just temporarily disabling them. If you do this, don't forget to enable them back.

  • Drop the constraint temporarily, while you do the insert, and the add it again. This is really a work around of last resort. Adding/Dropping constraint are DML statements and usually cannot participate in a transaction. Do this at your own peril.

Upvotes: 0

Related Questions