CODEWITHSUNDEEP
sql-server
Umair Nazir
Umair Nazir

Reputation: 27

Can a non-primary key columns be referenced from a foreign key?

Here is table one I want to refer DesignationId to other table but it is not working

create table Employees
(
    EmployeeID int identity(1,1) primary key,
    EmployeeNumber int not null,
    LocationID int not null,
    EmployeeName varchar(20) not null,
    DesignationID int not null,
    CategoryID int not null,
)

Second table is that .. on third row it is showing error

create table Designation
(
    DesignationID int primary key ,
    JobTitle varchar(20) not null,

    CONSTRAINT fk_Designation_Employees 
        FOREIGN KEY (DesignationID) 
        REFERENCES Employees (DesignationID),
)

Upvotes: 1

Views: 221

Answers (1)

Oluwafemi
Oluwafemi

Reputation: 14889

You are creating this incorrectly. Try it this way instead:

create table Designation
(
    DesignationID int primary key ,
    JobTitle varchar(20) not null,

)

create table Employees
(
    EmployeeID int identity(1,1) primary key,
    EmployeeNumber int not null,
    LocationID int not null,
    EmployeeName varchar(20) not null,
    DesignationID int not null,
    CategoryID int not null,


    CONSTRAINT fk_Employees_Designation 
        FOREIGN KEY (DesignationID) 
        REFERENCES Designation (DesignationID)
)

Many employees linked to a designation. One-To-Many relationship.

Upvotes: 4

Related Questions