带外键约束的子表插入报错，求原因及解决方法

我有两个父表（UserLogins、UserSecurityQuestions）和一个子表UserSecurityAnswers，表结构定义如下：

父表 #

CREATE TABLE UserLogins
(
    UserLoginID SMALLINT NOT NULL IDENTITY(1,1),
    UserLogin VARCHAR(50) NOT NULL,
    UserPassword VARCHAR(20) NOT NULL,
    CONSTRAINT pk_UL_UserLoginID PRIMARY KEY(UserLoginID)
);


CREATE TABLE UserSecurityQuestions
(
    UserSecurityQuestionID TINYINT NOT NULL IDENTITY(1,1),
    UserSecurityQuestion VARCHAR(50) NOT NULL,
    CONSTRAINT pk_USQ_UserSecurityQuestionID PRIMARY KEY(UserSecurityQuestionID)
);

子表 #

CREATE TABLE UserSecurityAnswers
(
    UserLoginID SMALLINT NOT NULL IDENTITY(1,1),
    UserSecurityAnswers VARCHAR(25) NOT NULL,
    UserSecurityQuestionID TINYINT NOT NULL,
    CONSTRAINT pk_USA_UserLoginID PRIMARY KEY(UserLoginID), 
    CONSTRAINT fk_UL_UserLoginID FOREIGN KEY(UserLoginID) REFERENCES UserLogins(UserLoginID),
    CONSTRAINT fk_USQ_UserSecurityQuestionID FOREIGN KEY(UserSecurityQuestionID) REFERENCES UserSecurityQuestions(UserSecurityQuestionID)
);

已向父表插入数据：

UserLogins #

insert into UserLogins values('User1', 'Pass1');
insert into UserLogins values('User2', 'Pass2');
insert into UserLogins values('User3', 'Pass3');
insert into UserLogins values('User4', 'Pass4');
insert into UserLogins values('User5', 'Pass5');

UserSecurityQuestions #

insert into UserSecurityQuestions values('What is your favourite food?');
insert into UserSecurityQuestions values('What is your favourite food?');
insert into UserSecurityQuestions values('What is your favourite food?');
insert into UserSecurityQuestions values('What is your favourite food?');
insert into UserSecurityQuestions values('What is your favourite food?');

尝试向子表UserSecurityAnswers插入数据：

insert into UserSecurityAnswers values('Apples');
insert into UserSecurityAnswers values('Spiderman');
insert into UserSecurityAnswers values('School1');
insert into UserSecurityAnswers values('Ram');
insert into UserSecurityAnswers values('Toyota');

触发错误：

Column name or number of supplied values does not match table definition.

我原本以为子表会继承父表的值，不清楚当前实现存在什么问题，恳请提供帮助。

核心问题1：子表结构逻辑冲突 #

UserSecurityAnswers中的UserLoginID被设置为IDENTITY(1,1)，但该字段同时是关联UserLogins的外键。IDENTITY会自动生成递增数值，这和外键需要关联父表已存在的UserLoginID逻辑矛盾，不仅会导致外键约束无法正确生效，还会让表结构的列逻辑混乱。

另外，当前子表以UserLoginID为主键，会限制一个用户只能对应一个安全问题答案，这显然不符合业务逻辑，应该改成复合主键(UserLoginID, UserSecurityQuestionID)，确保一个用户对一个问题只能有一个唯一答案。

核心问题2：插入数据列数不匹配 #

UserSecurityAnswers包含3个非空列：UserLoginID、UserSecurityAnswers、UserSecurityQuestionID，但插入时只提供了1个值，数据库无法匹配列数，因此触发报错。

核心误解：子表不会自动继承父表值 #

关系型数据库中，外键只是约束子表的值必须存在于父表中，不会自动从父表继承数据，必须手动指定关联的父表主键值。

修正步骤 #

1. 修正子表结构 #

CREATE TABLE UserSecurityAnswers
(
    UserLoginID SMALLINT NOT NULL,
    UserSecurityAnswers VARCHAR(25) NOT NULL,
    UserSecurityQuestionID TINYINT NOT NULL,
    -- 复合主键，避免一个用户重复提交同一问题的答案
    CONSTRAINT pk_USA_UserLoginQuestionID PRIMARY KEY(UserLoginID, UserSecurityQuestionID), 
    CONSTRAINT fk_UL_UserLoginID FOREIGN KEY(UserLoginID) REFERENCES UserLogins(UserLoginID),
    CONSTRAINT fk_USQ_UserSecurityQuestionID FOREIGN KEY(UserSecurityQuestionID) REFERENCES UserSecurityQuestions(UserSecurityQuestionID)
);

2. 正确插入子表数据 #

插入时必须提供所有3个列的值，推荐明确指定列名（避免表结构变更导致列顺序错乱）：

-- 为每个用户关联对应的安全问题和答案
insert into UserSecurityAnswers (UserLoginID, UserSecurityAnswers, UserSecurityQuestionID) 
values (1, 'Apples', 1);

insert into UserSecurityAnswers (UserLoginID, UserSecurityAnswers, UserSecurityQuestionID) 
values (2, 'Spiderman', 2);

insert into UserSecurityAnswers (UserLoginID, UserSecurityAnswers, UserSecurityQuestionID) 
values (3, 'School1', 3);

insert into UserSecurityAnswers (UserLoginID, UserSecurityAnswers, UserSecurityQuestionID) 
values (4, 'Ram', 4);

insert into UserSecurityAnswers (UserLoginID, UserSecurityAnswers, UserSecurityQuestionID) 
values (5, 'Toyota', 5);

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