SQL Server外键约束问题

Question

我有两个表：

1. 狗
2. 狗主人

我正在尝试从DogOwner创建一个外键到Dog，但不是在dog表的主键上。 我计划为dog表的dog_id列创建自己的唯一ID。以下是模式：

CREATE TABLE dog(
    id          INT NOT NULL identity(1,1),
    dog_id      INT NOT NULL,
    dog_name    VARCHAR (200) NOT NULL,
    create_date DATETIME NOT NULL,
    update_date DATETIME DEFAULT getutcdate(),
    start_date  DATETIME DEFAULT getutcdate(),
    end_date    DATETIME DEFAULT getDate() - 101,
    is_active   BIT NOT NULL DEFAULT '0',
    PRIMARY KEY (id,dog_id)
    );

CREATE TABLE dogowner(
    dogowner_id  INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    dog_id       INT NOT NULL,
    FOREIGN KEY (dog_id) REFERENCES dog(dog_id)
    );

一旦我在dogowner表上创建外键约束，就会出现以下错误：

There are no primary or candidate keys in the referenced table 'dog' that match the referencing column list in the foreign key 'FK__dogowner__dog_id__00AA174D'.

> 更新：

最终我放弃了复杂的模式设计，选择在需要版本控制的每个表上使用历史表。因此，狗表将有一个名为dog_history或dog_log的历史表，其中包含对所有历史表进行的插入/更新操作。

虽然这不是我想要的方式，但它允许我在数据库中拥有外键约束、软删除和数据记录。感谢所有人的意见。我遵循KISS原则。

- Chesneycar

---

错误非常明显。您正在尝试将dogowner中的dog_id指向dog中的一个键。在dog表中唯一存在的键是id，dog_id上的主组合键。因此，它无法将dog_id与dog表中的任何键匹配。 - SchmitzIT

顺便问一下，你为什么需要/想要添加自己的id列呢？ - SchmitzIT

嗨@SchmitzIT，这是为了版本控制在表上的目的。我计划在任何更新/删除时保留记录的历史。我可以在mySQL中轻松实现这样的功能。我想在dogowner表中使用dog_id作为我的外键。 因此，在dog表上进行软删除时，记录将被标记为非活动状态（is_active = 0），对于更新，将在dog表中插入一个新记录，但dog_id将保持不变。 - Chesneycar

Answer 1

狗ID字段需要是唯一的，以下方法可行：

create table dog(
id int not null identity(1,1),
dog_id int unique not null,
dog_name varchar(200) not null,
create_date datetime not null ,
update_date datetime default getutcdate(),
start_date datetime default getutcdate(),
end_date datetime default getDate() - 101,
is_active bit not null default '0',
primary key(id,dog_id)
);

create table dogowner(
dogowner_id int not null identity(1,1) primary key,
dog_id int not null,
foreign key(dog_id) references dog(dog_id)
);

来自微软文档:

您可以使用UNIQUE约束来确保特定列中不会输入重复值，而这些列不参与主键。虽然UNIQUE约束和PRIMARY KEY约束都强制唯一性，但是当您想要强制实现非主键列或列组的唯一性时，请使用UNIQUE约束而不是PRIMARY KEY约束。

希望这可以帮助到您。

- Milica Medic Kiralj

---

嗨@Milica，唯一键对我的目的不起作用。我想在dog表上保留版本历史记录。相同的dog_id将多次插入到dog表中。例如。 insert into dog(dog_id,dog_name,create_date) values(1,'joe',GETUTCDATE()); insert into dog(dog_id,dog_name,create_date) values(1,'max',GETUTCDATE());插入语句意味着名为“Joe”的狗被更改为“Max”。 - Chesneycar

但是你不能使用外键引用 dog_id，因为被引用的列必须是唯一的。 - Milica Medic Kiralj

非常感谢您的答案。我已经更新了我的问题，并为版本控制我的数据库解决了问题。您的答案确实解决了我从SQL Server收到的错误，因此我将其标记为“已解决”。 - Chesneycar

Answer 2

首先在dog表的dog_id字段上创建唯一键并对其创建唯一约束，然后您可以将此唯一键作为外键引用到Dog_owner表中。

- user3714403