没有约束可以强制执行该规则,但是可以使用如下所示的触发器来做到这一点:
CREATE TRIGGER Images_not_more_than_five_per_add
ON Images FOR INSERT
AS
DECLARE @RowCount int
SET @RowCount = @@ROWCOUNT
SET NOCOUNT ON
IF @RowCount = 1
BEGIN
IF (SELECT COUNT(*) FROM Images WHERE Images.addid = (SELECT addid FROM inserted)) > 5
BEGIN
RAISERROR('No more than five images per add are allowed', 16, -1)
ROLLBACK
RETURN
END
END
ELSE
BEGIN
IF EXISTS (
SELECT *
FROM
Images
INNER JOIN (
SELECT DISTINCT addid FROM inserted
) I ON Images.addid = I.addid
GROUP BY
Images.addid
HAVING COUNT(*) > 5
)
BEGIN
RAISERROR('No more than five images per add are allowed', 16, -1)
ROLLBACK
RETURN
END
END