Referencing Composite Primery Key

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
)

Answer 1

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

Answer 2

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

Answer 3

>>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

Answer 4

>>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