Is it possible to restrict a table to have only one record in mysql?
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?
Answer
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