RSS

PostgreSQL mini cookbook: Constraining your data

PostgreSQL mini cookbook: Constraining your data, those tricks back to 2001 and still works today. 😉

Records in one table should all be connected to records in another table - you shouldn’t have diary entries for a company that isn’t listed in the companies table.

Solution

Define a FOREIGN KEY on the diary table that REFERENCES the primary key in companies.

Or use ALTER TABLE ... ADD CONSTRAINT after the event.

Discussion

In the definition of table diary we have

dy_company field REFERENCES companies(co_id)

Field dy_company is known as a “foreign key” since it refers to the key field (co_id) in a foreign table (companies).

You can do this after the event using:

ALTER TABLE diary
  ADD CONSTRAINT diary_company_ref
    FOREIGN KEY (dy_company) REFERENCES companies(co_id);

If there are values of dy_company that cannot be found in companies an error will be given.

Making sure each record has a unique value

You want to make sure each order-id is unique since two orders with the same id-code is going to cause problems.

Solution

Either use the UNIQUE modifier on the field when defining the table, or create a UNIQUE index on that field (or fields) after the table is created.

Discussion

It is customary to make sure there is a unique code for each record in a table (a primary key) so you can select that record and no other.

If you don’t know why this is a good idea, search the web for terms like “Normalisation” and “Normal forms”.

When creating a table all the following fields will be forced to be unique.

CREATE TABLE foo (
  field1 serial
  field2 text UNIQUE
  field3 text PRIMARY KEY
  field4 text,
  field5 text
PRIMARY KEY (field4)
);
CREATE UNIQUE INDEX foo_field5_idx ON foo(field5);

  • The serial type creates a UNIQUE NOT NULL int4 and attaches it to a sequence.

  • The UNIQUE modifier creates a unique index on the value. You might want to add NOT NULL as well if the field should always have a value.

  • The PRIMARY KEY is equivalent to UNIQUE NOT NULL.

Which form you want to use will depend upon context and your personal preferences.

Allowing only certain values in a field

You have an enumerated field that can only take certain well-defined values, and want to restrict it to those values (e.g. a day-of-week field, a prime-number field) - you think a foreign key referencing a lookup table isn’t right in this case.

Solution

Add a CHECK constraint with a boolean expression that returns true when the field’s value is valid.

Discussion

In the diary table, the dy_type field can only have certain values.

CREATE TABLE diary (
  dy_type text CHECK (
    dy_type IN (
      'Created', 'Order', 'Delivery', 'Paid', 'Note'
    )
  ),
...

Note that you need to be careful with this - if we add a new diary-type (say ‘Message’) you will need to change the table definition, whereas with a foreign key you would just add another row to the lookup table.

Preventing changes to a field

You want to effectively “lock” a field so that after the record has been inserted, that field can never be changed (a creation_date field for example).

Solution

You use an UPDATE trigger that masks the field.

That calls a function that either discards the change or raises an error if there is a change to the protected field.

Discussion

To make sure the dy_timestamp field cannot be modified use:

CREATE FUNCTION noupd_ts() RETURNS opaque AS '
BEGIN
  NEW.dy_timestamp = OLD.dy_timestamp
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER diary_noupd_ts_trig
  BEFORE UPDATE ON diary
    FOR EACH ROW
      EXECUTE PROCEDURE noupd_ts();

The function silently discards any changes to the dy_timestamp field if users update the diary table.

References