locked
Circular Foreign key dependency problem RRS feed

  • Question

  • Lets say we have a Parent child table structure...

    Create table ParentTable
    (
    Id int identity(1,1),
    DefaultChildId int null
    )

    Create table ChildTable
    (
    Id int identity(1,1),
    ParentId int (Forign key to Parent table),
    Value varchar(10) 
    )

    Now for a given Parent I want to know the Default child value.

    The Obvious thing to me seems to be to create a property on the header table linking to an id on the child table as shown above.  

    But now it seems like it would be sensible to add a foreign key constraint to that property to the id of the child table.  

    Obviously you can not do this, as the parent record would need to be created before the child record can be created due to the Foreign key constraint on the ParentId.  So I have made it nullable so this can be populated before we create the default child node...

    However the default Child id does seem like it should be constrained somehow if the value is not null...

    Hope someone can see my dilemma and offer some better way of designing this requirement....

    Tuesday, February 28, 2012 11:31 AM

Answers

  • If the default required is Code, and is associated with the System, but not Policy, and Code is an attribute (not another object), you can ADD Default_Code_Inforce and Default_Code_NotInForce to the System TABLE.

    If Code is an object, and is a child of System, the default goes in the Code TABLE, that is, the Code has the "default" attribute. If Code is an object, and is not a child of System, but is associated with System (many to many) it would go in a System_Code TABLE, that is, it is an attribute of the relationship.

    • Marked as answer by Stephanie Lv Thursday, March 8, 2012 7:29 AM
    Wednesday, February 29, 2012 1:09 PM
    Answerer

All replies

  • You will have to insert into a Childtable ( via stored procedure or trigger or output clause) the Parentid value..... 

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, February 28, 2012 11:55 AM
  • But now it seems like it would be sensible to add a foreign key constraint to that property to the id of the child table.  

    Obviously you can not do this, as the parent record would need to be created before the child record can be created due to the Foreign key constraint on the ParentId.  So I have made it nullable so this can be populated before we create the default child node...

    However the default Child id does seem like it should be constrained somehow if the value is not null...

    You can still create a foreign key on ParentTable referencing ChildTable.  The relationship will be enforced only when the DefaultChild value is not null.  As Uri suggested, the sequence would be (assuming all is done in a single transaction):

    1. insert into ParentTable (with NULL DefaultChildId)
    2. insert into ChildTable with the related ParentId
    3. update the DefaultChildId with the newly assigned ChildTable.Id

    We might be able to make some specific design suggestions if you share the actual business problem you are modeling.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, February 28, 2012 12:10 PM
    Answerer
  • >The Obvious thing to me seems to be to create a property on the header table linking to an id on the child table as shown above. 

    Do not include child attributes in the parent! Default child is not an attribute of the parent, it is an attribute of the child itself. One way to enforce a default is:

    Child
    ------
    Id (PK)
    Parent (FK)
    Value
    Status (default, secondary, ....)
    UNIQUE(Parent, Status)

    If status can be NULL, that is, only default is specified, a generated COLUMN can be used.

    Child
    ------
    Id (PK)
    Parent (FK)
    Value
    Status (default, secondary, ....)
    Status_Unique AS ISNULL(Status, CAST(Id AS VARCHAR)) PERSISTENT UNIQUE

    This way, the child's attribute (that it is the default) is held within the child TABLE itself.

    Tuesday, February 28, 2012 1:02 PM
    Answerer
  • IF:

    1.        A child can have more than one parent.
    2.        The parent child relationship is not an attribute of the child.

    If either of these cases is true, then it would make sense to create a third table for the relationship, also I try to avoid NULL values that will be included in an index.

    Create table ParentTable
    (
         Id int identity(1,1) NOT NULL (PK)

    )

    Create table Relationship

    (

         ParentId int NOT NULL (FK),

         ChildId int NOT NULL (FK),

         RelationshipType int NOT NULL

    )

    Create table ChildTable
    (
         Id int identity(1,1) NOT NULL (PK)

    )


    William F. Kinsley

    Tuesday, February 28, 2012 3:38 PM
  • Thanks for the replies guys, much food for thought.  I will detail the business requirements in question.

    We import data with status codes from different systems.  

    So the parent table is for the system.

    Create Table System
    (
    Id int Identity(1,1) not null (pk)
    Name
    )

    As far as our domain is concerned we are only interested in if the status is InForce or OutOfForce.

    So the child table is really a lookup to decide if an imported status code is InForce or OutOfForce

    Create Table SystemPolicyStatus
    (
    Id int identity(1,1) NOT NULL(pk),
    SystemId int NOT NULL (FK),
    PolicyStatus varchar(50) null,
    InForce bit not null
    )

    We can then join to this table to derive if the status code we recived for a policy from a particular system is inforce or not.

    However at some point we will want to add additional policies to the collection of policies for a particular system.  At this point here we do not know what status to give it other than inforce or outOfForce.  

    So the requirement is for any given system we need to be able to map the a default StatusCode for inforce or out of force...

    So bearing in mind what has been already said I am now thinking that a third table is the way to go..... it feels much better.

    Create Table DefaultSystemPolicyStatus
    (
    SystemId INT NOT NULL (PK,FK),
    InForce BIT NOT NULL, (PK),
    SystemPolicyStatusId INT NOT NULL (FK)
    )

    Although any further comments are gratefully received.

    Tuesday, February 28, 2012 4:07 PM
  • So this isn't parent child at all. This is an object (system) and a relationship between it and another object (policystatus). The relationship is defined in SystemPolicyStatus, and *also* includes the inforce bit. The problem is you need a default for InForce.

    Other than that, i am completely confused as to the rest. Could you explain in more detail, or perhaps with an example.
    Tuesday, February 28, 2012 5:24 PM
    Answerer
  • I would have thought a parent child relation ship was correct.  A system has many PolicyStatuses, but a policy status only belongs to one System..

    However I can understand the confusion, let me try to clarify with an example.

    We have System1 which has 2 status codes for inforce and 2 status codes for notInForce.

    PolicyStatusTable
    ID SystemId StatusCode InForce
    1 1 A 1
    2 1 B 1
    3 1 C 0
    4 1 D 0

    We then import a policy policy 1 which has a status code of 2 which as far as we are concerned just equals 

    With in the applciation we can look at this policy and may change the status from InForce to OutOfForce.  However we do not know the distinction between status codes C and D which are both OutOfForce status codes.  So we need one of these flagged as the default code for us to use.

    Lets say when ever we want to update a policies status belonging to System1 to OutOfForce we want to use Status Code C.  All I am really trying to work out is the best way of storing that we want to use SystemPolicyStatus.Id 3 (status C) any time we want to update a policy to OutOfForce.

    Hope that makes a little more sense, although I feel it may well have the oposit effect :S

    Tuesday, February 28, 2012 5:50 PM
  • It is usually difficult to explain just part of a model, and even harder to remember to list all the knowledge you take for granted in understand your own model.

    OK, riddle me this: Why is the system default for InForce tied to a specific policy? Why can't system itself have a default InForce status?
    Tuesday, February 28, 2012 6:07 PM
    Answerer
  • The default for InForce code should be specific to a System not a policy.

    Per system I need 1 default code for InForce and 1 default code for NotInForce.

    Wednesday, February 29, 2012 8:28 AM
  • If the default required is Code, and is associated with the System, but not Policy, and Code is an attribute (not another object), you can ADD Default_Code_Inforce and Default_Code_NotInForce to the System TABLE.

    If Code is an object, and is a child of System, the default goes in the Code TABLE, that is, the Code has the "default" attribute. If Code is an object, and is not a child of System, but is associated with System (many to many) it would go in a System_Code TABLE, that is, it is an attribute of the relationship.

    • Marked as answer by Stephanie Lv Thursday, March 8, 2012 7:29 AM
    Wednesday, February 29, 2012 1:09 PM
    Answerer