Limit the number of records a table can have

Question

I created a table, master-domain. This table should only have 3 records. How can I limit mysql database to only allow NO MORE than that number of records? Is there a specific sql command to acomplish this?

This is my current SQL:

CREATE TABLE `mydatabase`.`master-domain`
(
`domain` VARCHAR( 50 ) NOT NULL COMMENT 'Domain Name',
PRIMARY KEY ( `domain` )
)

PS. I have godaddy and it includes phpMyAdmin, in addition to MySQL databases.

You want a db table that have no more than 3 records? Might as well not use a database. — Churk, Mar 08 '12 at 19:30
@churk the use of a database is a must, and this table is just one in a series of tables with different rules — Omar, Mar 08 '12 at 19:39
I had this error: `MySQL said: #1227 - Access denied; you need the SUPER privilege for this operation` while trying to run: `DELIMITER $$ CREATE TRIGGER maxrow BEFORE INSERT ON \`master-domain\` FOR EACH ROW BEGIN SELECT COUNT(*) INTO @cnt FROM \`master-domain\`; IF @cnt >= 3 THEN CALL sth(); -- raise an error END IF; END $$ DELIMITER ;` — Omar, Mar 08 '12 at 20:03
@Omar: So? Isn't that enough information in the error message? — zerkms, Mar 08 '12 at 20:24

octern · Answer 1 · 2012-03-08T21:18:04.367

5

You can make a table's primary key a field of type ENUM. For example:

CREATE TABLE test (
    id enum('1','2') NOT NULL, 
    domain varchar(50) NOT NULL, 
    primary key (id));

When you update it you have to explicitly set the ID to "", "1", or "2".* It can't be null and there can only be one record with each ID. The domain is still stored in the domain field, so hopefully whatever external system is querying this database won't have any problems getting the results it wants.

If you want to replicate the current restriction that domain names have to be unique, you could also add unique key (domain).

* note that the empty string is allowed (and not the same as NULL) because enum is actually a type of string. So you'd specify two permitted ID values in order to have three total.

Alternately: What are you trying to achieve / prevent here? Is there some automated process that might add records to the table? Are you trying to make sure that you don't accidentally do it, or that someone who hijacks your account can't do it?

If the process that might insert records is running on your user, you could put your three records into the table and then take away INSERT privileges from yourself. You'd still be able to alter the existing records but you wouldn't be able to add any more unless you explicitly re-granted the ability.

edited Mar 08 '12 at 21:18

answered Mar 08 '12 at 20:22

octern

4,825
21
38

With `set`, you can have up to 8 (=2^3) different ids. You either need `ENUM` type or an FK to another table with 3 rows. – ypercubeᵀᴹ Mar 08 '12 at 20:43
1

See this question for an FK answer: [Limit number of records using trigger and constraints in MySQL](http://stackoverflow.com/questions/9621509/limit-number-of-records-using-trigger-and-constraints-in-mysql/9622048#9622048) – ypercubeᵀᴹ Mar 08 '12 at 20:44
@ypercube Thank you, but can you elaborate a tiny bit more? I tested this table definition and it seems to limit me to 3 entries. The ID field can't have duplicates, it can't be null, and any other value gets changed to either 1 or 3 and then generates a "duplicate entry" error. I'm totally willing to believe I'm overlooking something, but I can't tell what. Thanks again! – octern Mar 08 '12 at 20:56
It will also allow values of: `'1,2'` and `'1,3'` and `'2,3'` and `'1,2,3'` and just `''` – ypercubeᵀᴹ Mar 08 '12 at 21:00
I see. I was making an unrelated error while testing it, making it appear that those didn't work when in fact they do. I also clearly didn't understand what a SET does. Thanks for enlightening me. It looks like an ENUM might work as intended if you limited it to '', '1', and '2', though... – octern Mar 08 '12 at 21:17
No, ENUM is a valid approach. – ypercubeᵀᴹ Mar 08 '12 at 21:17
Now I see that you said that in your initial answer, too :P – octern Mar 08 '12 at 21:18

score 1 · Answer 2 · answered Mar 08 '12 at 19:35

1

You can have a look here at the MAX_ROWS parameter. However, I believe this is normally used to make the table size larger than the disk size and I don't think you would get the restriction you are looking for using it. Alternatively, you could just select the top 3 rows.

I would question the point of using a database to only store 3 rows - it seems a total waste.

answered Mar 08 '12 at 19:35

christophmccann

4,181
7
42
66

I used `CREATE TABLE \`mydatabase\`.\`master-domain\` (\`domain\` VARCHAR( 50 ) NOT NULL COMMENT 'Domain Name', PRIMARY KEY ( \`domain\` ) ) MAX_ROWS = 3` but it did not work. It still allows more records to be inserted – Omar Mar 08 '12 at 19:49
1

MAX_ROWS isn't used for limit on the number of rows. It is used when creating the DB files as a guide for how to store data and reserve space. I have posted my answer below. Try it. – Mit Mehta Jul 13 '17 at 11:55

score 0 · Answer 3 · answered Jul 13 '17 at 09:56

I think there is no such inbuilt functionality provided by MySQL. One solution is that you can create trigger.

CREATE TRIGGER your_trigger_name
BEFORE INSERT ON master-domain
FOR EACH ROW
BEGIN
    DECLARE cnt INT;

    SELECT count(*) INTO cnt FROM master-domain;

    IF cnt = 10 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can store only 3 records.';
    END IF;
END;

Try above trigger on your table. Hope this will help you.

Limit the number of records a table can have

3 Answers3