Roxanne25
asked on
Foreign Keys ~ 2 Fields to the same Reference field
Hi, I am trying to set up foreign keys on my tables and I ran into a little problem.
I have one table that has an added_by field and then a changed_by field. Both of these fields are foreign keys to a single field in another table.
So, I tried this:
ALTER TABLE employee_org
add CONSTRAINT FK_CHANGED_FIELDS
FOREIGN KEY (last_changed_by, added_by)
REFERENCES system_user (user_id);
And it said no way jose!
SQL Error: ORA-00957: duplicate column name
00957. 00000 - "duplicate column name"
And I also tried adding this:
ALTER TABLE employee_org
add CONSTRAINT FK_CHANGED_BY
FOREIGN KEY (last_changed_by)
REFERENCES system_user (user_id);
and then doing the second one:
ALTER TABLE employee_org
add CONSTRAINT FK_ADDED_BY
FOREIGN KEY (added_by)
REFERENCES system_user (user_id);
And it said no way jose again!
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
So, how do I accomplish my task?
I have one table that has an added_by field and then a changed_by field. Both of these fields are foreign keys to a single field in another table.
So, I tried this:
ALTER TABLE employee_org
add CONSTRAINT FK_CHANGED_FIELDS
FOREIGN KEY (last_changed_by, added_by)
REFERENCES system_user (user_id);
And it said no way jose!
SQL Error: ORA-00957: duplicate column name
00957. 00000 - "duplicate column name"
And I also tried adding this:
ALTER TABLE employee_org
add CONSTRAINT FK_CHANGED_BY
FOREIGN KEY (last_changed_by)
REFERENCES system_user (user_id);
and then doing the second one:
ALTER TABLE employee_org
add CONSTRAINT FK_ADDED_BY
FOREIGN KEY (added_by)
REFERENCES system_user (user_id);
And it said no way jose again!
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
So, how do I accomplish my task?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Does it make sense to add a foreign key on the
It depends on your requirements. For example: What do you do if user 'bob' changes a record then quits?
You can't delete him from the system_user table until the foreign key has been resolved.
With no foreign key, the last_changed field will always show 'bob' until the next person changes it.
You might look into a change history table to track changes over time, not just the 'last' change.
It depends on your requirements. For example: What do you do if user 'bob' changes a record then quits?
You can't delete him from the system_user table until the foreign key has been resolved.
With no foreign key, the last_changed field will always show 'bob' until the next person changes it.
You might look into a change history table to track changes over time, not just the 'last' change.
The issue is exactly as the database reported: "parent keys not found". You have records in your child table (employee_org) that are not represented by primary key values in the parent table (system_user).
It is completely acceptable to have a FK that is not populated, if business processes require it, as it would appear they do in this case - some records may never be changed.
What do you plan to do if you receive multiple changes? You can just keep the most recent, or the first, or you could introduce a trigger to automatically copy any existing change data to a change_history table before overwriting the change info in the employee_org table with the newest change info.
It is completely acceptable to have a FK that is not populated, if business processes require it, as it would appear they do in this case - some records may never be changed.
What do you plan to do if you receive multiple changes? You can just keep the most recent, or the first, or you could introduce a trigger to automatically copy any existing change data to a change_history table before overwriting the change info in the employee_org table with the newest change info.
@K_from_O,
I see you are new to Experts-Exchange and I welcome you and appreciate your willingness to help. I do ask that in the future you read the previous posts before responding. I really don't see any additional information in your post that hasn't already been covered.
I see you are new to Experts-Exchange and I welcome you and appreciate your willingness to help. I do ask that in the future you read the previous posts before responding. I really don't see any additional information in your post that hasn't already been covered.
slight:
Yea, some of it was re-stating, I agree. I did suggest using a trigger to move the history data. Also, if the answer isn't accepted, does attempting to clarify hurt anything?
Yea, some of it was re-stating, I agree. I did suggest using a trigger to move the history data. Also, if the answer isn't accepted, does attempting to clarify hurt anything?
>> does attempting to clarify hurt anything?
No but restating what other Experts have already said can be seen as an attempt to poach points from a question.
Since I saw you were new here I just wanted to start you off on the right track.
Agreeing with a previous post and adding additional information is acceptable and actually welcomed.
For example, in this case, something along the lines of:
I agree with everything said. In case you wanted to track multiple changes I suggest creating a trigger.
or something similar.
No but restating what other Experts have already said can be seen as an attempt to poach points from a question.
Since I saw you were new here I just wanted to start you off on the right track.
Agreeing with a previous post and adding additional information is acceptable and actually welcomed.
For example, in this case, something along the lines of:
I agree with everything said. In case you wanted to track multiple changes I suggest creating a trigger.
or something similar.
ASKER
Does it make sense to add a foreign key on the last_changed_by field because it will only be populated if someone made a change to the record?