How can I ensure that there is one-and-only-one row in DB table?

Question

I would like to ensure that my MySQL table named "myTable" has one-and-only-one row.

So I need to be able to do Update on this row but obviously Insert and Remove should be not possible.

I am asking this question because of this Stack Overflow answer

Thanks!

score 19 · Answer 1 · edited Sep 02 '15 at 08:32

19

CREATE TABLE `myTable` (
  `id` enum('1') NOT NULL,
  `MyValue1` int(6) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The ENUM(''1'') construct as primary key is used to prevent that more than one row can be entered to the table';

edited Sep 02 '15 at 08:32

AlexB

7,302
12
56
74

answered Sep 02 '15 at 08:12

John

199
1
2

3

Although this might be a good answer, please consider adding some more explanation about why this is the right solution and what exactly you are doing :) – Gerald Versluis Sep 02 '15 at 09:08
A better solution, if you ask me. Instead of intercepting events with triggers, you model the undesired state impossible. – Carlos Ferreyra Apr 29 '21 at 17:06
NOT NULL is not required as the PRIMARY KEY column by default has NOT NULL constraint – Mohan Jun 28 '21 at 07:55

score 6 · Answer 2 · answered Nov 18 '16 at 01:40

The easiest approach is to create a column that only has one legal value, then to declare it not null unique.

This forces the table to have no more than one row, but still allows zero rows. If you need to prevent the removal of the only row, use the trigger approach given in a different answer.

create table example( 
    enforce_one_row enum('only') not null unique default 'only',
    ... your columns ....
);

score 6 · Accepted Answer · answered Jan 17 '11 at 17:42

6

Two suggestions for you, one or both which may work depending on the particulars of the data you're trying to store:

See if the ENUM or SET constraints will work for you; note that they offer differing levels of enforcement based on SQL mode (e.g., strict). See more info:

http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html

AND/OR

Implement INSERT, UPDATE, and DELETE triggers to control access on the data (you may want to populate the data initially before you create these; again, it depends on your scenario)

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

answered Jan 17 '11 at 17:42

kvista

5,039
1
23
25

I am new to triggers... Could you give me an example of a trigger which would stop all insert, update and delete on a specific table? – benjisail Jan 18 '11 at 14:55
Check out the workaround specified in http://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions – kvista Jan 18 '11 at 15:37
BTW, keep in mind that you can also write an AFTER trigger to "clean up" bad data. Ideally, constraints would prevent the data from getting in the table in the first place, but when you run into the limits of what I've described above (and I admit triggers do not address all needs), you might take a "clean up" approach, after the fact. Also, consider that if you need to make sure there's only one row in the table for readers only, you could always create a view off the base table that would prevent the display of data you didn't want - you may get more flexibility that way. – kvista Jan 18 '11 at 15:44

score 1 · Answer 4 · answered Jan 17 '11 at 16:17

1

Try this:

CREATE TABLE foo (x INT NOT NULL PRIMARY KEY CHECK (x = 1), col1 INT NOT NULL, col2 INT NOT NULL);

answered Jan 17 '11 at 16:17

nvogel

24,981
1
44
82

1

It seems that as the MySQL Reference Manual says: "The CHECK clause is parsed but ignored by all storage engines." – benjisail Jan 17 '11 at 16:50
1

benjisail, You are right. MySQL sucks. But it will work in all the other major DBMSs. – nvogel Jan 17 '11 at 17:10

score 0 · Answer 5 · answered Jun 10 '14 at 08:31

    create table if not exists myTable (
        ID int not null
    ) engine=innodb;

    select 'create trigger tbi_myTable';
    drop trigger if exists tbi_myTable;
    delimiter //
    create trigger tbi_myTable 
        before insert on myTable 
        for each row
    begin
        if (select count(1) from myTable) > 0 then
            -- Signal is only in 5.6 and above use another way to raise an error: if less than 5.6
            SIGNAL SQLSTATE '50000' SET MESSAGE_TEXT = 'Cannot insert into myTable only one row alowed!';
        end if;
    END //
    delimiter ;
    insert into myTable values (1);

    -- This will generate an error
    insert into myTable values (2);

    -- This will only have one row with "1"
    select * from myTable;

score 0 · Answer 6 · answered Jan 17 '11 at 16:13

You can make use on tables privileges (assuming once you have set the relevant privileges to block insert/delete, and you should not update the privileges anymore, however, any user has the privileges can override the privileges again)

insert the one-and-only-one record first then add in the privileges by disable INSERT, UPDATE, DROP in column table_priv

mysql> desc tables_priv;
+-------------+-------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-------+
| Field       | Type                                                                                                                    | Null | Key | Default           | Extra |
+-------------+-------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-------+
| Host        | char(60)                                                                                                                | NO   | PRI |                   |       |
| Db          | char(64)                                                                                                                | NO   | PRI |                   |       |
| User        | char(16)                                                                                                                | NO   | PRI |                   |       |
| Table_name  | char(64)                                                                                                                | NO   | PRI |                   |       |
| Grantor     | char(77)                                                                                                                | NO   | MUL |                   |       |
| Timestamp   | timestamp                                                                                                               | NO   |     | CURRENT_TIMESTAMP |       |
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view') | NO   |     |                   |       |
| Column_priv | set('Select','Insert','Update','References')                                                                            | NO   |     |                   |       |
+-------------+-------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-------+
8 rows in set (0.00 sec)

score 0 · Answer 7 · answered Dec 14 '11 at 01:58

0

If your database is set to 'strict' mode, you can create a table like this one:

create table foo (id enum('SYSROW') not null, (other column definitions) primary key (id));

answered Dec 14 '11 at 01:58

Jeshurun

22,940
6
79
92

score -1 · Answer 8 · answered Jan 17 '11 at 16:14

-1

You can check in mySQL using IF statement.

IF (SELECT count(*) FROM myTable) = 1
    //your SQL code here.

answered Jan 17 '11 at 16:14

dlock

9,447
9
47
67

How can I ensure that there is one-and-only-one row in DB table?

8 Answers8