Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

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?
0
al4629740
Asked:
al4629740
  • 11
  • 10
  • +1
3 Solutions
 
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
 
PortletPaulCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now