figuring out PK/FK

[MagicM]

:cry:

I'm new to this MS SQL and I've been trying to understand this Primary key and Foreign key. I know that I use the foreign key to reference a table but for some reason I'm not getting this down right can someone point me to the right direction.

Also I notice that if I have the job_title table that contain the job_title base on the code I've assign why do I have to have all the job_title_code every one different. That a total of fifthteen.

Here's the codes:

USE POS410
go

CREATE TABLE employee
(
social_security_number VARCHAR(11) NOT NULL, /*String Character up to 11 for SSN*/
last_name VARCHAR(35) NOT NULL, /*String Character up to 35 characters for last name*/
first_name VARCHAR(35) NOT NULL, /*String Character up to 35 chars for first name*/
address VARCHAR(40) NOT NULL, /*String Character up to 40 chars for street address*/
city VARCHAR(15) NOT NULL, /*String Character up to 15 chars for City*/
state CHAR(2) NOT NULL, /*String Character up to 2 (fixed) for State*/
zip_code INT NOT NULL, /*Integer Zip code*/
telephone_area_code INT NOT NULL, /*Integer Area code*/
telephone_number INT NOT NULL, /*Integer phone number*/
email_address VARCHAR(75) NOT NULL, /*String Character up to 75 chars for email address*/
job_title_code VARCHAR(11) NOT NULL, /*Fixed string charcter up 7 chars for job title code*/
hire_date SMALLDATETIME NOT NULL, /*Date type : '01/01/1900'*/
salary DECIMAL (10,2) NOT NULL,
PRIMARY KEY(social_security_number), /*Primary key: Job_Title_Code*/
FOREIGN KEY(job_title_code) REFERENCES employee(social_security_number)
)
GO

CREATE TABLE job_title
(
job_title_code VARCHAR(11) NOT NULL,
job_title VARCHAR(45) NOT NULL,
exemp_non_exempt_status VARCHAR(10),
miniumum_salary DECIMAL(10,2),
maximum_salary DECIMAL(10,2),
PRIMARY KEY(job_title_code),
/*FOREIGN KEY(job_title_code) REFERENCES employee(social_security_number)*/
)
GO

/* Inserting TEN Records information into Employee table */

INSERT INTO employee VALUES ('529-38-7034', 'Flocker', 'Gaylord', '395 Gay Lane', 'Pueblo', 'CO', '81004', '970', '1238823', '[e-mail]', 'EGG7614', '8/15/2003' , '35000')
INSERT INTO employee VALUES ('518-11-2254', 'Smith', 'Joseph', '1 Vision Lane', 'Chappaqua', 'NY', '10514', '607', '5582531', '[e-mail]', 'EGG1990', '12/10/1995' , '41000')
INSERT INTO employee VALUES ('325-04-1752', 'Boatman', 'Moses', '5529 Industrial Park Circle', 'Robbistan', 'ME', '04671', '207', '3549731', '[e-mail]', 'EGG9972', '03/28/1970' , '66000')
INSERT INTO employee VALUES ('429-72-9565', 'Zacharvas', 'Jose', '398 Orchard Lane', 'Carefree', 'AZ', '85377', '602', '6532597', '[e-mail]', 'EGG1467', '01/15/1989' , '25000');
INSERT INTO employee VALUES ('125-40-1750', 'White', 'MaryAnn', '350 Mustang Drive', 'Monticello', 'UT', '84535', '435', '3288321', '[e-mail]', 'EGG3744', '01/25/1975' , '45000');
INSERT INTO employee VALUES ('444-57-825', 'Gonzales', 'Susan', '4391 Oquirrh Way', 'Cottonwood', 'UT', '84018', '435', '3288321', '[e-mail]', 'EGG9729', '01/18/2002' , '67000');
INSERT INTO employee VALUES ('586-65-1970', 'Lords', 'Mi', '3 O-Clock Park', 'Santa Clara', 'UT', '84765', '435', '8837597', '[e-mail]', 'EGG9792', '04/10/2001' , '65000');
INSERT INTO employee VALUES ('461-41-5007', 'Fillmore', 'Jack', '5 Race Lane', 'Ballard', 'UT', '84066', '435', '3542595', '[e-mail]', 'EGG0501', '10/24/2003' , '58000');
INSERT INTO employee VALUES ('080-23-0917', 'Prettyman', 'Lisa', '45 Uintah Avenue', 'Dugway', 'UT', '84022', '435', '5231564', '[e-mail]', 'EGG0510', '06/05/2004' , '56000');
INSERT INTO employee VALUES ('295-55-4874', 'Jensen', 'Mike', '5 Tiebreaker Circle', 'Wendover', 'NV', '89835', '775', '4785116', '[e-mail]', 'EGG9910', '11/13/2003' , '38000');
GO
/* Inserting FIVE records into Job_Title table */

INSERT INTO job_title VALUES('EGG4167', 'General Clerk III', 'Exempt', '35000', '45000');
INSERT INTO job_title VALUES('EGG1050', 'General Clerk I', 'Exempt', '10000', '35000');
INSERT INTO job_title VALUES('EGG0991', 'Secretary III', 'Exempt', '37000', '47000');
INSERT INTO job_title VALUES('EGG2799', 'Database Administrator', 'Non-Exempt', '65000', '75000');
INSERT INTO job_title VALUES('EGG8650', 'Cost Control Analyst Manager', 'Non-Exempt', '40000', '74000');
GO

[nicentral]

What exactly are you trying to figure out? Are you getting a parse error?

[MagicM]

I got it working now after trying moving things around.

When I use statement like this I would get 15 result but they are repeat of the same employee name for all five job_title.

I thought that the foreign key was use to point to the primary key more like relationship use in Access where one table contain the job title assign to each employee.
I notice that this is not the case. both tables have different job_title_code assigned.
Why do I have to do that?

SELECT first_name, last_name, job_title
FROM employee, job_title
WHERE employee.job_title_code = job_title.job_title_code
OR salary between '15000' and '25000'

if I change or to AND I get a blank result.
I've change the reference key statement to this and it execute fine under the job_title table:

PRIMARY KEY(job_title_code),
FOREIGN KEY(job_title_code) REFERENCES job_title(job_title_code)

See if this does the same thing, what do I need to change in my code?

[pclfw]

Well you are going to have fun with this one.
1) Only use NOT NULL when there MUST be an entry in a field. People are not yet forced to have email addresses, even in the USA.

2) You don't need a foreign key reference on the job title table. The employee table references the job title not the reverse.

3) The INSERT lines for the employee table have the INT values quoted. That makes the values CHARs and the insert fails.

4) Its unwise to have telephone numbers as INT data type. Some idiot with put a negative number in there. CHAR or VARCHAR is fine for them.

5) For the SSN see point 4.

6) Salary column use the MONEY data type its more accurate when doing calculations.

[Kate Albany]

It appears you are trying to setup a one to many relationship between job_title -> employee. This would imply that one employee can have one job_title and one job_title can be assigned to many employees.

To do this you should set the tables PK/FK relations like this:

job_title table

PRIMARY KEY(job_title_code),

employee table

PRIMARY KEY(social_security_number),
FOREIGN KEY(job_title_code) REFERENCES job_title(job_title_code)

I easiest way to do this would be to drop both tables and then use the constraints above in your create table statements. You will need to create the job_title table before you create the employee table. The same apples to the insert statements, you will need to populate the job_title table before you can populate the employee table.

--------------

The values for 'job_title_code' in your insert into employee statements do not match any of the primary key values in the job_title table. I suspect that when using the constraints above you will receive a referential integrity constraint error when you try to insert into the employee table. You need to specify one of the PK values (job_title_code) you have inserted into the job_title table when inserting into the employee table.

--------------

SELECT first_name, last_name, job_title
FROM employee, job_title
WHERE employee.job_title_code = job_title.job_title_code
OR salary between '15000' and '25000'

You shouldn't use the 'OR' in this manner as it will give you a 'Cartesian join'/'Cartesian product'. You may want to look that up, it will help you understand relationships.

Now with the new constraints on the tables, if you want to see all the employee's with a salary between '15000' and '25000' the query should look like this.

SELECT first_name, last_name, job_title
FROM employee, job_title
WHERE employee.job_title_code = job_title.job_title_code
AND salary between '15000' and '25000'