CODEWITHSUNDEEP
sql-server
motaz99
motaz99

Reputation: 491

Foreign key to part of primary key's table

I have a database with this tables Conversion and Client I want to create relation between this tables
so ID_Send in Conversion Reference to ID in Client
and ID_Receive in Conversion Reference to ID in Client

create table Conversion(ID_Send int ,
                        ID_Receive int ,
                        [Time] datetime,
                        [Message] varchar(2048),
                        primary key(ID_Send,ID_Receive,[Time])
                        )

create table Client (ID int IDENTITY(1,1) primary key,
                    [First name] varchar(500) not null,
                    [Last Name]varchar(500) not null,
                    [Birth day] datetime,
                    Gender bit not null,
                    Country  varchar(200)not null,
                    City varchar(200) ,
                    [Language] varchar(200)not null, 
                    [Chat name] varchar(500)not null ,
                    [Password] varchar (500)not null,
                    --foreign key(ID) REFERENCES Conversion (ID_Send)--there is an error 
                    )

Upvotes: 2

Views: 3451

Answers (2)

Bryan Wolfford
Bryan Wolfford

Reputation: 2182

Motazz, there can be only one Primary key in a table like you have in the Client table. to get rid of the error:

1st create the Client table, 2nd replace the code for Conversion with:

create table Conversion(ID_Send int FOREIGN KEY REFERENCES Client(ID),
                    ID_Receive int FOREIGN KEY REFERENCES Client(ID),
                    [Time] datetime,
                    [Message] varchar(2048),
                    primary key(ID_Send,ID_Receive,[Time])
                    )

Upvotes: 2

marc_s
marc_s

Reputation: 754348

If you have a compound primary key (made up of mulitple columns), all your foreign keys also must use all columns of the PK to reference that table.

After all : how else would you be able to make a clear, deterministic reference from a child table to the parent? Only if you use the columns that uniquely identify one row in the parent table does this work.

The only workaround would be to put a UNIQUE index on the ID_Send and ID_Receive columns in your parent table and then reference that unique index.

But then the question is: if those values are unique - why isn't one of those columns alone your primary key??

Upvotes: 2

Related Questions