is it possible to setup a foreign key while data is in the table
I have a table where data already resides. I am trying to setup a foreign key but got the following error message
'tblOrgRegistrations' table saved successfully
'tblOrgHours' table
- Unable to create relationship 'FK_tblOrgHours_tblOrgRegi
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblOrgHours_tblOrgRegi
Is it possible to setup a foreign key while data is already present for RegID?
'tblOrgRegistrations' table saved successfully
'tblOrgHours' table
- Unable to create relationship 'FK_tblOrgHours_tblOrgRegi
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblOrgHours_tblOrgRegi
Is it possible to setup a foreign key while data is already present for RegID?
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is there a way to still establish the foreign key even though EVERY record in the tblOrgRegistrations does not have a counterpart in the tblOrgHours? Some of them simply don't exist yet.
ASKER
If that does not work, can I temporarily create compliant data in the Hour table and then delete those records in the Hour table?
If the table has data, the data MUST conform to the constraint.
You could create "temporary data", but that kind of defeats the purpose of the constraint.
Better would be to create the data in the ancillary table before storing that primary data.
Better would be to create the data in the ancillary table before storing that primary data.
ASKER
The problem is that all the students in the registration table do not have hours in the hour table yet.
What do you suggest?
What do you suggest?
Will each entry in the hours table be unique so that the primary key of each row is one of the columns in the students table?
Actually, your business rules seem to mandate that the value CAN be null as the student record is created (probably prior to registration) and the hours are populated after the child's attendance is known.
A constraint here that the column must be populated requires that the student's attendance be known before he is registered and that obviously won't work.
A constraint here that the column must be populated requires that the student's attendance be known before he is registered and that obviously won't work.
ASKER
However if I want to delete the registration later...I don't want to be able to if they have hours
Ah. Then you have the constraint reversed. :)
You can't create attendance data unless there is a registration, and you can't delete a registration if there is recorded attendance.
You can't create attendance data unless there is a registration, and you can't delete a registration if there is recorded attendance.
ASKER
So am I to make a foreign key in the Registration table?
The relationship is one registered record in the registration table to many records in the hour table.
The relationship is one registered record in the registration table to many records in the hour table.
Assuming that the hour table has a design where each row contains the primary key of the student, then that would be the correct relationship. No time can be recorded unless a registration exists with the correct primary key and no Registration row can be deleted if the hours table contains a reference to it.
ASKER
So hence, my original setup was correct.
I was trying to setup a foreign key in the Hour table. But it would not let me....
I was trying to setup a foreign key in the Hour table. But it would not let me....
What statement are you trying to run and what error are you getting?
ASKER
I'm trying to alter the database hour table and add a foreign key. I am using the graphical interface and get the error as stated in my question:
'tblOrgRegistrations' table saved successfully
'tblOrgHours' table
- Unable to create relationship 'FK_tblOrgHours_tblOrgRegi
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblOrgHours_tblOrgRegi
Should I make the tblOrgHours table nullable?
'tblOrgRegistrations' table saved successfully
'tblOrgHours' table
- Unable to create relationship 'FK_tblOrgHours_tblOrgRegi
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblOrgHours_tblOrgRegi
Should I make the tblOrgHours table nullable?
Nullable won't necessarily help.
It looks like there are Hours data for a Registration that doesn't exists.
It looks like there are Hours data for a Registration that doesn't exists.
ASKER
How can I locate it.
ASKER
I think
SELECT r.*,h.*
FROM dbo.tblOrgHours h
left JOIN tblOrgRegistrations r ON r.RegID = h.RegID -- Change Foreign Key as appropriate
WHERE r.RegID IS NULL
actually works
SELECT r.*,h.*
FROM dbo.tblOrgHours h
left JOIN tblOrgRegistrations r ON r.RegID = h.RegID -- Change Foreign Key as appropriate
WHERE r.RegID IS NULL
actually works
Yep. That should show you the offending rows.
ASKER
Please check my follow up question I am posting right now
ASKER