October 6, 2010 at 7:01 am
Attached is an example of the query.
After writing this code and running the query , the Job Title ID field shows NULL? Can you please give me some suggestions.
October 6, 2010 at 7:09 am
Employee73.Job_Title_ID FK references Job_Titles_8 PK
Script shows creation of Job_Titles_10 but doesn't show creation of Job_Titles_8
Script shows neither Job_Titles_8 nor Job_Titles_10 population.
Can't troubleshoot without proper information 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 6, 2010 at 7:16 am
Executable code rather than screenshots would be much more useful too.
October 6, 2010 at 11:48 am
Thanks for the reply. This one should be better.
USE Database1;
CREATE TABLE Job_Titles_4
(Job_Title_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
EE0_1_Classification VARCHAR(25),
Job_Title VARCHAR(27),
Job_Description VARCHAR(30),
Exempt_NonExempt_Status VARCHAR(1));
USE Database1
INSERT INTO Job_Titles_4
(EE0_1_Classification,Job_Title,Job_Description,Exempt_NonExempt_Status)
VALUES('Officials & Managers','Director Finance& Accounting',' ','Y');
USE Database1;
CREATE TABLE Employee50
(Employee_ID INT IDENTITY(1,1)PRIMARY KEY,
Job_Title_ID INT FOREIGN KEY REFERENCES Job_Titles_4(Job_Title_ID),
Last_Name VARCHAR(17),
First_Name VARCHAR(15),
Address VARCHAR(25),
City VARCHAR(15),
State VARCHAR(2),
Telephone_area_code VARCHAR(3),
Telephone_number VARCHAR(15),
Hire_Date DATETIME,
Salary DECIMAL(18,2),
Gender VARCHAR(1),
Race VARCHAR(2),
Age VARCHAR(2));
INSERT INTO Employee50
(Last_Name,First_Name,Address,City,State,Telephone_area_code,Telephone_number,Hire_Date,Salary,Gender,Race,Age)
VALUES('Carpenter','Donald','927 Second Street','Encitas','CA','619','619-555-0154','11/01/2003','15000','M','AA','18'),
('Slentz','Raj','123 Torey Dr.','North Clairmont','CA','619','619-555-0128','06/01/2000','48000','M','AS','34');
SELECT *
FROM Employee50,Job_Titles_4;
October 6, 2010 at 12:36 pm
As far as I can see script is inserting nothing in employee50.Job_Title_ID therefore is correct to show there... nothing.
Also, I would define employee50.Job_Title_ID as Not Null.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 6, 2010 at 12:50 pm
Thanks for the reply, I was not aware that I was supposed to insert anything into the field since it is a reference to a Primary key field that adds integer values automatically.
When I add "NOT NULL" to the foreign key statement I recieve an error message "Cannot insert value into column"
October 6, 2010 at 12:57 pm
Wayne, can I borrow your avatar?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 6, 2010 at 12:58 pm
kennywashington2000 (10/6/2010)
Thanks for the reply, I was not aware that I was supposed to insert anything into the field since it is a reference to a Primary key field that adds integer values automatically.When I add "NOT NULL" to the foreign key statement I recieve an error message "Cannot insert value into column"
You are welcome.
The FK reference to PK means that you are not allowed to insert a row in the Employee50 child table that has not a row in parent table Job_Titles_4. Only execption is to insert a Null value, which is what the script was doing.
Not Null constraint in FK protects you from inserting Null values in such a column therefore you are only allowed to insert a value that already exists in parent table Job_Titles_4
Hope this clarifies.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 7, 2010 at 1:44 am
I am rephrasing Paul's answer in a way that makes sense to me - ignore if you've already got it.
A foreign key is a constraint - it restricts the values that can be entered into the column(s) it acts upon. The values are restricted to the primary key values in the referenced column(s). It does not cause any values to be automatically entered for you - you still have to explicitly enter them yourself. The fact the referenced column is an identity is also totally irrelevant to the foreign key.
This is the best quick intro to database design I know of:
October 7, 2010 at 5:52 am
hallidayd (10/7/2010)
I am rephrasing Paul's answer in a way that makes sense to me - ignore if you've already got it.A foreign key is a constraint - it restricts the values that can be entered into the column(s) it acts upon. The values are restricted to the primary key values in the referenced column(s). It does not cause any values to be automatically entered for you - you still have to explicitly enter them yourself. The fact the referenced column is an identity is also totally irrelevant to the foreign key.
Good job! 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 7, 2010 at 6:12 am
Thank both of you for your help, this cleared a lot of confusion for me.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply