Today I was creating a constraint in my DB using this statement:
ALTER TABLE dbo.[Cars] ADD CONSTRAINT FK_Cars_Users
FOREIGN KEY (UserId) REFERENCES dbo.[Users](ID) ON UPDATE NO ACTION ON DELETE NO ACTION
And got this weird error message:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_Cars_Users”. The conflict occurred in database “MyDatabaseName”, table “dbo.Users”, column ‘ID’.
The problem came as I didn’t realized I had set a default value for FK UserId to 0, and primary key in Users was autoincrmental starting at 1, so there would never be a UserId=0 in User table and SQL complained about it.
ALTER TABLE Cars ADD UserID int NOT NULL DEFAULT 0
Good someone helped me finding the problem, I was blind!
So make sure when you create foreign keys that their default value (if any) will have something in the other side 😉
