Professional Web Applications Themes

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. #1

    Default Re: how to do inserts with circular fk constraints?

    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 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. #2

    Default Re: how to do inserts with circular fk constraints?


    Sorry replying to myself:

    I just found this posting in the archives:
    http://groups.google.at/groups?q=sql+server+circular+constraints&hl=de&lr= &ie=UTF-8&oe=UTF-8&selm=%23QQGCsmVAHA.259%40cppssbbsa04&rnum=1

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


    many thanks in advance,
    Andreas



    Andreas Guest

  3. #3

    Default 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

    alter table TLeft add
    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

Similar Threads

  1. Foreign Key Constraints
    By pengypenguin@gmail.com in forum MySQL
    Replies: 8
    Last Post: October 25th, 03:39 PM
  2. Replies: 3
    Last Post: March 22nd, 12:41 PM
  3. Constraints and indexes
    By Luc in forum Oracle Server
    Replies: 7
    Last Post: October 21st, 07:13 PM
  4. How to switch off constraints?
    By Joachim Engel in forum Informix
    Replies: 7
    Last Post: October 13th, 04:36 PM
  5. import constraints and indexes only
    By michael ngong in forum Oracle Server
    Replies: 0
    Last Post: December 5th, 02:50 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139