Foreign Key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Foreign Key

Author

Topic

kevin_co
Starting Member

5 Posts

Posted - 2009-07-06 : 15:09:19

First and foremost this is a homework assignment but I believe I have completed everything that I need I just want to double check that I am doing it correctly. The assignment calls for two tables to be created and Employee Table and a Job Title table. The employee table has all the fields a regular employee table would have, name, address, etc. and the last field requirement states "Foreign key from Job Title table. The Job Title table has 4 fields Classification, Job Title, Job Description, and Status. Here is what I have done.

This is the code for the Job Title Table


USE KudlerFineFoods

CREATE TABLE JobTitle
(
	EE01Classification				varchar(50)					NOT NULL,
	JobTitle						varchar(30)	PRIMARY KEY		NOT NULL,
	JobDescription					varchar(500)				NOT NULL,
	Status							varchar(20)					NOT NULL
)

This is the code for the Employee Table


USE KudlerFineFoods

CREATE TABLE Employees
(
	EmployeeID				int		IDENTITY	PRIMARY KEY				NOT NULL,
	LastName				varchar(30)									NOT NULL,
	FirstName				varchar(30)									NOT NULL,
	Address					varchar(50)									NOT NULL,
	City					varchar(30)									NOT NULL,
	State					char(2)										NOT NULL,
	AreaCode				char(3)										NOT NULL,
	Phone					char(8)										NOT NULL,
	EE01Classification		varchar(50)									NOT NULL,
	HireDate				smalldatetime								NOT NULL,
	Salary					money										NOT NULL,
	Gender					char(1)										NOT NULL,
	Age						char(2)										NOT NULL,
	JobTitle				varchar(30) REFERENCES JobTitle(JobTitle)	NOT NULL
)

My question is 1) Have I done this correctly and 2) If I have, when I write the code to populate the rows in the employee table do I include a Job Title.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-06 : 15:38:28

Looks reasonable. One typical way to handle this is to have a JobTitleID column (probably smallint) in [JobTitle]. Then use the ID in Employees. Of course the ID would be the primary key and foreing key rather than the JobTitle value itself. One advantage of that is if you decide to tweak the JobTitle you can do it easily in one place rather than in all the [employee] rows that use title. Plus you typically don't ever want to update a primary key value. Finally, it will use less space that way.

And yes, you will need to include a valid title (or titleID) when you populate the employee table. Valid meaning it must exist in [jobTitle]

Be One with the Optimizer
TG

kevin_co
Starting Member

5 Posts

Posted - 2009-07-06 : 15:44:53

Thank you so much that helped a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources