SQL Server Foreign key/Insert issue

Question

I created two database tables. SQL Server will not allow me to insert items into my second table, the one with the foreign key. The error I keep getting is

Msg 207, Level 16, State 1, Line 53 Invalid column name 'ExemNonExemStat'.

Following is my code. Please help, this is driving me crazy.

----Create Job Title Table
CREATE TABLE [WILLKudlerFineFoods].dbo.tblJobTitle
(
EmpeeJobID char (10) CONSTRAINT PK_EmpeeJobID PRIMARY KEY Not Null,
EmplerInfRep char (200) Not Null,
EmpeeJobTitle char (20) Not Null,
EmpeeJobDesc char (200) Not Null,
ExemNonExemStat char (20) Not Null
)

----Create Employee Table
CREATE TABLE [WILLKudlerFineFoods].dbo.tblEmployee
(
EmpeeID char (6) CONSTRAINT PK_EmpeeJobID PRIMARY KEY Not Null,
EmpeeJobID char (10) 
      CONSTRAINT FK_EmpeeJobID FOREIGN KEY (EmpeeJobID)
          REFERENCES tblJobTitle (EmpeeJobID) Not Null,
EmpeeLName char (25) Not Null,
EmpeeFName char (20) Not Null,
EmpeeAdd char (100) Not Null,
EmpeeCity char (100) Not Null,
EmpeeState char (100) Not Null,
EmpeeTeleAreaCode char (3) Not Null,
EmpeeTeleNum char (7) Not Null,
EmplerInfRep char (200) Not Null,
EmpeeHirDate datetime Not Null,
EmpeeSal numeric (8) Not Null,
EmpeeGend char (6) Not Null,
EmpeeAge char (3) Not Null
)

--Insert record 1 into Job Title table
INSERT INTO [WILLKudlerFineFoods].dbo.tblJobTitle
(
[EmpeeJobID],
[EmplerInfRep],
[EmpeeJobTitle],
[EmpeeJobDesc],
[ExemNonExemStat]
)
VALUES
(
'0000000001','Office/Clerical','Accounting Clerk','Computes, classifies, records, and verifies numerical data for use in maintaining accounting records.','Exempt'
)

--Insert record 1 into Employee table
INSERT INTO [WILLKudlerFineFoods].dbo.tblEmployee
(
[EmpeeID],
[EmpeeJobID],
[EmpeeLName],
[EmpeeFName],
[EmpeeAdd],
[EmpeeCity],
[EmpeeState],
[EmpeeTeleAreaCode],
[EmpeeTeleNum],
[EmplerInfRep],
[EmpeeHirDate datetime],
[EmpeeSal numeric],
[EmpeeGend],
[EmpeeAge]
)
VALUES
(
'000001','0000000001','Glen','Edelman','175 Bishops Lane','La Jolla','CA','619','5550199','Sales Worker','10072003','10.75','M','64','01234567'
)

Answer 1

Line 53 appears to be the first insert for tblJobTitle.

I'd try a couple of things:

• sp_help tblJobTitle to make sure the column actually exists in the table.

• Since you're using brackets around ExemNonExemStat in the insert, try using them when creating the table as well. It shouldn't make a difference, but something is definitely odd here so it pays to be absolutely sure.

• execute each query one at a time to be absolutely sure where the error is happening.

• I don't think this has anything to do with the foreign key, but try removing that as well.

Answer 2

There are a few errors and a few eybrow-raisers in your script. Here's the script I got to work on my instance of SQL Server 2008:

/*
DROP TABLE tblEmployee
GO
DROP TABLE tblJobTitle
*/
----Create Job Title Table
CREATE TABLE tblJobTitle
(
    EmpeeJobID char (10) CONSTRAINT PK_EmpeeJobID PRIMARY KEY Not Null,
    EmplerInfRep char (200) Not Null,
    EmpeeJobTitle char (20) Not Null,
    EmpeeJobDesc char (200) Not Null,
    ExemNonExemStat char (20) Not Null
)

----Create Employee Table
CREATE TABLE tblEmployee
(
    EmpeeID char (6) CONSTRAINT PK_EmpeeID PRIMARY KEY Not Null,
    EmpeeJobID char (10) CONSTRAINT FK_EmpeeJobID FOREIGN KEY (EmpeeJobID)REFERENCES tblJobTitle (EmpeeJobID) Not Null,
    EmpeeLName char (25) Not Null,
    EmpeeFName char (20) Not Null,
    EmpeeAdd char (100) Not Null,
    EmpeeCity char (100) Not Null,
    EmpeeState char (100) Not Null,
    EmpeeTeleAreaCode char (3) Not Null,
    EmpeeTeleNum char (7) Not Null,
    EmplerInfRep char (200) Not Null,
    EmpeeHirDate datetime Not Null,
    EmpeeSal numeric (8) Not Null,
    EmpeeGend char (6) Not Null,
    EmpeeAge char (3) Not Null
)

--Insert record 1 into Job Title table
INSERT INTO tblJobTitle
(
[EmpeeJobID],
[EmplerInfRep],
[EmpeeJobTitle],
[EmpeeJobDesc],
[ExemNonExemStat]
)
VALUES
(
'0000000001','Office/Clerical','Accounting Clerk','Computes, classifies, records, and verifies numerical data for use in maintaining accounting records.','Exempt'
)

--Insert record 1 into Employee table
INSERT INTO tblEmployee
(
    [EmpeeID],
    [EmpeeJobID],
    [EmpeeLName],
    [EmpeeFName],
    [EmpeeAdd],
    [EmpeeCity],
    [EmpeeState],
    [EmpeeTeleAreaCode],
    [EmpeeTeleNum],
    [EmplerInfRep],
    [EmpeeHirDate],
    [EmpeeSal],
    [EmpeeGend],
    [EmpeeAge]
)
VALUES
(
    '000001',
    '0000000001',
    'Glen',
    'Edelman',
    '175 Bishops Lane',
    'La Jolla',
    'CA',
    '619',
    '5550199',
    'Sales Worker',
    '8/25/09',
    '10.75',
    'M',
    '64'
)

Errors:

1. You were trying to great two PK constraints with the same name (PK_EmpeeJobID). Change the second one to PK_EmpeeID.
2. You had too many columns in the tblEmployee insert. Delete the last value, and you'll be okay.
3. You were trying to convert 10072003 to a datetime in the column EmpeeHirDate. I changed this to 8/25/09.

Suggestions:

1. You're using char fields for variable length input. Use a varchar so you don't have to rtrim(ltrim()) every column when you compare it. This way, you can index them. When you have to do a string operation on a column, the compiler can't use the index, and is forced into a table scan. This tends to be a performance bottleneck.
2. For your PKs, use: EmpeeJobID int PRIMARY KEY NOT NULL IDENTITY(1,1) so that it becomes an identity column (aka sequential). Also, int is much faster to join on. Do the same for EmpeeID.

Hopefully this gets you pointed in the right direction!