SQL Server弱实体表插入失败：非空列Cust_ID无法插入NULL值

我正在使用SQL Server，创建了如下Payment弱实体表：

CREATE TABLE Payment(
    Cust_ID                 CHAR(4),
    Credit_Card_Number      CHAR(16),
    Payment_Number          INTEGER,
    Date                    DATE,
    Fee                     MONEY,
    PRIMARY KEY (Cust_ID, Credit_card_number, Payment_number))

ALTER TABLE Payment
    ADD CONSTRAINT FK_CustPays
        FOREIGN KEY (Cust_ID) 
        REFERENCES Customer(Cust_ID) ON DELETE CASCADE
; 

ALTER TABLE Payment
    ADD CONSTRAINT FK_CardPayment
        FOREIGN KEY (Credit_Card_Number) 
        REFERENCES Credit_card(Credit_card_number) ON DELETE CASCADE
;

执行如下INSERT语句时：

Insert into Payment(Payment_number,Date,Fee) Values ('918702','2016-08-12',93);

报错：

Cannot insert the value NULL into column 'Cust_ID', table 'DB139.dbo.Payment'; column does not allow nulls. INSERT fails.

我用同样方式插入其他表数据成功，但另外两个弱实体表Savings_account和Checking_account也出现此问题。我未插入NULL值，不清楚报错原因，现附上相关表的建表语句寻求帮助：

CREATE TABLE  Account(  
   Account_number           CHAR(10),
   Balance                  MONEY,
   Name_of_bank             VARCHAR(50),
   Date_of_creation         DATE,
   PRIMARY KEY  (Account_number))

ALTER TABLE Account
    ADD Account_number CHAR(16)
    CONSTRAINT FK_AccountNumberDouble
        FOREIGN KEY (Credit_card_number) 
        REFERENCES Credit_card(Credit_card_number) ON DELETE CASCADE
;

ALTER TABLE Account
    ADD Cust_ID CHAR(4)
    CONSTRAINT FK_CustIDTriple
        FOREIGN KEY (Cust_ID) 
        REFERENCES Customer(Cust_ID) 
;    

CREATE TABLE Savings_account(
  Account_number      CHAR(10),
  Interest_rate       INTEGER,
  PRIMARY KEY  (Account_number))

ALTER TABLE Savings_account
    ADD CONSTRAINT FK_SavingsAccount
        FOREIGN KEY (Account_number) 
        REFERENCES Account(Account_number) 
;    

CREATE TABLE Checking_account(
  Account_number        CHAR(10),
  Overdraft_limit       MONEY,
  PRIMARY KEY  (Account_number))

ALTER TABLE Checking_account
    ADD CONSTRAINT FK_CheckingAccount
        FOREIGN KEY (Account_number) 
        REFERENCES Account(Account_number) 
;

原因分析 #

1. Payment表的核心问题：Cust_ID和Credit_Card_Number是复合主键的一部分，SQL Server中主键列默认强制非空。你的INSERT语句未指定这两列的值，数据库会自动尝试插入NULL，直接触发非空约束报错。另外Payment_number定义为INTEGER类型，插入时用字符串引号包裹值也是不合理的。
2. Account表的结构错误：你执行的ALTER语句存在两处致命问题——重复添加同名主键列Account_number，且外键引用的Credit_card_number列在Account表中根本不存在，这会导致Account表结构异常，间接引发子表插入失败。
3. 其他弱实体表的关联问题：Savings_account和Checking_account依赖Account表，但Account表的Cust_ID列默认非空，若插入Account数据时未提供该值，同样会触发类似报错。

解决步骤 #

1. 修复Payment表的插入语句 #

必须明确提供复合主键的所有列值，同时修正Payment_number的类型问题：

Insert into Payment(Cust_ID, Credit_Card_Number, Payment_number, Date, Fee) 
Values ('C001', '1234567890123456', 918702, '2016-08-12', 93);

2. 重构Account表及关联子表 #

先清理错误的表结构，重新创建包含合法关联的表：

-- 先删除依赖表和原表
DROP TABLE IF EXISTS Savings_account;
DROP TABLE IF EXISTS Checking_account;
DROP TABLE IF EXISTS Account;

-- 重新创建Account表，直接包含所需外键列
CREATE TABLE Account(  
   Account_number           CHAR(10),
   Balance                  MONEY,
   Name_of_bank             VARCHAR(50),
   Date_of_creation         DATE,
   Credit_card_number       CHAR(16), -- 新增外键关联列
   Cust_ID                  CHAR(4),   -- 新增客户关联列
   PRIMARY KEY  (Account_number),
   CONSTRAINT FK_AccountCreditCard FOREIGN KEY (Credit_card_number) REFERENCES Credit_card(Credit_card_number) ON DELETE CASCADE,
   CONSTRAINT FK_AccountCustomer FOREIGN KEY (Cust_ID) REFERENCES Customer(Cust_ID)
);

-- 重新创建子表，直接包含外键约束
CREATE TABLE Savings_account(
  Account_number      CHAR(10),
  Interest_rate       INTEGER,
  PRIMARY KEY  (Account_number),
  CONSTRAINT FK_SavingsAccount FOREIGN KEY (Account_number) REFERENCES Account(Account_number)
);    

CREATE TABLE Checking_account(
  Account_number        CHAR(10),
  Overdraft_limit       MONEY,
  PRIMARY KEY  (Account_number),
  CONSTRAINT FK_CheckingAccount FOREIGN KEY (Account_number) REFERENCES Account(Account_number)
);

3. 插入关联表的注意事项 #

• 插入Account数据时，必须提供Cust_ID和Credit_card_number的有效值（两列均非空）
• 插入Savings_account或Checking_account之前，确保对应的Account_number已存在于Account表中

内容的提问来源于stack exchange，提问作者Jim