SQL：外键引用复合主键

Question

我是SQL的新手，有很多事情我还不太理解。我有以下表格：

CREATE TABLE Person 
(
    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,

    PRIMARY KEY (First_Name, Name, Address)
);

我知道想要创建另一个表，该表将Person表的主键作为外键和主键：

CREATE TABLE Purchase 
(
    No_Installments int,
    Rate int,
    Person varchar(50) NOT NULL PRIMARY KEY,

    CONSTRAINT PFK 
        FOREIGN KEY (Person) REFERENCES Person (First_Name, Name, Address)
);

由于某种原因，这不起作用并且每次都会出现错误。我已经在stackoverflow上查看了其他帖子，但它们似乎并没有帮助我。我做错了什么？

- cybel

---

请标记您正在使用的数据库。 - Gordon Linoff

Answer 1

如果您有一个由三个列组成的复合主键，那么任何想要建立外键关系的子表也必须拥有这3个列，并且使用所有3个列来建立FK关系。

FK-PK关系是一个全有或全无的方案—您不能仅引用主键的一部分—要么您引用所有列—或者您不引用。

CREATE TABLE Purchase 
(
    No_Installments int,
    Rate int,

    Person varchar(50) NOT NULL PRIMARY KEY,

    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,

    CONSTRAINT PFK 
        FOREIGN KEY (First_Name, Name, Address) 
        REFERENCES Person (First_Name, Name, Address)
);

- marc_s

---

我怀疑在现实世界中任何人都会这样做。更好的方法是创建一个单一的主键，并对其他列强制执行唯一约束。 - user3885927

@user3885927：是的，这种相当笨拙的设置正是单独使用单列代理键的一个重要原因 - 还可以使连接更容易编写和理解！我同意 - 但我只是想展示在这种情况下需要什么（如果你不能改变它）。 - marc_s

Answer 2

在数据库中使用identity、auto_increment、serial或其他方式，创建一个整数类型的主键：

CREATE TABLE Person (
    PersonId int identity PRIMARY KEY
    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,
    CONSTRAINT unq_person_3 UNIQUE (First_Name, Name, Address)
);

然后使用身份列作为参考：

CREATE TABLE Purchase (
    PurchaseId int identity PRIMARY KEY,
    No_Installments int,
    Rate int,
    PersonId int,
    CONSTRAINT PFK 
        FOREIGN KEY (PersonId) REFERENCES Person (PersonId)
);

注意事项：

• 你真的不想处理复合主键。你是否考虑过连接会是什么样子？
• 你不想有一个主键，其中的值会发生变化。当有人改变他/她的名字时会发生什么？当有人搬家时会怎样？
• Person 不应该是 Purchases 的主键。你只允许某人进行一次购买吗？
• 正如最初所述，如何生成这样的列因数据库而异；identity 是 SQL Server 这样做的方式。

- Gordon Linoff

---

这个解决方案更贴近实际应用 :) - user3885927

Answer 3

您可能希望为每个人分配一个唯一的ID，而不是依赖于他们的姓名或需要地址来保证唯一性。该ID将成为主键和外键。您的购买表也应该有自己的ID作为其主键，否则由于主键必须唯一，每个人只能有一次购买记录。

CREATE TABLE Person (
   id serial NOT NULL,
   First_Name varchar(20) NOT NULL,
   Name varchar(20) NOT NULL,
   Address varchar(50) NOT NULL,
   PRIMARY KEY (id));

CREATE TABLE Purchase (
   id serial NOT NULL,
   No_Installments int,
   Rate int,
   Person int NOT NULL,
   FOREIGN KEY (Person) REFERENCES Person (id), 
   PRIMARY KEY (id));

- Brad Parker