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

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 possib开发者_运维知识库le.

I am asking this question because of this Stack Overflow answer

Thanks!

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';

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 ....
);

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

Try this:

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

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)

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));

    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;

You can check in mySQL using IF statement.

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