外键未填充主键值 [英] Foreign Key Not Populating with Primary Key Values

问题描述

我已经找到了答案，但没有找到答案.我有2张桌子.两者都有一个自动生成的PK.表2中的PK是表1中的FK.由于它们都是自动生成的，因此我假设表1中的FK将使用表2中自动生成的值填充，但它不起作用.表1中的FK最终为空.这是我用于创建表1的SQL代码:

I have searched for an answer but am not finding it. I have 2 tables. Both have an auto-generated PK. The PK from table 2 is an FK in table 1. Since they are both auto-generated I assumed the FK in table 1 would populate with the value that is auto-generated from table 2 but it is not working. The FK in table 1 ends up null. Here is my SQL code for creating table 1:

CREATE TABLE Employee_tbl (
EmployeeID int PRIMARY KEY IDENTITY,
LastName varchar(20) not null,
FirstName varchar(20) not null,
Age varchar(3) not null,
JobID int FOREIGN KEY REFERENCES JobTitle_tbl(JobID),
)

这是表2:

create table JobTitle_tbl(
JobID int PRIMARY KEY IDENTITY,
EEO1Classification varchar(50) not null,
Exempt_nonexempt_status varchar(20) not null,
)

我也有一些插入语句:

INSERT INTO Employee_tbl
    (LastName, FirstName, Age)
Values
    ('Smith', 'John', '50'),
    ...

和:

INSERT into JobTitle_tbl (EEO1Classification, Job_title, )
VALUES ('Office/Clerical', 'Accounting Clerk', ),

为什么查询表1时FK值显示为空?

Why is the FK value showing null when I query table 1?

推荐答案

外键不会自动填充，因为它不知道要使用什么外键.您需要将行插入JobTitle_tbl表中，然后选择ID退回(如果使用SQL Server，则使用@@ identity)

The foreign keys will not auto-populate, as it doesn't know what foreign key to use. You need to either insert the rows into the JobTitle_tbl table, then select the IDs back out (or use @@identity if using sql server)

select id from JobTitle_tbl where Job_title = ''

另一种选择是更新插入语句以包括主键，尽管您必须首先允许标识插入.

Another option would be to update your insert statements to include the primary key, although you'll have to allow identity inserts first.

SET IDENTITY_INSERT JobTitle_tbl ON
into the JobTitle_tbl (id, title) values (1, 'Manager')
SET IDENTITY_INSERT JobTitle_tbl OFF

无论哪种情况，都需要使用您拥有的ID更新第一条插入语句.

In either case, you'll need to then update your first insert statements with the ID that you have.

insert into Employee_tbl (LastName, FirstName, JobID) values ('Smith', 'John', 1)

这篇关于外键未填充主键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！