Tutorial - Referential Integrity

Tutorial - Referential Integrity  

Referential Integrity is an excellent way to stop discrepencies from entering your data.

This is a really basic introduction tutorial to get you started with the idea of referential integrity, and how you can use it to

protect your data.

We'll start off with some basic examples.

First, connect to your database:

tomcat@elrond ~$ psql -d tutorial

Now, we create the 'parent' table that the 'child' table refers to. In this example, we'll create two tables

(client and clientcontact) which lets you have multiple contacts for a client.

CREATE TABLE client (

clientid SERIAL NOT NULL PRIMARY KEY,

clientname VARCHAR(255)

Here's the 'child' table.

CREATE TABLE clientcontact (

contactid SERIAL NOT NULL PRIMARY KEY,

clientid int CONSTRAINT client_contact_check REFERENCES client(clientid),

name VARCHAR(255),

phone VARCHAR(255),

fax VARCHAR(255),

emailaddress VARCHAR(255)

After copying the table syntax into your PostgreSQL prompt, you'll receive some notices back:

NOTICE: CREATE TABLE will create implicit sequence 'clientcontact_contactid_seq' for SERIAL column 'clientcontact.contactid'

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'clientcontact_pkey' for table 'clientcontact'

NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)

The first two should be familiar from previous examples.

The third one is the one we're most interested in. That means that the 'constraint' that we want is in place.

The SQL creating the table is slightly different. Note the line,

clientid int CONSTRAINT client_contact_check REFERENCES client(clientid)

So, as usual the field is called 'clientid', using an 'int' (integer) as the datatype. The next part means

create a constraint called client_contact_check, which refers to the clientid of the client table. The syntax for doing this:

<fieldname> <datatype> CONSTRAINT <constraint_name> REFERENCES

<table_name>(<field_to_reference>)

Now, if we add some data to our tables:

tutorial=> INSERT INTO client(clientid,clientname) VALUES (1,'Client 1');

tutorial=> INSERT INTO client(clientid,clientname) VALUES (2,'Client 2');

tutorial=> INSERT INTO clientcontact(clientid, name, phone, fax, emailaddress) VALUES (1, 'Chris', '12345', '67890',

'tomcat@designmagick.com');

tutorial=> INSERT INTO clientcontact(clientid, name, phone, fax, emailaddress) VALUES (1, 'Chris', '23456', '78901',

'chris@designmagick.com');

We'll check the data went into the database ok:

tutorial=> SELECT * FROM client;

clientid | clientname

1 | Client 1

2 | Client 2

(2 rows)

tutorial=> SELECT * FROM clientcontact;

contactid | clientid | name | phone | fax | emailaddress

1 | 1 | Chris | 12345 | 67890 | tomcat@designmagick.com

2 | 1 | Chris | 23456 | 78901 | chris@designmagick.com

(2 rows)

Now we'll try and delete the first client:

tutorial=> DELETE FROM client WHERE clientid='1';

ERROR: client_contact_check referential integrity violation - key in client still referenced from clientcontact

It won't let us! It would break the integrity of the data (since there is still a client with clientid of 1). So to actually

perform this action, we'd have to delete all of the contacts for the first client before we can remove the first client from the

table.

We can delete the second client (since nothing references it) :

tutorial=> DELETE FROM client WHERE clientid='2';

DELETE 1

If we try and add a contact for a client that doesn't exist (for example clientid of 3):

tutorial=> INSERT INTO clientcontact(clientid, name, phone, fax, emailaddress) VALUES (3, 'Chris', '12345', '67890',

'tomcat@designmagick.com');

We get an error:

ERROR: client_contact_check referential integrity violation - key referenced from clientcontact not found in client

So it doesn't let us put data into child tables without properly referencing the parent table (we can't add a contact for a

client that doesn't exist!).

These basic ideas can stop your data from becoming totally useless. There's not much use having a contact for a client that

doesn't exist!