Foriegn Key Restraint
Jan 25, 2000
Here is a table structure, with 2 contrainst applied to the same columns. Can this possible cause a problem.
CREATE TABLE dbo.app_person
(
appid int NOT NULL,
source char(1) NOT NULL,
first char(16) NULL,
mi char(1) NULL,
last char(24) NULL,
suffix char(4) NULL,
ssn char(9) NULL,
dob datetime NULL,
marital_status decimal(1,0) NULL,
housing char(1) NULL,
mortgage_holder char(20) NULL,
mortgage_payment decimal(9,2) NULL,
auto_holder char(20) NULL,
auto_account char(16) NULL,
auto_balance decimal(9,2) NULL,
auto_payment decimal(9,2) NULL,
de_datetime datetime NULL,
de_clerk char(10) NULL,
excl_other_income bit DEFAULT 0 NOT NULL,
pre_housing char(1) NULL,
same_area bit DEFAULT 0 NOT NULL,
income_type decimal(2,0) NULL,
other_income_type decimal(2,0) NULL,
relationship decimal(2,0) NULL,
line_of_work bit DEFAULT 0 NOT NULL,
selected_bureau_id decimal(1,0) NULL,
excl_income bit DEFAULT 0 NOT NULL,
CONSTRAINT PK_app_person PRIMARY KEY NONCLUSTERED (appid,source),
CONSTRAINT IX_lnapp100 UNIQUE NONCLUSTERED (appid,source),
CONSTRAINT FK_lnapp100_lnapp000 FOREIGN KEY (appid) REFERENCES dbo.app_main (appid)
)
View 1 Replies
Aug 26, 2007
I am just learning how to program in SQL, so please be patient with me
ok, here it is, i am getting the following errors with my script when i try to execute, and although i realize that this is not the best style to write my script in, i am trying to learn why this is erroring. the errors are:
Msg 1769, Level 16, State 1, Line 9
Foreign key 'Employee2Job_Title' references invalid column 'Title' in referencing table 'Employee'.
Msg 1750, Level 16, State 0, Line 9
Could not create constraint. See previous errors.
now i realize that the second error is because of the first, and that if i can fix the first, the second will go away. Thanks for the help.
Here is my script
use inventory
go
CREATE TABLE Job_Title
(Job_Title_Title char(25) NOT NULL ,
Job_Title_EEO1_Classification char(25) ,
Job_Title_Job_Description Varchar(45) ,
Job_Title_Exempt_Status Varchar(15)
, PRIMARY KEY (Job_Title_Title)
);
CREATE TABLE Employee
(Employee_Emp_ID integer NOT NULL ,
Employee_last_name varchar(15) ,
Employee_first_name varchar(15) ,
Employee_address varchar(30) ,
Employee_city varchar(15) ,
Employee_state char(2) ,
Employee_Telephone_area_code char(3) ,
Employee_Telephone_number char(8) ,
Employee_EEO1_Classification char(25) ,
Employee_Hire_Date char(8) ,
Employee_Salary char(6) ,
Employee_Gender Varchar(1) ,
Employee_Age char(2) ,
Employee_Title char(25)
, PRIMARY KEY (Employee_Emp_ID)
, constraint Employee2Job_Title FOREIGN KEY (Title
) REFERENCES Job_Title
);
Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values
(1, 'Edelman', 'Glenn', '175 Bishop Lane', 'La Jolla', 'CA', 619, '555-0199', 'Sales Workers', 10/7/2003, 21500.00, 'M', 64, 'Cashier')
Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values
(2, 'McMullen', 'Eric', '762 Church Street', 'Lemon Grove', 'CA', 619, '555-0133', 'Sales Workers', 11/1/2002, 13500.00, 'M', 20, 'Bagger')
Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values
(3, 'Slenj', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', 619, '555-0123', 'Officials & Managers', 6/1/2000, 48000.00, 'M', 34, 'Assistant Manager')
Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values
(4, 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA', 760, '555-0100', 'Sales Workers', 3/12/2003, 10530.00, 'F', 24, 'Bagger - 30 hours/wk')
Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values
(5, 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', 619, '555-0154', 'Office/Clerical', 11/1/2003, 15000.00, 'M', 18, 'Stocker')
Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values
(6, 'Esquivez', 'David', '10983 North Coast Highway Apt 902', 'Encinitas', 'CA', 760, '555-0108', 'Operatives (Semi skilled)', 7/25/2003, 18500.00, 'M',25, 'Asst. - Butchers & Seafood Specialists')
Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values
(7, 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', 858, '555-0135', 'Sales Workers', 7/12/2003, 21000.00, 'F', 24, 'Cashier')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Accounting Clerk', 'Office/Clerical', 'Computes, Classifies, records, and verifies numerical data for use in maintaining accounting records.', 'Non-Exempt')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Assistant store manager', 'Officials & Mangers', 'Supervises and coordintes activities of workers in department of food store. Assist store manager in daily operations of store.', 'Exempt')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Bagger', 'Sales Worker', 'Places customer orders in bags. Performs carry out duties for customers.', 'Non-Exempt')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Cashier', 'Sales Worker', 'Operates Cash register to itemize and total customers purchases in grocercy store', 'Non-Exempt')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Computer Support Specialist', 'Technician', 'Installs, Modifies, and makes minor repairs to personal computer hardware and software systems and provides technical assistance and training to system users.', 'Non-Exempt')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Director of Finance & Accounting', 'Officials & Mangers', 'Plans and directs finance and accounting activites for Kudlser Fine Foods.', 'Non-Exempt')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Retail Asst. Bakery & Pastry', 'Craft Workers (Skilled)', 'Obtains or prepares Bakery and Pastry items requested by customers in retail food store.', 'Non-Exempt')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Retail Asst. Butchers and Seafood Specialists', 'Operatives (Semi skilled)', 'Obtains or prepares Meat and Seafood items requested by customers in retail food store.', 'Non-Exempt')
Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values
('Stocker', 'Office/Clerical', 'Stores, prices, and restocks merchandise displays in store.', 'Non-Exempt')
View 3 Replies
View Related
