CODEWITHSUNDEEP
sqlite
t3rrh42d2
t3rrh42d2

Reputation: 134

Limiting the number of rows a table can contain based on the value of a column - SQLite

Since SQLite doesn't support TRUE and FALSE, I have a boolean keyword that stores 0 and 1. For the boolean column in question, I want there to be a check for the number of 1's the column contains and limit the total number for the table.

For example, the table can have columns: name, isAdult. If there are more than 5 adults in the table, the system would not allow a user to add a 6th entry with isAdult = 1. There is no restriction on how many rows the table can contain, since there is no limit on the amount of entries where isAdult = 0.

Upvotes: 0

Views: 36

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

The SQL-99 standard would solve this with an ASSERTION— a type of constraint that can validate data changes with respect to an arbitrary SELECT statement. Unfortunately, I don't know any SQL database currently on the market that implements ASSERTION constraints. It's an optional feature of the SQL standard, and SQL implementors are not required to provide it.

A workaround is to create a foreign key constraint so isAdult can be an integer value referencing a lookup table that contains only values 1 through 5. Then also put a UNIQUE constraint on isAdult. Use NULL for "false" when the row is for a user who is not an adult (NULL is ignored by UNIQUE).

Another workaround is to do this in application code. SELECT from the database before changing it, to make sure your change won't break your app's business rules. Normally in a multi-user RDMS this is impossible due to race conditions, but since you're using SQLite you might be the sole user.

Upvotes: 1

CL.
CL.

Reputation: 180060

You can use a trigger to prevent inserting the sixth entry:

CREATE TRIGGER five_adults
BEFORE INSERT ON MyTable
WHEN NEW.isAdult
 AND (SELECT COUNT(*)
      FROM MyTable
      WHERE isAdult
     ) >= 5
BEGIN
    SELECT RAISE(FAIL, "only five adults allowed");
END;

(You might need a similar trigger for UPDATEs.)

Upvotes: 1

Related Questions