sql - Creating a foreign key to compound primary key -

i have 2 tables - educators , faculties:

create table [dbo].[educators] (     [userid] [nvarchar](128) not null,     [facultyid] [smallint] not null,     [institutionuserid] [nvarchar](128) not null,      constraint [pk_educators]          primary key clustered ([userid] asc) )  create table [dbo].[faculties] (     [institutionuserid] [nvarchar](128) not null,     [facultyid] [smallint] not null,      constraint [pk_userfaculties]          primary key clustered ([institutionuserid] asc, [facultyid] asc) ) 

the table faculties has compound primary key made 2 columns (institutionuserid , facultyid). have same column in educators table. want link 2 tables foreign key.

so, query:

alter table [dbo].[educators] check      add constraint [fk_educators_facultyid_faculties_facultyid]      foreign key ([facultyid], [institutionuserid])     references [dbo].[faculties] ([facultyid], [institutionuserid]) 

but getting error message:

msg 1776, level 16, state 0, line 7
there no primary or candidate keys in referenced table 'dbo.faculties' match referencing column list in foreign key 'fk_educators_facultyid_faculties_facultyid'.

msg 1750, level 16, state 1, line 7
not create constraint or index. see previous errors.

how solve problem?

your tables , constraints fine. issue can see order of columns in primary key

constraint [pk_userfaculties] primary key clustered  (     [institutionuserid] asc,     [facultyid] asc ) 

is different order you've declared in foreign key constraint

alter table [dbo].[educators]  check add  constraint [fk_educators_facultyid_faculties_facultyid] foreign key([facultyid], [institutionuserid]) references [dbo].[faculties] ([facultyid], [institutionuserid]) 

try changing order of columns in declaration of foreign key this:

alter table [dbo].[educators]  check add  constraint [fk_educators_facultyid_faculties_facultyid] foreign key([institutionuserid], [facultyid]) references [dbo].[faculties] ([institutionuserid], [facultyid])