限制表以限制要存储的记录数 [英] Constraint on table to limit number of records to be stored

问题描述

我有一个数据库，有两个表 Ads 和 Images 。 Ads 表中有一个主键 adid ，它是 Images 中的外键code>表。

I have a database and there are two tables Ads and Images. There is a primary key adid in Ads table which is a foreign key in Images table.

我想在表图像上创建一个不超过5个 adid 可以存储在 Images 表中。

I want to created a constraint on table Images that not more than 5 adid can be stored in Images table.

我需要知道这种约束的类型是什么，以及如何在SQL Server中使用查询来完成此约束。

I need to know what this type of constraint is called and how I can accomplish this with query in SQL Server.

推荐答案

没有约束强制执行该规则，但可以使用以下触发器来做到这一点：

There is no constaint to enforce that rule, but a trigger like the following can do it:

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

这篇关于限制表以限制要存储的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！