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_tblOrgRegistrations'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblOrgHours_tblOrgRegistrations". The conflict occurred in database "CAPRegistration", table "dbo.tblOrgRegistrations", column 'RegID'.

Is it possible to setup a foreign key while data is already present for RegID?
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Yes.  But any data already in the table must conform to the constraint that you're creating.
0
al4629740Author Commented:
Is there a way to resolve any conflicts/issues if not?
0
PortletPaulfreelancerCommented:
locate the non-compliant data, fix the non-complaint data, re-apply the constraint

the whole concept of a constraint is to compel the data to meet the condition, so there is no alternative
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
Is there a way to resolve any conflicts/issues if not?
You should be able to identify them using something like this:
SELECT r.*
FROM dbo.tblOrgRegistrations r
             LEFT JOIN tblOrgHours h ON r.RegID = h.RegID  -- Change Foreign Key as appropriate
WHERE h.<Primary Key for tblOrgHours Goes Here> IS NULL

Open in new window


But as previously indicated it is up to you to resolve.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
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.
0
al4629740Author Commented:
If that does not work, can I temporarily create compliant data in the Hour table and then delete those records in the Hour table?
0
Kent OlsenData Warehouse Architect / DBACommented:
If the table has data, the data MUST conform to the constraint.
0
Kent OlsenData Warehouse Architect / DBACommented:
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.
0
al4629740Author Commented:
The problem is that all the students in the registration table do not have hours in the hour table yet.

What do you suggest?
0
Kent OlsenData Warehouse Architect / DBACommented:
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?
0
Kent OlsenData Warehouse Architect / DBACommented:
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.
0
al4629740Author Commented:
However if I want to delete the registration later...I don't want to be able to if they have hours
0
Kent OlsenData Warehouse Architect / DBACommented:
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.
0
al4629740Author Commented:
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.
0
Kent OlsenData Warehouse Architect / DBACommented:
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.
0
al4629740Author Commented:
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....
0
Kent OlsenData Warehouse Architect / DBACommented:
What statement are you trying to run and what error are you getting?
0
al4629740Author Commented:
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_tblOrgRegistrations'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblOrgHours_tblOrgRegistrations". The conflict occurred in database "CAPRegistration", table "dbo.tblOrgRegistrations", column 'RegID'.

Should I make the tblOrgHours table nullable?
0
Kent OlsenData Warehouse Architect / DBACommented:
Nullable won't necessarily help.

It looks like there are Hours data for a Registration that doesn't exists.
0
al4629740Author Commented:
How can I locate it.
0
al4629740Author Commented:
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
0
Kent OlsenData Warehouse Architect / DBACommented:
Yep.  That should show you the offending rows.
0
al4629740Author Commented:
Please check my follow up question I am posting right now
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.