Creating a primary and foreign key

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

Creating a primary and foreign key

Author

Topic

RX840
Starting Member

9 Posts

Posted - 2008-12-08 : 01:14:04

I am very very new to sql and need some assistnce creating a primary and foreign key and connecting two basic tables. I am trying to link the two tables.

table 1 is called Employee
table 2 is called Job

Here's the code that I wrote for this, but I something in it is wrong. Can someone please show me what I need to do here?

I simply want to make the empid the primary key and have it work properly on both tables with the Employee table as the main table for empid. As you can see I have added (empsid & empsuperid) during my struggle to get this right - any help would be greatly appreciated.

CREATE TABLE Employee
(
empid INT NOT NULL,
PRIMARY KEY (EmpID),
UNIQUE (EmpID),
lname VARCHAR(15) NOT NULL,
fname VARCHAR(15) NOT NULL,
address VARCHAR(80) NOT NULL,
city VARCHAR(25) NOT NULL,
state VARCHAR(2) NOT NULL,
zip INT NOT NULL,
telnum INT NOT NULL,
hire SMALLDATETIME,
salary DECIMAL (10,2),
gender VARCHAR(1) NOT NULL,
age INT NOT NULL,
);

CREATE TABLE Job
(
jobtitle VARCHAR(30) NOT NULL,
jobdesc VARCHAR(80) NOT NULL,
exstatus VARCHAR(1) NOT NULL,
eeocl VARCHAR(15) NOT NULL,
jobco INT NOT NULL,
empsid INT NOT NULL,
empsuperid INT NOT NULL,
PRIMARY KEY(empsid),
FOREIGN KEY(empsuperid) REFERENCES Employee(empid)
);

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-08 : 01:36:29

Books Online is a great resource. This link has sections that cover both of your PK and FK questions.

http://msdn.microsoft.com/en-us/library/ms174979(SQL.90).aspx

Nathan Skerl

mt_dren
Starting Member

3 Posts

Posted - 2008-12-08 : 01:38:39

Hi, I’m new to the forum but this what I would try if it is a 1:M relationship (also didn't specify a primary key on the job table). If it was a M:N then I would add an intersection table. Someone else might have a better idea.

CREATE TABLE Employee
(
empid INT NOT NULL PRIMARY KEY clustered,
lname VARCHAR(15) NOT NULL,
fname VARCHAR(15) NOT NULL,
address VARCHAR(80) NOT NULL,
city VARCHAR(25) NOT NULL,
state VARCHAR(2) NOT NULL,
zip INT NOT NULL,
telnum INT NOT NULL,
hire SMALLDATETIME,
salary DECIMAL (10,2),
gender VARCHAR(1) NOT NULL,
age INT NOT NULL,
)

CREATE TABLE Job
(
jobtitle VARCHAR(30) NOT NULL,
jobdesc VARCHAR(80) NOT NULL,
exstatus VARCHAR(1) NOT NULL,
eeocl VARCHAR(15) NOT NULL,
jobco INT NOT NULL,
empsid INT NOT NULL foreign key (empsid) references employee(empid)
)

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-08 : 02:15:13

www.w3schools.com is the best place to start

Arnav
Even you learn 1%, Learn it with 100% confidence.

RX840
Starting Member

9 Posts

Posted - 2008-12-08 : 16:45:42

Mr Dren,

your suggestion worked! thank you so much for the help on this one!!!

Nathans and Sunsanvin - thank you as well, I am currently learning SQL and appreciate the extra links.

Patrick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources