I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error:
- Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.
Hi. Maybe the SecondCategoryId field has data that not exists in the categories table|||
mariop77 is probably right.
Try doing a SELECT DISTINCT SecondCategoryID FROM ADS
Make sure all FKs really do exist.
|||It has Nulls someties. How can I allow nulls? The reason is sometimes there will be no second category...|||Hi,
Actually, null is allowed here. But you need to make sure that Allow Null has been checked in the design view of the table.
|||Yes, it is allowed on the column level of course, yet can not create relationship...|||Seems to me like this is a limitation not allowing more than one join to the same table... Do I need to create alias for the existing table?
1 Category (alias 1) many Ads on Ads.CategoryID
1 Category (Alias 2) many Ads on Ads.SecondCategoryID
I used to be able to do this in MS Access, but maybe SQL does not allow...
sql
