SQL Server Referential Integrity - One-To-Many relationship

I get the following error when I try to create and save a new relationship between Companies and Trades tables (Companies.ID links to Trades.CompanyID)

'Companies' table saved successfully
'Trades' table
- Unable to create relationship 'FK_Trades_Companies'.  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Trades_Companies'. The conflict occurred in database 'XXXXXXX', table 'Companies', column 'ID'.

There is a one to many relationship between Companies and Trades. All trades have a company, but some companies have no trades.

All references in Trades.CompanyID exist in Companies.ID.

Is the problem because some companies have no related trades?

In Access, there is an option to specify the join type when creating referential integrity links. Is there something similar with SQL Server?


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.

sounds like you ALREADY have a Constraint named 'FK_Trades_Companies'.  

tomfolinsbeeAuthor Commented:
Using Query Analyzer, i entered sp_help Trades and then sp_help Companies.
I do not see any contraints that started with FK.

To clarify, the error message occurs when I'm in Diagram view in Enterprise Manager. I linked the two tables, then tried to save.
In diagram view right click the table , select properties then relationships tab and remove the relationship.. it may not always show up in the diagram picture but it will still be in there.  I had to do this myself a few days ago.
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.

tomfolinsbeeAuthor Commented:
Razix, I removed the existing relationship in the diagram, and resaved the tables. Then I opened the diagram and added the relationship again. When I try to save, I get the same error. When I close the diagram and open it again, the link is still there. I checked sp_help Trades and there is no FK constraint.

If I I understand correctly, the diagram doesn't necesarily reflect the relationships that are enforced in the database.

Is it possible that the reason i can't enforce the constraint is because some Company.ID do not have any corresponding records in Trades.CompanyID? Does the error have anything to do with having a one-to-many relationship between the tables?

Thanks for your help.

I have never tried to establish a Foreign Key relation ship on tables with existing data, but when you first design the tables there is NO data at all, so the fact that you have a Company record that does not have a corresponding Trades record is not the problem.  However, it the reverse where the case (a Trades record whose CompanyID record DOES NOT EXIST - a so-called 'orpahn' record, could quite possibly be the cause of the problem.

Try this:

Select * from Trades where Trades.CompanyID not in (Select CompanyID from Company)

to see it there are any orphan records.  If there are any, you will need to correcdt these records before you can set up the FK constraint - as these records would violate that constraint, and thus be 'illegal' records.


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

you are correct in your last statement. If you have a company.ID that is not in the Trades.CompanyID you will not be able to create that relationship, unless you fix the problem.  Thats the whole point of referential integrity.
Are you using a zero to indicate no relationship at the moment? I have seen this done before. If so, you need to update all these zeros to Nulls before you create the relationship.
tomfolinsbeeAuthor Commented:
Thanks everyone for your help. I've saved the relationship.

This is what I did:
I updated Trades.CompanyID = dummy value where CompanyID = 0. Since the Company table uses autonumber to set the primary key, I could just insert a Company.ID = 0 record.

Turns out there were still a handful of records where Trades.CompanyID did not have a corresponding record in the company table. I thought I checked that with an inner join query where Company.ID = null, but might done in incorrectly. This morning I tried Select * from Trades where Trades.CompanyID not in (Select CompanyID from Company) as suggested by AW. Not sure if the "not in" generates the same results as <>null , but at least I found the records that were preventing me from establishing the relationship.  

Again, thanks for your help.

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

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.