How to insert a new record to table A when table A deppends on table B and vice versa

Question

Hello, I'm not sure if this is well designed, if it's not please, advice me on how to do this.

• I'm using Sql Server 2008

I have:

TableA (TableA_ID int identity PK, Value varchar(10), TableB_ID PK not null)
TableB (TableB_ID int identity PK, Value varchar(10), TableA_ID PK not null)

The goal is simple:

• TableA can have rows only if there is at least 1 row in TableB associated with TableA;
• And for each row in TableB, there must be a row associated with it in TableA);

TableA is the "Parent Table", and TableB is the "Children's table", it's something like, a parent should have 1 or more children, and each child can have only 1 parent.

Is this right?

The problem I'm having is when I try to do an INSERT statement, if this is correct, how should I make the INSERT? temporary disable the constraints?

Thanks!

The problem I'm having is when I try to insert

Answer 1

The parents don't depend on the children. You need to remove your reference to Table B in Table A.

Answer 2

You have a circular dependency. These don't really work well for declarative enforcement, you would have to disable the constraints every time you wanted to insert.

Answer 3

TableA (TableA_ID int identity PK, Value varchar(10))
TableB (TableB_ID int identity PK, Value varchar(10), TableA_ID not null)

as a parent, table a does not need to reference table b, since table be requires there be a field in table a. this is called a one to many relationship.

so in table a you might have these values:

1 a
2 b
3 c

and in table b you could have these:

1 asdf 1
2 sdfg 1
3 pof 2
4 dfgbsd 3

now you can make a query to show the data from table a with this:

select b.TableB_ID, b.Value, a.TableA_ID, a.Value
from TableB b
inner join TableA
on b.TableA_ID=a.TableA_ID

Answer 4

That's an unusual requirement. If I was stuck with it (and I would really push back to make sure it was indeed a requirement) I would design it this way:

Make a regular foreign key from table a to table b with a the parent and b the child.

Add a trigger to table a that inserts a record to table b if one does not exist when a table a record is inserted. Add another trigger to table b that deletes the table a record if the last related record in table b is deleted.

ALternatively, you could put the inserts to both tables ina stored proc. Remove all insert rights to the table except through the proc. YOu would still need the foreign key relationship from tablea to table b and the trigger on table b to ensure that if the last record is deleted the table a record is deleted. But you could do away with the trigger on table a in this case.

I would use the first scenario unless there is information in table b that cannot be found from the trigger on table a, say one or more required fields that don't have a value you can figur eout form table a.

Answer 5

I would put the inserts into a proc: disable the constraints, insert the data, enable the constraints. You may need to make sure that this is the only transaction going on whilst the constraints are disabled though.

That could be acheived by making the isolation level SERIALIZABLE, but that in turn could massace your concurrency.

Kev