你好,我想为一个表添加一个检查约束,但是我想比较来自不同表的两列
ALTER TABLE Installation
ADD Constraint chk_1 check (DateInstallation < dateachat)
DateInstallation是“安装”表的一列,而dateachat来自另一个名为“支持”的表,因此,如何做到这一点,即代码给了我一个错误并表示感谢
你好,我想为一个表添加一个检查约束,但是我想比较来自不同表的两列
ALTER TABLE Installation
ADD Constraint chk_1 check (DateInstallation < dateachat)
DateInstallation是“安装”表的一列,而dateachat来自另一个名为“支持”的表,因此,如何做到这一点,即代码给了我一个错误并表示感谢
您不能通过简单的check
约束来做到这一点。您基本上有两个选择:
insert
上使用update
和installation
触发器。使用UDF,约束看起来像:
ALTER TABLE Installation ADD Constraint chk_1
CHECK (DateInstallation < dbo.get_dateachat(installationId));
使用上述任何一种解决方案,如果dateachat
发生更改,您可能都需要在 second 表上施加约束。不会根据 second 表中的更改评估触发器和检查约束。
您想与哪个dateachat比较?鉴于它“来自另一个表”,因此可能会有> 1行,因此,可能有> 1个dateachat值要进行比较。
您有责任在表达式中进行指定,例如
ADD约束chk_1检查(DateInstallation
这种性质的Check约束在标准SQL中确实是有效的约束,但是您找不到支持或接受它们的实际产品,因为SELECT FROM现在清楚地表明这是一个多表约束。典型的SQL DBMS对于CHECK约束接受的评估范围是一个单元组(/ row)。
(另外,CHECK约束定义的表达式必须由建议的元组(/ rows) 满足 。这意味着您的情况似乎表明安装日期必须在购买日期之前 ,可能看起来很奇怪。)
,您需要在这里问自己很多问题,以及可能的解决方法。
您可以使用UPDATE或INSERT TRIGGERS来执行这些操作。
开始时您的约束定义有点奇怪-您打算如何将表中的行与表中的行相关联?支持获取“ dateachat”吗?
有一个(可能很强)的论点是,这里的内容是业务逻辑,不应作为约束复制到数据库中,而应在应用程序逻辑中强制执行。这也引发了有关您的数据库结构的一些问题-可能有更好的方法来设计表以避免这种约束吗?
您可以使用标量函数执行此操作。 将逻辑放入函数中,然后检查(条件“涉及函数和表列”为真)
经验法则,请尝试使用最好返回true或false(0,1)的函数。 安全播放,并始终在检查约束定义CHECK(ISNULL(function,0)= 1)中检查是否为空。尽量不要滥用对函数的检查,如果函数开始执行聚合(计算现有行等),那么可能是时候重新考虑数据完整性的方法了,保持简单。
CREATE TABLE dbo.Purchase
(
PurchaseId INT IDENTITY PRIMARY KEY,Description VARCHAR(10),PurchaseDate DATE
);
GO
CREATE TABLE dbo.Installation
(
InstallationId INT IDENTITY,InstallationDate DATE,InstallationPurchaseId INT NOT NULL
);
GO
CREATE OR ALTER FUNCTION dbo.CheckIfPurchaseIsBeforeDate(@PurchaseId INT,@Date DATE)
RETURNS BIT
AS
BEGIN
RETURN (SELECT ISNULL(MIN(1),0) WHERE EXISTS(SELECT * FROM dbo.Purchase WITH(NOLOCK) WHERE PurchaseId = @PurchaseId AND PurchaseDate < @Date));
END
GO
--ALTER TABLE dbo.Installation DROP CONSTRAINT chkInstallationDate;
ALTER TABLE dbo.Installation ADD CONSTRAINT chkInstallationDate CHECK(ISNULL(dbo.CheckIfPurchaseIsBeforeDate(InstallationPurchaseId,InstallationDate),0) = 1);
GO
--two orders
INSERT INTO dbo.Purchase(Description,PurchaseDate)
VALUES('November','20191110'),('December','20191206');
GO
--install the order of november2019 in january 2020
INSERT INTO dbo.Installation(InstallationDate,InstallationPurchaseId)
VALUES('20200115',1);
GO
--install the order of december2019 in october2019
INSERT INTO dbo.Installation(InstallationDate,InstallationPurchaseId)
VALUES('20191020',2); --check constraint conflict
GO
--set the installation of november2019 back to october
UPDATE dbo.Installation
SET InstallationDate = '20201010'
WHERE InstallationId = 1; --check constraint conflict
GO