adding referential integrity

  • Hi,

    I was trying to add a referential integrity to the new table that I am going to create. I am getting an exception when doing this. Following are my tables.

    Table1

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

    UserId int primary key

    col1 int

    col2 int

    etc

    Table2

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

    DependentNo int

    col1 int

    col2 int

    etc

    Table3 (new table)

    -------

    UserId int primary key

    DependentNo int primary key

    Table3 is a relationship table. I want to create a constraint so that all the data in table3 will always exist in table1 and table2. How should I do this? Please note that Table2 doesn't have any primary key. I can add one if it is needed.

    Thanks,

    sridhar.

  • Hi there,

    Just wanna help you.. I think you need to make DependentNo column from Table2 a primary key to ensure referential integrity..

    This is my proposed table design..

    CREATE TABLE Table1

    (

    UserId INT PRIMARY KEY,

    col1 INT,

    col2 INT

    )

    CREATE TABLE Table2

    (

    DependentNo INT PRIMARY KEY,

    col1 INT,

    col2 INT

    )

    CREATE TABLE Table3

    (

    UserId INT FOREIGN KEY REFERENCES Table1(UserId),

    DependentNo INT FOREIGN KEY REFERENCES Table2(DependentNo),

    CONSTRAINT pk_Table3 PRIMARY KEY(Userid,DependentNo)

    )

    Please tell me if this post is useful.. 😀

    Regards,

    shield_21

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

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