Creating table with a compound key + a foreign key as compound key

I am having a problem with creating my database in SQL Server Compact

I basically have 5 tables ( CentreDeCout, Prof开发者_如何学Goil, Groupe, CCProfilPivot, CCProfilGroupePivot)

CentreDeCout has NomCC as primary key

Profil has NomProfil as primary key

CCProfilPivot has NomCC and NomProfil as primary key ( referenced to CentreDeCout and Profil primary key)

Groupe has NomGroupe as primary key

and I need CCProfilGroupePivot to have the primary key of CCprofilPivot and NomGroupe as its primary key.

So the primary key would be NomCC, NomProfil and NomGroupe.

NomCC and NomProfil would be taken from CCProfilPivot and NomGroupe from Groupe

This is how I did it

  
CREATE TABLE CentreDeCout(
NomCC nvarchar(100) primary key
)

CREATE TABLE Profil(
NomProfil nvarchar(100) primary key
)

CREATE TABLE Groupe(
NomGroupe nvarchar(100) primary key
)

CREATE TABLE CCProfilPivot(
NomCC nvarchar(100) references CentreDeCout(NomCC),
NomProfil nvarchar(100) references Profil(NomProfil),
PRIMARY KEY(NomCC,NomProfil)
)


CREATE TABLE CCProfilGroupePivot(
NomCC nvarchar(100) references CCProfilPivot(NomCC),
NomProfil nvarchar(100) references CCProfilPivot(NomProfil),
NomGroupe nvarchar(100) references Groupe(NomGroupe),
PRIMARY KEY (NomCC,NomProfil,NomGroupe)
)

The last create table gives me this error

SQL Exercution Error.


Exercuted SQL statement: CREATE TABLE CCProfilGroupePivot(
NomCC nvarchar(100) references CCProfilPivot(NomCC),
NomProfil nvarchar(100) references CCProfilPivot(NomProfil),
NomGroupe nvarchar(100) references Groupe(NomGroupe),
PRIMARY KEY (NomCC,NomProfil,NomGroupe)
)

error Source: SQL Server Compact ADO.Net Data Provider
Error Message: The referenced table must have a primary or candidate key.[FK Name = FK_CCProfilGroupePivot__00000000000000D7]

Can anyone please help me?

Thank you Gibit

You have to reference both columns of a compound key at the same time.

CREATE TABLE CCProfilGroupePivot(
  NomCC nvarchar(100) not null,
  NomProfil nvarchar(100) not null,
  NomGroupe nvarchar(100) references Groupe(NomGroupe),
  PRIMARY KEY (NomCC,NomProfil,NomGroupe),
  foreign key (NomCC, NomProfil) 
    references CCProfilPivot (NomCC, NomProfil)
);