修复外键问题:被引用的表中没有主键或候选键

Question

CREATE TABLE Customer_MDM.membership_registration
(
registration_num INT Primary key,
registration_location VARCHAR(25),
customerID INT /* FOREIGN KEY Customer details */,
first_name VARCHAR(25),
last_name VARCHAR(25),
dob DATE,
marital_status VARCHAR(25),
gender VARCHAR(25),
occupation VARCHAR(25),
income_level VARCHAR(25),
date_enrolled VARCHAR(25),
phone_number VARCHAR(25),
email VARCHAR(30),
city VARCHAR(25),
state VARCHAR(25),
zipcode INT
);

:

CREATE TABLE Customer_MDM.loyalty_data
(
customerID INT,
loyalty_level INT,
customer_age INT,
reward_points INT,
average_amount_spend INT,
first_name VARCHAR(25) /* FOREIGN KEY ('Membership_registration') */,
last_name VARCHAR(25) /* FOREIGN KEY ('Membership_registration') */,
dob DATE /* FOREIGN KEY ('Membership_registration') */,
marital_status VARCHAR(25) /* FOREIGN KEY ('Membership_registration') */,
gender VARCHAR(25) /* FOREIGN KEY (‘Membership_registration') */,
occupation VARCHAR(25) /* FOREIGN KEY (Membership_registration'') */,
income_level VARCHAR(25) /* FOREIGN KEY (‘Membership_registration') */, 
city VARCHAR(25) /* FOREIGN KEY ('Membership_registration') */,
state VARCHAR(25) /* FOREIGN KEY ('Membership_registration’) */,
zipcode INT /* FOREIGN KEY ('Membership_registration') */,
PRIMARY KEY(customerID,loyalty_level)
);

最后:

ALTER TABLE Customer_MDM.loyalty_data 
ADD FOREIGN KEY (first_name) 
REFERENCES Customer_MDM.membership_registration(first_name);

尝试执行ALTER TABLE语句，我得到这些错误:

Msg 1776, Level 16, State 0, Line 42
在引用的表Customer_MDM中没有主键或候选键。membership_registration'匹配外键'FK__loyalty_d__first__634EBE90'中的引用列列表

Msg 1750, Level 16, State 1, Line 42
无法创建约束或索引。见前面的错误。

Answer 1

只需为成员表创建一次外键就可以了。然后可以很容易地引用所有其他列。

CREATE TABLE Customer_MDM.loyalty_data (
customerID INT,
loyalty_level INT,
customer_age INT,
reward_points INT,
average_amount_spend INT,
registration_num INT,
PRIMARY KEY(customerID,loyalty_level),
FOREIGN KEY (registration_num) REFERENCES Customer_MDM.membership_registration(registration_num)
);

Answer 2

首先需要在membership_registration表中创建一个关于名字的唯一索引。希望你不是用名字作为外键这只是在测试外键

Answer 3

创建表membership_registration时，需要确保列first_name是一个键。可以是主键，也可以是唯一约束。

由于表已经有一个主键，所以可以用唯一约束定义这个列。例如:

CREATE TABLE Customer_MDM.membership_registration(
registration_num INT Primary key,
registration_location VARCHAR(25),
customerID INT /* FOREIGN KEY Customer details */,
first_name VARCHAR(25),
last_name VARCHAR(25),
dob DATE,
marital_status VARCHAR(25),
gender VARCHAR(25),
occupation VARCHAR(25),
income_level VARCHAR(25),
date_enrolled VARCHAR(25),
phone_number VARCHAR(25),
email VARCHAR(30),
city VARCHAR(25),
state VARCHAR(25),
zipcode INT,
constraint uq1 UNIQUE (first_name) -- added constraint here
);

参见运行示例:db<>fiddle。

在任何情况下，名字都不是键的好选择，因为很可能您的数据模型需要容纳多个具有相同名字的人。