Post reply

Create a foreign key constraint

  • March 4, 2011 at 12:11 pm

    #142378

    I have created a new table A

    Table A

    id

    Bid

    bsequence

    description

    Table A is using Table Bs id and sequence

    Table B

    Bid

    Bsequnece

    .

    .

    I am trying to create a foreign key constraint by clicking on modify table and add relationship.When I try saving it says cannot create constraint.

    Please help me how to create this constraint.

    Thanks

  • March 4, 2011 at 1:54 pm

    #1294879

    a foreign key can only be created on a a unique or primary key.

    that unique constraint or primary key constraint can span multiple columns.

    so if you changed your table to something like this definition:

    CREATE TABLE [Table A] (

    id int identity(1,1) NOT NULL ,

    Bid int,

    bsequence int not null,

    description varchar(30),

    constraint PK_TWOCOLS PRIMARY KEY(id, bsequence) )

    then you can create a foreign key

    ALTER TABLE

    ADD

    CONSTRAINT FK__Whatever FOREIGN KEY (id,bsequence ) REFERENCES [Table A](Bid,bsequence )

    i try to make my FK's point to a single column, typically an identity primary key wherever possible.

    i also make sure the column name in the child tables are always the exact same name as the columns they are referencing; much easier to validate later.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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