mysql - SQL删除更新外键主键约束

Question

假设我有一个表，其中已经包含“IsDelete Char(1)”列。每当我对该表进行删除过程时，我不执行实际的删除命令。

eg. DELETE FROM TableName 

但是我做了更新命令。

eg. UPDATE TableName SET IsDelete = '1' .....

因此，如果我想为这个表建立参照完整性，我不知道如何才能做到。因为我没有发出实际的删除命令。请解释一下。

Answer 1

检查这个使用 SQL Server 测试的声明性解决方案(查看源代码中的注释):

CREATE TABLE dbo.SalesOrder
(
    SalesOrderID INT IDENTITY(1,1)
    ,OrderDate DATETIME NOT NULL
    ,IsDeleted CHAR(1) NOT NULL DEFAULT 'N'
    ,CONSTRAINT PK_SalesOrder PRIMARY KEY (SalesOrderID)
    ,CONSTRAINT CK_SalesOrder_IsDeleted CHECK(IsDeleted IN ('Y','N'))
);

CREATE TABLE dbo.SalesOrderDetail
(
    SalesOrderDetailID INT IDENTITY(1,1)
    ,Qty DECIMAL(8,2) NOT NULL
    ,UnitPrice DECIMAL(8,2) NOT NULL
    ,IsDeleted CHAR(1) NOT NULL DEFAULT 'N'
    ,SalesOrderID INT NOT NULL
);

--We need this index to create the next foreign key constraint
CREATE UNIQUE NONCLUSTERED INDEX IUN_SalesOrder_SalesOrderID_IsDeleted
ON  dbo.SalesOrder(SalesOrderID, IsDeleted);

--If we "delete" (UPDATE dbo.SalesOrder SET IsDeleted = "Y" ...) a row from dbo.SalesOrder table, 
--then this modification (... SET IsDeleted = "Y" ...) will be propagated to dbo.SalesOrderDetail table
--because of ON UPDATE CASCADE clause
ALTER TABLE dbo.SalesOrderDetail
ADD CONSTRAINT FK_SalesOrderDetail_SalesOrder_SalesOrderID_IsDeleted
FOREIGN KEY (SalesOrderID, IsDeleted) REFERENCES dbo.SalesOrder(SalesOrderID, IsDeleted)
ON UPDATE CASCADE;

INSERT  dbo.SalesOrder (OrderDate)
SELECT  '20110101'
UNION ALL
SELECT  '20110202'
UNION ALL
SELECT  '20110303';

INSERT  dbo.SalesOrderDetail (Qty, UnitPrice, SalesOrderID)
SELECT  1,10,1 UNION ALL SELECT 1,11,1 UNION ALL SELECT 1,12,1
UNION ALL 
SELECT 2,20,2
UNION ALL 
SELECT 3,30,3 UNION ALL SELECT 3,31,2;

SELECT  *
FROM    dbo.SalesOrder
SELECT  *
FROM    dbo.SalesOrderDetail 

--Test "DELETE"/UPDATE statement
UPDATE  dbo.SalesOrder 
SET     IsDeleted = 'Y'
WHERE   SalesOrderID = 1;

--Now, we can check SalesOrderDetail rows ([Status] values WHERE [SalesOrderID]=1)
SELECT  *
FROM    dbo.SalesOrder
SELECT  *
FROM    dbo.SalesOrderDetail 

DROP TABLE dbo.SalesOrderDetail;    
DROP TABLE dbo.SalesOrder;