tomfolinsbee
asked on
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.
'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.
ASKER
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.
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.
ASKER
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.
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.
ASKER CERTIFIED 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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
AW