PostgreSQL mini cookbook: Constraining your data
Making sure a related record exists
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.
FOREIGN KEY on the diary table
REFERENCES the primary key in companies.
ALTER TABLE ... ADD CONSTRAINT after the event.
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.
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.
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 int4and attaches it to a sequence.
UNIQUEmodifier creates a unique index on the value. You might want to add
NOT NULLas well if the field should always have a value.
PRIMARY KEYis 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.
Add a CHECK constraint with a boolean expression that returns true when the field’s value is valid.
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).
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.
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.
- PostgreSQL SQL Create Table
- PostgreSQL SQL Alter Table
- PostgreSQL SQL Create Index
- PostgreSQL SQL Create Trigger
- PostgreSQL SQL Create Function
- PostgreSQL SQL Create Rule
Encrypted your DNS to protect your privacy and firewall to block phishing, malicious domains, block ads in all browsers and apps