0

I'm dealing with a set of tables in a database that appear to have a circular relationship (see image). This is the ARTS database if that is of any help to anyone.

Circular reference in Sql server

A user signing on:

  • a) must create a (insert into) session, which in turn needs a SessionStartTransactionID (=SignOnTransaction)
  • b) a SignOnTransaction is a type of ControlTransaction
  • c) a ControlTransaciton is a type of Transaction
  • d) a Transaction needs a reference to an existing Session (along with Operator, etc.)

Note: The Transaction.SessionStartTransactionID,Transaction.OperatorID, and Transaction.WorkStationID (thoese 3 are the composite primary key in Session) cannot be NULL in the Transaction table.

I can't figure out how to create (insert into) SignOnTransaction, or insert into any of the tables mentioned above.

How do you do that in SQL Server? Is that even possible? Where would I start? Thanks!

John
  • 3,591
  • 8
  • 44
  • 72

1 Answers1

1

If something you're describing is impossible, then you're understanding it wrong. You can't have table A that has a required Key that references table B that has a required key that references table A. One of the two keys has to be nullable, or foregin key relationships aren't being enforced.

Some ideas

Given that Session uses StartTransactionID as part of its primary key means that it can't be null, so it seems likely that StartTransactionID in Transaction can be null, so that you insert Transaction, then ControlTransaction then SignOnTransaction then Session, then update the Transaction that was created with the id. (If the FK was not enforced, you can skip the update, and just use the same value for the PK if it isn't an Indentity column).

The only other possible solution I can think of is that you have to use an ALTER TABLE Transaction NOCHECK CONSTRAINT StartTransactionIDconstraint_name every time you first insert into Transaction, and then restore the constraint after you update the table. Seems like a hackish solution to be sure. Especially because you can't do an ALTER TABLE in a transaction so that you leave yourself open for a ton of problems.

...

Since this appears to be part of a production system, why don't you run a SQL Trace to see how the data is getting populated. This helps me all the time.

Daniel Gimenez
  • 18,530
  • 3
  • 50
  • 70
  • You can use `alter table` inside a transaction. Wouldn't be a good solution here though. – Martin Smith Oct 19 '14 at 19:09
  • @Daniel Gimenez thanks for your input. I understand the problem but was hoping for an elegant solution e.g. doing the INSERTs in a transaction upon which all 4 tables get filled somehow (I think that is possible in Oracle?). Strictly speaking the Transaction table doesn't need a reference back to the Session, as there exists already a link from tables Session-SignOn-Control-Transaction table. It just bugs me that the ARTS consortium designed something like this and it doesn't work. – John Oct 20 '14 at 14:05