Post reply

Enforce Foreign Key Constraints ON / OFF

  • January 21, 2009 at 5:32 am

    #103660

    Hi there

    I have two Tables with the following

    AUD_Table

    TableID (PK)

    PrimaryKeyFieldID (FK)

    AUD_TableField

    FieldID (PK)

    TableID (FK)

    There are two constraints

    AUD_Table.TableID -> AUD_TableField.TableID

    AUD_TableField.FieldID -> AUD_Table.PrimaryKeyFieldID

    I am wanting to create a script to insert some values into these tables.

    ie INSERT INTO AUD_Table(TableID, PrimaryFieldID) VALUES (1,1)

    but can't because the 1 value does not exist yet

    The only way around this is to drop the constraints do the inserts and then add the constraints again (which Im not too keen on doing.

    Looking through the designer I can toggle the constraints ON and OFF by going to Design (in SQL server Management Studio 2008) on the table, right clicking then Relationships. I can alter the properties on "Enforce Foreign Key Constraint"

    Is there a way I can script this functionality to turn on or off rather than droping and recreating the constraints?

  • January 21, 2009 at 6:26 am

    #930267

    These types of circular references are typically a bad design. I understand why you want to do this in your design, but you may want to re-think the design a bit.

    Your first option is to make the PrimaryKeyFieldID in AUD_Table nullable. You could then insert the AUD_Table record with a NULL PrimaryKeyFieldID, insert the AUD_TableField records, get the PK value, and then update the AUD_Table record:

    [font="Courier New"]BEGIN TRAN

    INSERT AUD_Table (1,NULL)

    INSERT AUD_TableField (1,1)

    INSERT AUD_TableField (2,1)

    UPDATE AUD_Table SET PrimaryKeyFieldID = 1 WHERE TableID = 1

    COMMIT TRAN[/font]

    Another option would be to move your "constraint" information into another table.

    [font="Courier New"]CREATE TABLE AUD_Table

    (TableID INT NOT NULL)

    CREATE TABLE AUD_TableField

    (FieldID INT NOT NULL, TableID INT NOT NULL)

    CREATE TABLE AUD_ConstraintType

    (ConstraintTypeID INT NOT NULL, ConstraintTypeDesc VARCHAR(10) NOT NULL)

    CREATE TABLE AUD_TableFieldConstraint

    (FieldID INT NOT NULL, TableID INT NOT NULL, ConstraintTypeID IS NOT NULL)[/font]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply