# how to do inserts with circular fk constraints? - Microsoft SQL / MS SQL Server

Hi HSalim, thank you again for answering! HSalim wrote:  I am really hiding a lot of details to simplify the problem. For the sake of this discussion, I think you can assume that states are shared it doenst chance the cycle. (Just always assume constraints so that: every state has exactly one process, every process has exactly one start state) I don't think the problem goes away with different designs at all. The system-invariant does have a cycle: 1-*) Every process has exactly one start state 2-*) Every state has exactly one process 3-> Every process has one or more ...

1. ## Re: how to do inserts with circular fk constraints?

Hi HSalim,

HSalim wrote:

I am really hiding a lot of details to simplify the problem. For the
sake of this discussion, I think you can assume that states are shared
it doenst chance the cycle. (Just always assume constraints so that:
every state has exactly one process, every process has exactly one start
state)

I don't think the problem goes away with different designs at all. The
system-invariant does have a cycle:

1-*) Every process has exactly one start state
2-*) Every state has exactly one process
3-> Every process has one or more states

Now, if I want to create a new process, I _have_ to create a new start
state as well. Because If I don't, I violate (1) and (3). I cannot
create the start state first because of (2). The only way out I can see
is to have a block of SQL statements that are treated as atomic by the
DB. I.e. the system invariant is checked before and after the execution
of the statements, but not in between.

I thought about that. It seems a bit wrong to me, because it would
nativly allow for zero or more start states per process. I can of course
again express this constraint via well erm constraints, but it would not
help my problem. I would still have to 'insert' several rows with no
restriction checkin in between.

c) is not completly correct, because in real life, the state has
meaning, I just omitted it here. It specifies for example what persons
can make a process move into a given state.

Also how do I add a variable number of data-entities to a row in a db?

Yes, this is it. A process is completly self contained. But how do I add
arbitrarry much (but well structured) data to a table row?

The state of a db is just the sum of its memory, i guess (; My states do
have meaning, but you are right I omitted it here from the discussion.
Thinking about it the states per se have no meaning, only when I
interpret them they get one, but lets not talk about meaning too much,
it is a very weird topic (;

thank you very much for bearing with me (;
Andreas

Andreas Guest

2. ## Re: how to do inserts with circular fk constraints?

I just found this posting in the archives:

Deferrable constraints are exactly what I am looking for. Anybody knows
the status of them?

Andreas

Andreas Guest

3. ## Re: how to do inserts with circular fk constraints?

"Andreas Leitner" <at> wrote in message
news:3f251114\$inode.at...

You could use a nullable foreign key on one of the tables, but such a
solution gets complicated (impossible, unless you have a meaningful PK, or
use an instead of insert trigger with a temp table, perhaps) if you want to
be able to insert sets instead of single row pairs:

create table TLeft(
TLeftId int identity(1,1) primary key,
TRightId int null
)
GO

create table TRight(
TRightId int identity(1,1) primary key,
TLeftId int not null foreign key references TLeft(TLeftId)
)
GO

constraint FK_TRight foreign key (TRightId) references TRight(TRightId)
GO

insert TLeft(TRightId) select null

declare TLeftId int set TLeftId = scope_identity()

insert TRight(TLeftId) select TLeftId

declare TRightId int set TRightID = scope_identity()

update TLeft set TRightId = TRightId where TLeftId = TLeftId

Donald Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•