• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 960
  • Last Modified:

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?

Thanks.  







0
tomfolinsbee
Asked:
tomfolinsbee
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Arthur_WoodCommented:
sounds like you ALREADY have a Constraint named 'FK_Trades_Companies'.  

AW
0
 
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.
0
 
RazixCommented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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.


0
 
Arthur_WoodCommented:
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.

AW
0
 
RazixCommented:
Tom,

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.
0
 
muzzy2003Commented:
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.
0
 
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.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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