在 SQL 中指定表时出现问题

Question

我对数据库设计比较陌生。我有一个具有以下设计的数据库，

我已在 SQL 中指定了表，如下所示。

CREATE TABLE Piece
(identifier INT NOT NULL Unique,
 value      INT NOT NULL,
 the date it was acquited DATE,
 the date it was made DATE,
 PRIMARY KEY (identifier));

CREATE TABLE Person
(name VARCHAR(50),
 person_id INT NOT NULL Unique,
 biography VARCHAR (50),
 date of birth DATE,
 date of death DATE,
 PRIMARY KEY (person_id));

CREATE TABLE Jewel
(code INT NOT NULL Unique,
 gem type VARCHAR (50),
 weight INT,
 quality VARCHAR (50),
 color VARCHAR (50),
 description VARCHAR (50),
 PRIMARY KEY (code));

CREATE TABLE Gem
(type VARCHAR (50) NOT NULL,
 hardness INT,
 density  INT,
 FOREIGN KEY (type) references JEWEL(gem type));

CREATE TABLE Ownership
(person VARCHAR (50),
 piece  INT,
 start of ownership DATE,
 end of ownership   DATE,
 FOREIGN KEY (person) references PERSON(person_id),
 FOREIGN KEY (piece) references PIECE(identifier)); 

我的问题是

1.) 如何指定 GEM 表的主键，因为所有 3 个属性都不是唯一的，我是否应该创建一个像 Gem_id 这样的新属性，因为我更愿意使用现有属性而不是添加新属性。

2.）我在 person 表中使用了 person_id 属性来使其唯一并将其用作主键，是否有另一种方法可以为 person 表创建主键而不添加额外的属性，显然我不能包含对现有属性并使其唯一

3.) 所有完整性约束和数据类型都正确吗？我的设计有什么缺陷吗？

Answer 1

1）是的，您可以创建多列主键（复合主键），但它将是唯一的。像这样的事情应该可以做到：

CREATE TABLE Gem
(type VARCHAR (50) NOT NULL,
 hardness INT,
 density  INT,
 PRIMARY KEY (type , hardness, density ),
 FOREIGN KEY (type) references JEWEL(gem type));

2）还有其他方法可以为 Person 表创建主键，但我不推荐它。 Person_id 是我在这里使用的。

3）我将对您的设计进行以下更改：

• 将所有权表中的 Person 设置为 int 而不是 VARCHAR。如果它是 person_id 的外键，那么您不希望它是 VARCHAR。
• 在 Gem 表中添加一个 int 标识符。 GemTypeId 之类的。
• 在 gem 表中，将 gem type 列替换为新的 GemTypeId 列。

可能还有更多，但这些是让我震惊的。

看起来您试图不使用 ID 来表示您的数据。这是有原因的吗？如果您继续沿着这条路走下去，您可能会遇到数据完整性问题。