A restriction that allows only 10 instances of the string "username",

Question

I want to add a restriction to my database table so that the string "username" can only be entered into a specific column by a maximum of 10 times. Is it possible?

I am using ms sql server.

Answer 1

You can use AFTERtrigger UPDATE/INSERT:

SQLFiddleDEMO

CREATE TABLE tab(col NVARCHAR(100));

CREATE TRIGGER trg_tab
ON tab
AFTER INSERT, UPDATE
AS
 IF (SELECT COUNT(*) FROM tab WHERE col = 'username') > 10
 ROLLBACK;

INSERT INTO tab(col)
VALUES ('username'), ('username'), ('username'), ('username'),
       ('username'), ('username'), ('username'), ('username'),
       ('username'),('username');

INSERT INTO tab(col)
VALUES ('username');  

/* The transaction ended in the trigger. The batch has been aborted. */

Or use the restriction SCALAR FUNCTIONand CHECK:

SQLFiddleDemo2

CREATE TABLE tab(col NVARCHAR(100));

CREATE FUNCTION dbo.CheckFunction()
RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM tab WHERE col = 'username')
END;

ALTER TABLE tab
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFunction() <= 10 );

INSERT INTO tab(col)
VALUES ('username'), ('username'), ('username'), ('username'),
       ('username'), ('username'), ('username'), ('username'),
       ('username'),('username');

INSERT INTO tab(col)
VALUES ('username');  

/* The INSERT statement conflicted with the CHECK constraint "chkRowCount".
 The conflict occurred in database "db_6_34d5e", table "dbo.tab". */

Answer 2

Create a trigger AFTER INSERTin the table. something like that

create trigger LimitTable
on YourTableToLimit
after insert
as
    declare @tableCount int
    select @tableCount = Count(*)
    from YourTableToLimit

    if @tableCount > 50
    begin
        rollback
    end
go