sql - 修复外键问题：引用表中没有主键或候选键

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语句时出现以下错误：

消息 1776，级别 16，状态 0，第 42 行
被引用的表“Customer_MDM.membership_registration”中没有与外键“FK__loyalty_d__first__634EBE90”中的引用列列表匹配的主键或候选键

消息 1750，级别 16，状态 1，第 42 行
无法创建约束或索引。请参阅以前的错误。

标签： sqlsql-server

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