0

I am trying to model a database where I have 2 inverted foreign keys which both tables fields are required NOT NULL. How can I model that?

Here a small sample:

I have two tables called the first is a generic called "SystemObjects" and the second is called "Users". Each "SystemObject" is created by a "User" object and so require a 'CreatedBy' foreign key which cannot be null. In the same time, all users have also generic associated SystemObject entry and the table "Users" contains more info about those objects with specific type "User".

In OOP analogy, "User" inherits from "SystemObject" and has supplementary members. and so I need to keep track of that connection.

A summary to my problem:

  • "A" has a foreign key field to "B"
  • "B" has a foreign key field to "A"
  • Both are required (NOT NULL)

How to escape this trap? Any alternative Model that would solve my problem and allow me to have this vis-verca tracking?

Thank you!

Moslem Ben Dhaou
  • 6,897
  • 8
  • 62
  • 93
  • If you could be more specific as to what your problem is it would help? Why can't you have two FK's, one A->B and the other B->A, what problem are you trying to avoid? – Recurse Jun 27 '12 at 02:31
  • I have A and B tables. A has a field that is not null and is a reference to B. B also has a field that is not null and is a reference to B. The two fields are different, so I cannot use the same FK relationship. A quick sample is: User is a specific type of SystemObject and so it has to keep a reference to it. and SystemObject is always created by a User so it has to keep a ref to it. This means that you cannot insert a user unless you have a SystemObject to refer and you cannot insert a SystemObject unless you have a user to refer. – Moslem Ben Dhaou Jun 27 '12 at 07:57
  • So why not insert both the User and the SystemObject at the same time? I still fail to see what your problem is. – Recurse Jun 27 '12 at 23:33
  • That cant be done. Batch insert is only supported for one table at a time – Moslem Ben Dhaou Jun 28 '12 at 13:32

0 Answers0