How to insert a new record into table A when table A is decomposed into table B and vice versa
I'm not sure if it's well designed, if you don't like it please advise me how to do it.
- I am 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:
- Table A can only contain rows if TableB has at least 1 row associated with TableA;
- And for every row in TableB there should be a row associated with it in TableA);
TableA is "parent table" and TableB is "table of children", it is something similar, parent must have 1 or more children, and each child can only have 1 parent.
Is it correct?
The problem I am facing is when I try to do an INSERT statement, if it is correct, how do I do the INSERT? temporarily disable restrictions?
Thanks!
The problem I am facing is when I try to insert
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 require a reference to table b, since the table requires a field in table a. this is called a one-to-many relationship.
so in table a you can have the following values:
1 a
2 b
3 c
and in table b you can:
1 asdf 1
2 sdfg 1
3 pof 2
4 dfgbsd 3
now you can make a query to show data from table a like 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
a source to share
This is an unusual requirement. If I was stuck with it (and I really would like to go back to make sure it is indeed a requirement), I would design it like this:
Make a regular foreign key from table a to table b with parent and b with child.
Add a trigger on table a that inserts a record into table b if it does not exist when a record is inserted into table. Add another trigger to table b that drops the record table if the last linked record in table b is deleted.
Alternatively, you can put inserts to both tables in the saved proc. Remove all rights to insert into the table except through proc. You still need a foreign key relationship from table to table b and a trigger on table b to ensure that if the last record is deleted in the table, the record will be deleted. But in this case, you can do away with the trigger on the table.
I would use the first scenario if there is no information in table b that cannot be found from a trigger in table a, such as one or more required fields that have no value, which you can display in form table a.
a source to share
I would put inserts in proc: disable constraints, insert data, enable constraints. You may have to make sure that this is the only transaction even though restrictions are disabled though.
This can be achieved by setting the isolation level to SERIALIZABLE, but this in turn can lead to the loss of your concurrency.
Kev
a source to share