| Author |
Topic |
|
cmslosson
Starting Member
1 Post |
 Posted - 2009-11-16 : 18:06:20
|
| Hi everyone! New poster here and new to SQL Server 2008. I am working on a homework assignment and I received an error that I cannot figure out how to fix. I asked my instructor and he gave me a cryptic answer (which I posted below). My assignment is to create a database with two tables and enter in the data I am given. Each table needs a Primary Key (PK) and one table needs a Foreign Key (FK). I created the Database and tables no problem and even managed the FK. But when I try to imput data into my Employee table, it gives me this error (mind you, the data went into the Job_title table just fine):Msg 547, Level 16, State 0, Line 1The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Employee__JobTit__0519C6AF". The conflict occurred in database "KudlerFineFoods", table "dbo.Job_title", column 'JobTitle_ID'.The statement has been terminated.Here is the code I used to create the tables:I created the Job_Title Table first, then the employee table.Create Table Job_title(JobTitle_ID varchar(50) NOT NULL Primary Key,EEO1Classification varchar(50) NOT NULL,JobDescription varchar(max) NOT NULL,ExemptNonexempt varchar(50) NOT NULL)CREATE TABLE Employee(EmployeeID int NOT NULL Primary Key,LastName varchar(50) NOT NULL,FirstName varchar(50) NOT NULL,Address varchar(50) NOT NULL,City varchar(50) NOT NULL,State char(2) NOT NULL,TelephoneAreaCode char(3) NOT NULL,TelephoneNumber char(8) NOT NULL,HireDate smalldatetime NOT NULL,Salary money NOT NULL,Gender varchar(50) NOT NULL,Age char(3) NOT NULL,JobTitle_ID varchar(50) NOT NULL FOREIGN KEY REFERENCES Job_title(JobTitle_ID) Here is my code I used to enter the data:Again, I put the data in the Job_title table first and it worked fine. It gives the error message when I try to enter my data into the employee table.USE KudlerFineFoodsGOINSERT INTO Job_title ([JobTitle_ID], [EEO1Classification], [JobDescription], [ExemptNonexempt])VALUES ('Accounting Clerk', 'Office/Clerical', 'Computes, classifies, records, and verifies numeric...', 'NON'),('Assistant Manager', 'Officials & Managers', 'Supervises and coordinates activities of workers...', 'Exempt'),('Bagger', 'Sales Workers', 'Places customer orders in bags. Performs carryout...', 'NON'),('Cashier', 'Sales Workers', 'Operates cash register to itemize and total customer...', 'NON'),('Computer Support Specialists', 'Technician', 'Installs, modifies, and makes minor repairs to...', 'NON'),('Director of Finance and Accounting', 'Officials & Managers', 'Plans and directs the finance and accounting...', 'Exempt'),('Retail Assistant Bakery and Pastry', 'Craft Workers (skilled)', 'Obtains or prepares food items requested by...', 'NON'),('Retail Assistant Butchers and Seafood Specialists', 'Operatives (Semi Skilled)', 'Obtains or prepares food items requested by...', 'NON'),('Stocker', 'Office/Clerical', 'Stores, prices, and restocks merchandise displays...', 'NON')USE KudlerFineFoodsGOINSERT INTO Employee ([EmployeeID], [LastName], [FirstName], [Address], [city], [State], [TelephoneAreaCode], [TelephoneNumber], [HireDate], [Salary], [Gender], [Age], [Jobtitle_ID])VALUES (1,'Edelman', 'Glenn', '175 Bishops Lane', 'La Jolla', 'CA', '619', '555-0199', '10/07/2003', '21500.00', 'Male', '64', 'Cashier'),(2, 'McMullen', 'Eric', '763 Church St', 'Lemon Grove', 'CA', '619', '555-0133', '11/1/2002', '13500.00', 'Male', '20', 'Bagger'),(3, 'Slentz', 'Raj', '123 Torrey Dr', 'North Clairmont', 'CA', '619', '555-0123', '06/01/2000', '48000.00', 'Male', '34', 'Assistant Manager'),(4, 'Broun', 'Erin', '2045 Parkway Apt 29', 'Encinitas', 'CA', '760', '555-0100', '3/12/2003', '10530.00', 'Female', '24', 'Bagger'),(5, 'Carpenter', 'Donald', '927 Second St', 'Encintas', 'CA', '619', '555-0154', '11/1/2003', '15000.00', 'Male', '18', 'Stocker'),(6, 'Esquivez', 'David', '10983 N. Coast HWY Apt 902', 'Encinitas', 'CA', '760', '555-0108', '7/25/2003', '18500.00', 'Male', '25', 'Asst – Butchers & Seafood Specialists'),(7, 'Sharp', 'Nancy', '10793 Montecino Rd', 'Ramona', 'CA', '585', '555-0135', '7/12/2003', '21000.00', 'Female', '24', 'Cashier')What am I doing wrong? How can I fix the error? I didnt have this trouble until I added the FK. I tried to remove the constraint on the FK, but it didnt work. I got a message saying there was no constraint so the action was terminated. All this is fake information for a fake company. This is the code I used to try and get rid of the FK constraint:ALTER TABLE employee NOCHECK CONSTRAINT jobtitle_IDIs there a way to fix this? Thanks so much! My assignment is due tonight, but the instructor gave me an extension since I am trying to work with him to fix it. Though I still need an answer as soon as you all can give it. Thanks!--CM Slosson |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-11-16 : 18:55:33
|
quote:
Originally posted by cmslosson
<snip>(6, 'Esquivez', 'David', '10983 N. Coast HWY Apt 902', 'Encinitas', 'CA', '760', '555-0108', '7/25/2003', '18500.00', 'Male', '25', 'Asst – Butchers & Seafood Specialists'),(7, 'Sharp', 'Nancy', '10793 Montecino Rd', 'Ramona', 'CA', '585', '555-0135', '7/12/2003', '21000.00', 'Female', '24', 'Cashier')
I just did a quick glance, but I don't see the highlighted JobID in your job table. |
 |
|
|
prakum
Starting Member
16 Posts |
Posted - 2009-11-17 : 23:25:43
|
| "Asst – Butchers & Seafood Specialists" This entry is not in you job title tablePraveen Kumar |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Homework Help Please