Foreign Key Not Populating with Primary Key Values

问题:

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

and here is table 2:

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

I also have some insert statements:

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

and:

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

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

回答1:

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

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)