Created Primary Key on SQL Server not Identified

Question

CREATE TABLE sampProduct
(
    Product_ID   VARCHAR(15) NOT NULL,
    Supplier_ID  INT NOT NULL,
    Category_ID  INT NOT NULL,
    Unit_Price   DECIMAL(10, 2)
    PRIMARY KEY(Product_ID)
)
CREATE TABLE sampMachine
(
    M_Product_ID   VARCHAR(15) NOT NULL,
    Serial_No      VARCHAR(15) NOT NULL,
    Machine_Model  VARCHAR(20),
    PRIMARY KEY(M_Product_ID, Serial_No),
    FOREIGN KEY(M_Product_ID) REFERENCES sampProduct(Product_ID)
)
CREATE TABLE sampService
(
    Service_ID  VARCHAR(15) NOT NULL,
    Serial_No   VARCHAR(15) NOT NULL,
    Complaint   VARCHAR(40) NOT NULL,
    PRIMARY KEY(Service_ID),
    FOREIGN KEY(Serial_No) REFERENCES sampMachine(Serial_No)
)

Machine is a subtype of Product. M_ProductID is referencing the primary key of the parent table Product by this key. And Serial_No is used to uniquely identify a Machine. The problem is, I want to create a foreign key from Serial_No into the Service table, since I want to track the serial numbers of the machine (this is because, for example, we can have many machine for each model or type, but each of them has their unique serial numbers), but there is an error for the creation. Can you help me out?

Answer 1

One of the requirements for creating a foreign key is that the table you reference must have a unique key on the columns you want referenced. So in your example in order for it to work you would need a unique key on sampMachine (Serial_No) so that you could create foreign keys to it using just Serial_No. The way you have it now you would need to use M_Product_Id in the table sampService and add it to the foreign key definition.

create table sampService(

M_Product_ID varchar(15) not null,
Service_ID varchar(15) not null,
Serial_No varchar(15) not null,
Complaint varchar(40) not null,
primary key (Service_ID),
foreign key (Serial_No) references sampMachine(m_Product_Id, Serial_No)
)