Msg 547, Level 16, State 0, Line 1 (help please)
I use this statements to do add a constraint or to add a foreign key.
USE Kudler1_FF
ALTER TABLE Employee_Tbl
ADD FOREIGN KEY (JobTitle)
REFERENCE Job_Tbl (JobTitle)
and this one:
Alter TABLE Employee_Tbl
ADD Constraint FK_JobTitle
FOREIGN KEY (JobTitle)
REFERENCES Job_Tbl(JobTitle)
but now I get this error:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_JobTitle". The conflict occurred in database "Kudler1_FF", table "dbo.Job_Tbl", column ‘JobTitle‘.
回答
The table Employee_Tbl must be empty before you create the constraint or the foreign key because the database engine will validate the data present on this table.
If the table already have some data that does not match with Job_Tbl, you will get the error message. Hope that helps.
ALTER TABLE [dbo].[CMS_Permission] ADD CONSTRAINT [FK_CMS_Permission_ResourceID_CMS_Resource] FOREIGN KEY ([ResourceID]) REFERENCES [dbo].[CMS_Resource] ([ResourceID]);
Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> Microsoft.Data.SqlClient.SqlException: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_CMS_Permission_ResourceID_CMS_Resource". The conflict occurred in database "mydatabase", table "dbo.CMS_Resource", column ‘ResourceID‘.
所以，应该是[CMS_Permission]里面多加了数据，有新的ResourceID。而这个id不在[CMS_Resource]表中