locked
Referencing Composite Primery Key RRS feed

  • Question

  • How can I reeference a composite key in a foreign key

    CREATE TABLE SATICI(
    SATICI_KODU CHAR(10) PRIMARY KEY(SATICI_KODU),
    ADI VARCHAR(50),
    TELEFON_NO CHAR(20),
    ADRES CHAR(100)
    )

    CREATE TABLE Malzeme_Satar(
    MAL_KODU CHAR(10),
    AD CHAR(20),
    MİKTAR INT,
    AÇIKLAMA CHAR(300),
    TARİH DATETIME,
    ADET INT,
    SATICI_KODU CHAR(10) FOREIGN KEY REFERENCES SATICI,
    PRIMARY KEY(SATICI_KODU,MAL_KODU)
    )

    CREATE TABLE ÜRÜN(
    ÜRÜN_KODU CHAR(10) PRIMARY KEY(ÜRÜN_KODU),
    AD CHAR(20),
    BIRIM_FİYATI MONEY,
    STOK_MİKTARI INT
    )

    CREATE TABLE Malzeme_Kullanılır(
    SATICI_KODU CHAR(10) FOREIGN KEY REFERENCES SATICI,
    MAL_KODU CHAR(10)FOREIGN KEY(MAL_KODU) REFERENCES Malzeme_Satar,
    ÜRÜN_KODU CHAR(10) FOREIGN KEY REFERENCES ÜRÜN,
    PRIMARY KEY (SATICI_KODU,MAL_KODU,ÜRÜN_KODU),
    ADET INT
    )

    CREATE TABLE Müşteriler(
    tcno CHAR(11) PRIMARY KEY(tcno),
    AD VARCHAR(50),
    Soyad varchar(50),
    TELEFON_NO CHAR(20),
    ADRES CHAR(100),
    Eposta char(50)
    )

    CREATE TABLE SatınAlır(
    tcno CHAR(11) FOREIGN KEY REFERENCES Müşteriler,
    ÜRÜN_KODU CHAR(10) FOREIGN KEY REFERENCES ÜRÜN,
    PRIMARY KEY (tcno,ÜRÜN_KODU),
    ADET INT
    )

    Monday, December 15, 2014 6:44 AM

Answers

  • You should create the table and then use an ALTER TABLE ADD CONSTRAINT 
    ALTER TABLE Person.ContactBackup
    ADD CONSTRAINT <<FK_NAME>>FOREIGN KEY (<<Column1>>,<<Column2>>)
        REFERENCES <<Table1>> (<<COLUMN1>>, <<COLUMN2>>) ;
    GO


    Satheesh
    My Blog | How to ask questions in technical forum

    Monday, December 15, 2014 6:52 AM
    Answerer
  • >>A foreign key column can refer to only single column so the below doesnt make any sense

    Visakh, I believe that's not correct, We can create a multi-column foreign keys, though it cannot be created inline with the table creation using REFERENCES keyword

    Satheesh
    My Blog | How to ask questions in technical forum


    Sorry if I was not clear enough :)

    Yes as a constraint you can create composite FK. but even in that case foreigh key would be on composite column not on a single column as in the posted code

    So even in that case it has to be this

    ALTER TABLE Malzeme_Satar
    ADD CONSTRAINT FK_ForeginKeyName
    FOREIGN KEY (MAL_KODU,SATICI_KODU)
    REFERENCES (MAL_KODU,SATICI_KODU)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by cpluspro Thursday, December 18, 2014 7:32 PM
    Monday, December 15, 2014 7:19 AM
    Answerer

All replies

  • You should create the table and then use an ALTER TABLE ADD CONSTRAINT 
    ALTER TABLE Person.ContactBackup
    ADD CONSTRAINT <<FK_NAME>>FOREIGN KEY (<<Column1>>,<<Column2>>)
        REFERENCES <<Table1>> (<<COLUMN1>>, <<COLUMN2>>) ;
    GO


    Satheesh
    My Blog | How to ask questions in technical forum

    Monday, December 15, 2014 6:52 AM
    Answerer
  • A foreign key column can refer to only single column so the below doesnt make any sense

    SATICI_KODU CHAR(10) FOREIGN KEY REFERENCES SATICI,
    PRIMARY KEY(SATICI_KODU,MAL_KODU)

    as per column names i think what you need is this

    SATICI_KODU CHAR(10) FOREIGN KEY REFERENCES SATICI,
    PRIMARY KEY(SATICI_KODU)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, December 15, 2014 6:56 AM
    Answerer
  • >>A foreign key column can refer to only single column so the below doesnt make any sense

    Visakh, I believe that's not correct, We can create a multi-column foreign keys, though it cannot be created inline with the table creation using REFERENCES keyword

    Satheesh
    My Blog | How to ask questions in technical forum


    Monday, December 15, 2014 7:01 AM
    Answerer
  • >>A foreign key column can refer to only single column so the below doesnt make any sense

    Visakh, I believe that's not correct, We can create a multi-column foreign keys, though it cannot be created inline with the table creation using REFERENCES keyword

    Satheesh
    My Blog | How to ask questions in technical forum


    Sorry if I was not clear enough :)

    Yes as a constraint you can create composite FK. but even in that case foreigh key would be on composite column not on a single column as in the posted code

    So even in that case it has to be this

    ALTER TABLE Malzeme_Satar
    ADD CONSTRAINT FK_ForeginKeyName
    FOREIGN KEY (MAL_KODU,SATICI_KODU)
    REFERENCES (MAL_KODU,SATICI_KODU)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by cpluspro Thursday, December 18, 2014 7:32 PM
    Monday, December 15, 2014 7:19 AM
    Answerer