CODEWITHSUNDEEP
sqlsql-serverforeign-keys
V.M
V.M

Reputation: 63

Creating a Foreign Key between two tables in SQL

I'm trying to join two tables together using a foreign key. I am getting the error

There are no primary keys in the referenced table that match the referencing column list

I'm unsure of what I'm missing. 

if exists(select * from sysobjects where name = 'Riders')
    drop table Riders 
go

create table Riders
(
    RiderID int not null identity (10,1)
        constraint pk_Riders_RiderID primary key(RiderID,ClassID),
    [Name] nvarchar(50) not null,
        constraint chk_Riders_Name check (len(Name) > 4),
    ClassID nchar(6) not null   
)
go

if exists( select * from sysobjects where name = 'Class')
    drop table Class
go

create table Class
(
    ClassDescription nvarchar(50) not null,
    ClassID nchar(6) not null
        constraint fk_Riders_Class foreign key
        references Riders(ClassID)  on delete no action
)
go

Upvotes: 0

Views: 806

Answers (1)

Caius Jard
Caius Jard

Reputation: 74710

You seem to have your relationship the wrong way round

Class would have a primary key of ClassID; there must be no row in class that has a duplicate primary key. Class (as a table) hence decodes your class code into a class name (SNR=Senior Sport Series 1, 50CC=Fifty CC 2 Stroke Cup etc)

Riders requires a foreign key in that the Riders.ClassID column references Class.ClassID - the Classid column in Riders will have duplicated values (multiple riders are all in the same class) but the relationship you're wishing to enforce is that "no rider shall be entered into an unknown class" i.e. "no rider record shall have a classid value that is not present in the classid column of the class table"

You're hence looking for something more like:

if exists( select * from sysobjects where name = 'Class')
    drop table Class
go

create table Class
(
    ClassDescription nvarchar(50) not null,
    ClassID nchar(6) not null constraint pk_Class_ClassID primary key(ClassID)
)
go

create table Riders
(
    RiderID int not null identity (10,1)
        constraint pk_Riders_RiderID primary key(RiderID),
    [Name] nvarchar(50) not null,
        constraint chk_Riders_Name check (len(Name) > 4),
    ClassID nchar(6) not null 
        constraint fk_Riders_Class foreign key
        references Class(ClassID)  on delete no action  
)
go

Upvotes: 1

Related Questions