d2jsp
Log InRegister
d2jsp Forums > Off-Topic > Computers & IT > Programming & Development > Quick Sql Help Please
Add Reply New Topic New Poll
Views: 589Replies: 9Track Topic
Member
Posts: 29,345
Joined: Mar 27 2008
Gold: 504.69
#1
Mar 26 2016 08:06pm


Only stuck on trying to create the job_history table.

How do I ensure referential integrity? Confused on how to create the composite key that also is a foreign key to employees?

Any help appreciated.

Code
CREATE TABLE job_history
(employee_id NUMBER(3),
start_date DATE,
end_date DATE,
job_id NUMBER(3),
department_id NUMBER(3),
PRIMARY KEY (employee_id, start_date),
FOREIGN KEY (job_id) REFERENCES jobs(job_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id));


/edit

If it helps I can post my entire script so far.

This post was edited by ROM on Mar 26 2016 08:08pm
Member
Posts: 32,925
Joined: Jul 23 2006
Gold: 3,804.50
#2
Mar 26 2016 08:58pm
Quote
Only stuck on trying to create the job_history table.

How do I ensure referential integrity? Confused on how to create the composite key that also is a foreign key to employees?


not sure i understand. only employee_id is a foreign key i assume, not start date. can you not simply do this?

Code
FOREIGN KEY (employee_id) REFERENCES employees(employee_id));
Member
Posts: 29,345
Joined: Mar 27 2008
Gold: 504.69
#3
Mar 26 2016 11:21pm
Quote (carteblanche @ Mar 26 2016 10:58pm)
not sure i understand. only employee_id is a foreign key i assume, not start date. can you not simply do this?

Code
FOREIGN KEY (employee_id) REFERENCES employees(employee_id));


I was thinking start date was the same as hire date, not really sure though.

Are you able to define both employee ID and start date as a composite key and still make employee ID a foreign key as well?
Member
Posts: 32,925
Joined: Jul 23 2006
Gold: 3,804.50
#4
Mar 26 2016 11:26pm
Quote (ROM @ Mar 27 2016 01:21am)
I was thinking start date was the same as hire date, not really sure though.


Well, if it is, just put them both as the foreign key.

Quote
Are you able to define both employee ID and start date as a composite key and still make employee ID a foreign key as well?


why not? are you getting an error?
Member
Posts: 29,345
Joined: Mar 27 2008
Gold: 504.69
#5
Mar 26 2016 11:29pm
Quote (carteblanche @ Mar 27 2016 01:26am)
Well, if it is, just put them both as the foreign key.



why not? are you getting an error?


It's saying table doesn't exist. Triple checked spelling but no dice. I thought since they were both part of a composite key I would put them together but that gave me the same error.

This post was edited by ROM on Mar 26 2016 11:29pm
Member
Posts: 32,925
Joined: Jul 23 2006
Gold: 3,804.50
#6
Mar 26 2016 11:48pm
Quote (ROM @ Mar 27 2016 01:29am)
It's saying table doesn't exist. Triple checked spelling but no dice. I thought since they were both part of a composite key I would put them together but that gave me the same error.


post your CREATE TABLE job_history script. the one you already posted doesn't include it.
Member
Posts: 29,345
Joined: Mar 27 2008
Gold: 504.69
#7
Mar 27 2016 12:19am
Quote (carteblanche @ Mar 27 2016 01:48am)
post your CREATE TABLE job_history script. the one you already posted doesn't include it.


Code
CREATE TABLE job_history
(employee_id NUMBER(3),
start_date DATE,
end_date DATE,
job_id NUMBER(3),
department_id NUMBER(3),
PRIMARY KEY (employee_id, start_date),
FOREIGN KEY (employee_id, start_date) REFERENCES employees(employee_id,hire_date),
--FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
--FOREIGN KEY (start_date) REFERENCES employees(hire_date),
FOREIGN KEY (job_id) REFERENCES jobs(job_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id));


edit

SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
gives a column-list for which there is no matching unique or primary
key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS
catalog view

//edit

Narrowed it down to this. Is this not legal?

Code
FOREIGN KEY (start_date) REFERENCES employees(hire_date),


This post was edited by ROM on Mar 27 2016 12:24am
Member
Posts: 32,925
Joined: Jul 23 2006
Gold: 3,804.50
#8
Mar 27 2016 12:32am
Quote (ROM @ Mar 27 2016 02:19am)
Code
CREATE TABLE job_history
(employee_id NUMBER(3),
start_date DATE,
end_date DATE,
job_id NUMBER(3),
department_id NUMBER(3),
PRIMARY KEY (employee_id, start_date),
FOREIGN KEY (employee_id, start_date) REFERENCES employees(employee_id,hire_date),
--FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
--FOREIGN KEY (start_date) REFERENCES employees(hire_date),
FOREIGN KEY (job_id) REFERENCES jobs(job_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id));


edit

SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
gives a column-list for which there is no matching unique or primary
key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS
catalog view


does hire_date have a unique constraint?
Member
Posts: 32,925
Joined: Jul 23 2006
Gold: 3,804.50
#9
Mar 27 2016 01:39am
Quote (ROM @ Mar 27 2016 02:19am)
Code
CREATE TABLE job_history
(employee_id NUMBER(3),
start_date DATE,
end_date DATE,
job_id NUMBER(3),
department_id NUMBER(3),
PRIMARY KEY (employee_id, start_date),
FOREIGN KEY (employee_id, start_date) REFERENCES employees(employee_id,hire_date),
--FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
--FOREIGN KEY (start_date) REFERENCES employees(hire_date),
FOREIGN KEY (job_id) REFERENCES jobs(job_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id));


edit

SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
gives a column-list for which there is no matching unique or primary
key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS
catalog view

//edit

Narrowed it down to this. Is this not legal?

Code
FOREIGN KEY (start_date) REFERENCES employees(hire_date),


Looking at it, looks like hire date wouldn't be unique. So if employee ID is always unique in the employees table, why would you reference hire date as a foreign key?
Member
Posts: 29,345
Joined: Mar 27 2008
Gold: 504.69
#10
Mar 27 2016 06:34am
Quote (carteblanche @ Mar 27 2016 03:39am)
Looking at it, looks like hire date wouldn't be unique. So if employee ID is always unique in the employees table, why would you reference hire date as a foreign key?


You are right. That makes sense. I think I will leave it as is then without trying to make start date a foreign key.

Thanks again carteblanche.
Go Back To Programming & Development Topic List
Add Reply New Topic New Poll

© 2003-2026 d2jsp  Contact