Msg 547, Level 16, State

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]表中

知识点：

A表的字段a1，和B表的字段b1有外键关系，B表里面的b1字段需要有唯一性约束。

然后A表里面的字段a1，可以为空，在B表中即使没有null的字段也是允许的。A表里面的a1字段是数值，也可以重复。

筛选语句应该这么写，A表是CMS_Permission,B表是CMS_Resource。可以筛选出A表中存在，但是B表中不存在的数据，也就是导致添加外键失败的根源

SELECT *
FROM dbo.CMS_Permission AS a
    LEFT JOIN dbo.CMS_Resource AS b
        ON a.ResourceID = b.ResourceID
WHERE b.ResourceID IS NULL
      AND a.ResourceID IS NOT NULL
ORDER BY a.PermissionID ASC;