主键表的部分外键

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

我有一个具有这个表转换的数据库，我想在这些表之间创建关系，以便ID_Send在客户端对ID进行转换引用，ID_Receive在客户端对ID进行转换引用

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 
                    )

2 个解决方案

#1

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

Motazz，在一个表中只能有一个主键，就像在客户端表中一样。为了消除错误:

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

1创建客户端表，2将转换代码替换为:

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])
                    )

#2

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.

如果您有一个复合主键(由多列组成)，那么所有外键也必须使用PK的所有列来引用该表。

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.

惟一的解决办法是在父表中的ID_Send和ID_Receive列上放置一个惟一的索引，然后引用那个惟一的索引。

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

但是，问题是:如果这些值是唯一的，为什么没有一个单独的列是主键?

#1