CODEWITHSUNDEEP
sqlsql-server
kaviya .P
kaviya .P

Reputation: 479

SQl Server - unable to refer composite primary key

I am new to SQL server,I have the table with composite primary keys as follows:

CREATE TABLE Systems
(
Layer VARCHAR(25),
System_Name VARCHAR(25) ,
Sub_System_Name VARCHAR(25),
Q1_Scope VARCHAR(25),
Q2_Scope VARCHAR(25),
Q3_Scope VARCHAR(25),
Q4_Scope VARCHAR(25),
U20 VARCHAR(25),
Extracts_Requested VARCHAR(25),
Extracts_Received VARCHAR(25),
Control VARCHAR(25),
Extracts_Reviewed VARCHAR(25),
Control_Frequency VARCHAR(25),
System_Layer_Exists VARCHAR(25),
Notes VARCHAR(25),
Alias VARCHAR(25),
Extract_Requested_Date VARCHAR(25),
PRIMARY KEY (Layer,System_Name,Sub_System_Name)
);

Now I want to refer the columns like Layer,System_Name,Sub_System_Name in to another table say:

CREATE TABLE Owns_System
(
Name VARCHAR(25) FOREIGN KEY REFERENCES Employees(Name),
 Layer VARCHAR(25) FOREIGN KEY REFERENCES Systems(Layer),
System_Name VARCHAR(25) FOREIGN KEY REFERENCES Systems(System_Name),
Sub_System_Name VARCHAR(25) FOREIGN KEY REFERENCES Systems(Sub_System_Name),
Role VARCHAR(25)
);

It shows me the error that the table Systems

"There are no primary/candidates keys in the table Systems".

Can anyone please suggest me what I am doing wrong here.

Upvotes: 1

Views: 65

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table

So Before referring a column (PKey) in one table (Say TableA) as Foreign key in another Table (TableB), You need to make sure that the Column PKey is the Primary Key / Unique Key in TableA.

The Table Design Might be something like this

CREATE TABLE TableA
(
PKey INT PRIMARY KEY,
FullName VARCHAR(50),
Email VARCHAR(255) NOT NULL UNIQUE
)

CREATE TABLE TableB
(
Id INT PRIMARY KEY,
FKey INT FOREIGN KEY References TableA(PKey),
Email VARCHAR(255) FOREIGN KEY References TABLEA(Email)
)

Please Refer this W3 Schools link for more in detail on Foreign key

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You don't want to declare 3 separate foreign key constraints - you need to use a single composite foreign key:

CREATE TABLE Owns_System
(
Name VARCHAR(25) FOREIGN KEY REFERENCES Employees(Name),
Layer VARCHAR(25),
System_Name VARCHAR(25),
Sub_System_Name VARCHAR(25),
Role VARCHAR(25),
constraint FK_Blah FOREIGN KEY (Layer,System_Name,Sub_System_Name)
     REFERENCES Systems(Layer,System_Name,Sub_System_Name)
);

Upvotes: 1

Related Questions