Is it possible to restrict a table to have only one record in mysql?

Question

Hi I'm a totally newbie in web development area. I want to create a table for admin login that has only one record (because there is only one admin). I want to know is it possible to set MySQL somehow to restrict a special table to have only one record?

Solution 1

You can set up a trigger (to be specific, an Insert trigger) that counts the records and, if count is more than 1, it does not allow the insert operation.

Check out http://dev.mysql.com/doc/refman/5.0/en/insert.html and set up your logic accordingly.

Solution 2

While other answers are correct that there are better approaches to your particular question, it is possible to create a single-row table by adding a dummy enumeration column with only a single case, and making it a UNIQUE KEY (or the table's PRIMARY KEY):

mysql> CREATE TABLE only_one_row (
    restriction ENUM('') NOT NULL,
    single_row_value DATETIME NOT NULL,
    PRIMARY KEY (restriction)
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO only_one_row (single_row_value) VALUES (NOW());
Query OK, 1 rows affected (0.00 sec)

mysql> INSERT INTO only_one_row (single_row_value) VALUES ('2016-01-01');
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY"

mysql> SELECT * FROM only_one_row;
+-------------+---------------------+
| restriction | single_row_value    |
+-------------+---------------------+
|             | 2016-01-01 00:00:00 |
+-------------+---------------------+

This works because the column can only ever have a single value (the ENUM has only a single case, and can not be NULL), and the UNIQUE (or PRIMARY) key enforces that every row in the table must have a different value for the column.

This could be extended in the obvious fashion to create tables which can only contain between 0 and any specific number of rows, up to the practical limits of an ENUM. The limit in MySQL 5.7 is effectively 255 cases, but by that point it would be easier to take the UNQIUE KEY over a TINYINT UNSIGNED column instead. However, I could not find a way to enforce that a table have either 0 or N rows; this solution will only enforce "between 0 and N", as MySQL does not have working CHECK constraints. (Of course, where N is one, as in this example, the same effect is achieved.)

Note: While it may seem possible to use a generated virtual column to avoid taking up the extra byte of storage for the restricting enum, unfortunately most versions of MySQL and MariaDB will throw various errors if you try it, either because a constant value can not be used for a generated column, or if you trick the database into allowing that (for example, using GENERATED ALWAYS AS (LEAST(GREATEST(single_row_value,0),0)) VIRTUAL), because storage engines don't like placing indexes on virtual generated columns. This is documented to work for InnoDB in MySQL since 5.7.8, but it definitely does not work in MariaDB 10.1 as of this writing.

Edited to add: This is not foolproof. The "error" value for an ENUM is zero, and indexes start at one, so INSERT INTO only_one_row VALUES (0,...); INSERT INTO only_one_row VALUES (1,...); will succeed. However, this condition is unlikely to arise in normal operation, and it's still easier to keep track of than a trigger!

Edit 04/21: Since MySQL 8.0.16, MySQL does support CHECK constraints. See https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

Solution 3

There are many ways to do this that are a lot better than "force one row" in database.

1. Create a user's table and in it, create a field that is a flag which identifies a user as an admin. This could be as simple as:

   create table users (id int not null primary key auto_increment, name varchar(20), is_admin bool default 0);

   Then you simply set is_admin to 1 for an admin, and search for the admin:

   select id,name from users where is_admin;

   All users that are not admins:

   select id,name from users where not is_admin;

2. Create another table that identifies a user as an admin using a foreign key relation.

   create table users (id int not null auto_increment primary key, name varchar(25));

   create table admins (id int not null auto_incrememnt primary key, userid int, foreign key (userid) references users(id));

   Now you simply add the refernce to admins when you are inserting a user that is an admin. This approach allows you to have other flags and properties as well in this admins table (as opposed to adding lots of columns later in your users table).

Comments

[pouya]

Thanks burhan for suggesting another approach

[pouya]

I'm really impressed with it not the answer to my Q but a far better strategy

[Peter]

Great answer. One row tables are usefull, when you need a persistent user defined variable.

[Ifta]

Excellent answer!