Sunday, June 17, 2012

Creating a Table With Exactly One Row

Here's a situation which comes up a lot in database design: the one-row table.  That is, a table designed to hold one and only one row, which contains some settings or metadata about the database or application.  For example, imagine that we want to have a one-row table which holds the current database revision version and the date it was last upgraded:

CREATE TABLE db_version (
    version NUMERIC NOT NULL,
    upgraded_on TIMESTAMPTZ NOT NULL
);

Since the database is only one specific version at any time, it would cause problems for the upgrade scripts if there were more than one row in this table.  Which row should they believe?  So you want to ensure that there's never more than one row.

You could, of course, do this using a trigger.  However, a trigger is complex, and a BEFORE INSERT trigger needs to either count the rows, or be installed after a sample row is added.  Either is more work than it needs to be.  Try this instead:

CREATE UNIQUE INDEX db_version_one_row
ON db_version((version IS NOT NULL));

This works because the version column is already declared NOT NULL.  Since you've created a unique index on the NOT NULL expression, you can never insert a second row because it would violate the uniqueness condition.

Unfortunately, there's no clever trick to preventing that one row from being deleted; that requires a BEFORE DELETE trigger:

CREATE FUNCTION db_version_no_delete ()
RETURNS trigger
LANGUAGE plpgsql AS $f$
BEGIN
   RAISE EXCEPTION 'You may not delete the DB version!';
END; $f$;

CREATE TRIGGER db_version_no_delete
BEFORE DELETE ON db_version
FOR EACH ROW EXECUTE PROCEDURE db_version_no_delete();

Now this all works as expected; you can insert the first row to start the table, and only update it after that:

postgres=# INSERT INTO db_version VALUES ( '1.0',now());
INSERT 0 1

postgres=# INSERT INTO db_version VALUES ( '1.1',now());
ERROR:  duplicate key value violates unique constraint "db_version_one_row"
DETAIL:  Key ((version IS NOT NULL))=(t) already exists.

postgres=# UPDATE db_version SET version = '1.1', upgraded_on = now();
UPDATE 1

postgres=# delete from db_version;
ERROR:  You may not delete the DB version!
STATEMENT:  delete from db_version;

6 comments:

  1. Users can also use the simple functions with security definer. No need for one row table, and have as many columns. Function definition can be changed from SQL script.
    so I think it is even better.
    I am using it in our ERP application to maintain the version information.

    ReplyDelete
  2. I'm with Greg on this one.

    I use a column

    id integer not null default 1 unique

    and then add a check constraint

    check (id = 1)

    And of course the on delete trigger to prevent the row from being removed.


    This in Oracle land... In Postgresql Greg's solution is even nicer.

    ReplyDelete
  3. Ok, I got.

    But... Why just not store a history for the config/version?

    The database version is very useful, the config, maybe. But it's a good approach, doesn't it?

    ReplyDelete
  4. Greg,

    Yes, good point. Or even UNIQUE ON ((TRUE)).

    Daniel, Chaitanya,

    Yes, there are other approaches you might take to storing a database revision version. The point of this blog was to illustrate the one-row table, not to grapple with how to manage database revisions.

    ReplyDelete
  5. All these work, but why not just revoke delete on the table?

    ReplyDelete