Constraint that allows only 10 instances of the string "username"

Question

I want to add a constraint to my database table so that the string "username" can only be entered into a certain column a max of 10 times. Is this possible?

I'm using ms sql server.

Answer 1

You can use AFTER trigger 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 SCALAR FUNCTION and CHECK constraint:

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". */