SQL Server Performance

Foreign Key Relation

Discussion in 'SQL Server 2005 General Developer Questions' started by babupp, Dec 4, 2005.

  1. babupp New Member

    Hi,

    I have a doubt in creating the foreign key constraints.

    Can I create a relation like, many-parents-one-child ? I mean there are 3 PK tables, related to one FK table. My expectation is while entering data in child table, PK should be existing in any one of the parent table.

    Is it possible to do like this. Or what is the work-around for this.

    Thanks in advance.

    Regards
    Babu
  2. Chappy New Member

    You cannot do this using sql servers's built-in constraints.
    If the parent structures are all similar, you could consider adding them all to the same parent table, and add a 'parenttype' field, the FK constraints could then be used.

    You may also be able to add some custom constraints to do this instead.

    Failing that, do your checking logic in a stored procedure and prohibit access to inserts into table , other than through the stored proc.
  3. Adriaan New Member

    You're describing a table with Foreign Key relationships. Just make sure that the FK columns are NOT NULL, this way you cannot have rows without matching values on the remote tables.
  4. babupp New Member

    Hi Chappy,Adriaan,

    Thanks for your feed-back.

    I'm planning to go ahead with implementing triggers on child tables. Since the wirte operation is less and the read will be very high on these tables, I think that could be the best solution here.

    With Regards
    Babu
  5. Adriaan New Member

    The only point for using triggers is to avoid ugly messages in the client app.

    Another way to avoid those ugly messages is to build the same checks you want to do in the triggerm, into the client app immediately before it attempts to save data.

Share This Page