Foreign Key problems 1

[sunkentek]

Hello,
I am new to SQL and I am working on a class project,
But I just cant seem to fix this one problem and I have deleted and rebuilt this data base about a hundred times.
I fixed everything else that was bugging me but this I cant fix.

Ok, so here are the two tables I have written:

CREATE TABLE Employee
(
EmpID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
LastName NVARCHAR (30) NOT NULL,
FirstName NVARCHAR (30) NOT NULL,
Address VARCHAR (80) NOT NULL,
City VARCHAR (40) NOT NULL,
State VARCHAR (2) NOT NULL,
AreaCode CHAR (3) NOT NULL,
Telephone VARCHAR (8) NOT NULL,
EEO1Class NVARCHAR (25) NOT NULL,
HireDate DATE NOT NULL,
Salary money NOT NULL,
Gender VARCHAR (1) NOT NULL,
Age int NOT NULL,
JobID int FOREIGN KEY REFERENCES JobTitle(JobID) 
)

CREATE TABLE JobTitle
(
JobID int IDENTITY(500,10) NOT NULL PRIMARY KEY,
EEO1Class NVARCHAR (25) NOT NULL,
JobTitle NVARCHAR (50) NOT NULL,
JobDescrp NVARCHAR (255) NOT NULL,
Status CHAR (25) 
)

However If I run lets say:

SELECT *
FROM Employee

I see all the data but the Foreign Key shows up as NULL
Were as if I do :

SELECT *
FROM JobTitle

It shows the correct Keys.
Any tips, advise or suggestion please.

Thanks,

 

[sunkentek]

Sorry I almost forgot to add that I am using SQL Server 2008

 

[mrdenny]

When you entered the data into the Employee table did you put in a value for the JobId column?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[sunkentek]

No I did not, this is what the data looked like that I entered into both tables.

INSERT INTO Employee (LastName,FirstName,Address,City,State,AreaCode,Telephone,EEO1Class,HireDate,Salary, Gender,Age)
Values ('Edelman','Glenn','175 Bishops Lane','La Jolla','CA',619,555-0199,'Sales Worker','2003/10/07',$21500.00,'M',64)

INSERT INTO JobTitle (EEO1Class, JobTitle, JobDescrp, Status)
Values ('Office/Clerical', 'Accounting Clerk', 'maintains accounting records', 'Non-Exempt')

 

[sunkentek]

However I was under the impression that because the JobID key in the JobTitle Table would create the ID key and it should then populate the Foreign Key column of my Table Employee?

 

[mrdenny]

No it won't populate it automatically. You have to tell the SQL Server what value you want put into the JobId column. The JobTitle table will have multiple records in it, so there's no way for the database to know which record to select.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[sunkentek]

Thanks, I think I understand now what I was trying to do vs. what I did.

Thanks for the explanation.