Primary-Foreign Key

  • Thanks for the perspective

  • Hi All,

    I ran across the issue which is stated in this topic. Two tables having primary key as foreign key to each other.

    My problem is I want to insert records in both tables with a single insert statement.

    Something like this Insert Into Table1(1,

    Select PKID From Table2 WHERE PKID = Insert Into Table2(1, ?)

    )

    Is this Possible ?

    What is the best way to go about it ?

    ----------------------

    DB Example :

    ----------------------

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Table1](

    [PKID] [int] NOT NULL,

    [FKID] [int] NULL,

    CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

    (

    [PKID] ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Table2](

    [PKID] [int] NOT NULL,

    [FKID] [int] NULL,

    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED

    (

    [PKID] ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Table2] FOREIGN KEY([FKID])

    REFERENCES [dbo].[Table2] ([PKID])

    GO

    ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Table2]

    GO

    ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [FK_Table2_Table1] FOREIGN KEY([FKID])

    REFERENCES [dbo].[Table1] ([PKID])

    GO

    ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table1]

    Regards,

    Keshav

  • keshabheda (4/21/2011)


    Hi All,

    I ran across the issue which is stated in this topic. Two tables having primary key as foreign key to each other.

    My problem is I want to insert records in both tables with a single insert statement.

    Something like this Insert Into Table1(1,

    Select PKID From Table2 WHERE PKID = Insert Into Table2(1, ?)

    )

    Is this Possible ?

    What is the best way to go about it ?

    You would have to do two inserts. Although you can insert to two tables with one statement you cannot do that if there is a foreign key constraint between them.

    In your case both foreign keys are nullable so you have the option of inserting nulls into both tables and then updating the foreign key with the correct values. An alternative is to create a new table for the two foreign key columns and remove them from Table1 and Table2.

Viewing 3 posts - 16 through 17 (of 17 total)

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