Any way to enforce numeric primary key size limit in sql?

Question

I'd like to create a table which has an integer primary key limited between 000 and 999. Is there any way to enforce this 3 digit limit within the sql?

I'm using sqlite3. Thanks.

Answer 1

You may be able to do so using a CHECK constraint.

But,

CHECK constraints are supported as of version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.

(from here)

So unless SQLite 3 = SQLite 3.3 this probably won't work

Answer 2

SQLite supports two ways of doing this:

Define a CHECK constraint on the primary key column:

CREATE TABLE mytable (
  mytable_id INT PRIMARY KEY CHECK (mytable_id BETWEEN 0 and 999)
);

Create a trigger on the table that aborts any INSERT or UPDATE that attempts to set the primary key column to a value you don't want.

CREATE TRIGGER mytable_pk_enforcement
BEFORE INSERT ON mytable
FOR EACH ROW 
  WHEN mytable_id NOT BETWEEN 0 AND 999
BEGIN
  RAISE(ABORT, 'primary key out of range');
END

If you use an auto-assigned primary key, as shown above, you may need to run the trigger AFTER INSERT instead of before insert. The primary key value may not be generated yet at the time the BEFORE trigger executes.

You may also need to write a trigger on UPDATE to prevent people from changing the value outside the range. Basically, the CHECK constraint is preferable if you use SQLite 3.3 or later.

note: I have not tested the code above.

Answer 3

Indeed using the CHECK constraint it won't allow numbers outside that range, but that didn't work exactly like I wanted (and I'm sorry I didn't mention that), because I wanted it to use the first available primary key (freed by deleted rows) after it tries the 1000th key.

Thanks for the answers, I'll look into the TRIGGER option.

Answer 4

jmisso, I would not recommend reusing primary keys that have been deleted. You can create data integrity problems that way if all other tables that might have that key in them were not deleted first (one reason to always enforce setting up foreign key relationships in a database to prevent orphaned data like this). Do not do this unless you are positive that you have no orphaned data that might get attached to the new record.

Why would you even want to limit the primary key to 1000 possible values? What happens when you need 1500 records in the table? This doesn't strike me as a very good thing to even be trying to do.

Answer 5

HLGEM, you're probably right. I was trying to enforce that because that key is part of a larger 7-digit code, which I can't allow to grow larger. Probably this table won't even reach 500 rows in a couple of years, since it is for a small store. Lets hope that until then, they can afford to get RFIDs.

Answer 6

Why is it an integer? You said that it was between 000 and 999, so it sounds like you're not actually doing math on the integer.

If it's not something you're doing arithmetic with, you should be using characters.

Answer 7

What about pre-populating the table with the 1000 rows at the start. Toggle the available rows with some kind of 1/0 column like Is_Available or similar. Then don't allow inserts or deletes, only updates. Under this scenario your app only has to be coded for updates.