1. Home
  2. Tutorials
  3. Databases
  4. PostgreSQL
  5. Transactions and Foreign Keys
Yolinux.com Tutorial

PostgreSQL: Transactions and Foreign Keys

The PostgreSQL database requires InnoDB to support transactions, foreign keys and referential integrity. This tutorial shows how to configure the PostgreSQL database for the InnoDB database engine and examples showing the use of transactions and foreign keys.

For an introduction to installation, configuration and use of the PostgreSQL database see the YoLinux PostgreSQL tutorial.

Tutorial Table of Contents:
PostgreSQL Transactions:

Database transaction support refers to the need to handle a bundle of SQL transactions as a single action. The entire group of SQL instructions must all occur or have none occur at all. This is often referred to as ACID: Atomicity, Consistency, Isolation and Durability.

The requirement is to support actions such as the following banking account transaction example:

  1. Subtract $500.00 from the account of Fred Flinstone
  2. Add $500.00 to the account of Barney Rubble
It is essential that both occur. If a system failure occurs and only one transaction occurs, then the database would be in error and in an inconsistent state.

ACID:
  • Atomicity: The transaction must either all be carried out successfully, or none at all. If aborted, the previous operations are rolled back to their former state.
  • Consistency: The database is always in a proper state and not in error.
  • Isolation: Data being used for one transaction cannot be used by another transaction until the first transaction is complete.
  • Durability: Once a transaction has completed correctly, its effects should remain, and not be reversible (unless another transaction or set of SQL commands purposefully directs a change).
Create Initial Database:
CREATE TABLE accounts (IDpk serial PRIMARY KEY,
                       name char(20),
                       balance numeric(10,2));
INSERT INTO accounts (name,balance) VALUES ('Fred Flinstone',1000.00);
INSERT INTO accounts (name,balance) VALUES ('Wilma Flinstone',1000.00);
INSERT INTO accounts (name,balance) VALUES ('Barney Rubble',1000.00);
Verify:
# select * from accounts;
 idpk |         name         | balance 
------+----------------------+---------
    1 | Fred Flinstone       | 1000.00
    2 | Wilma Flinstone      | 1000.00
    3 | Barney Rubble        | 1000.00
(3 rows)

Execute a transaction:
BEGIN;
UPDATE accounts SET balance = balance - 500.00 WHERE name = 'Fred Flinstone';
UPDATE accounts SET balance = balance + 500.00 WHERE name = 'Barney Rubble';
COMMIT;
Verify:
# select * from accounts;
 idpk |         name         | balance 
------+----------------------+---------
    2 | Wilma Flinstone      | 1000.00
    1 | Fred Flinstone       |  500.00
    3 | Barney Rubble        | 1500.00
(3 rows)

Transaction Errors:

The purpose of transactions is to "rollback" the database to a copacetic state if any one of the SQL statements fails in the collective transaction.
One must be aware of what the database regards as a failure.

BEGIN;
UPDATE accounts SET balance = balance - 500.00 WHERE name = 'Fred Flinstone';
UPDATE accounts SET balance = balance + 500.00 WHERE name = 'Betty Rubble';
COMMIT;

Output:
=# BEGIN;
BEGIN
=# UPDATE accounts SET balance = balance - 500.00 WHERE name = 'Fred Flinstone';
UPDATE 1
=# UPDATE accounts SET balance = balance + 500.00 WHERE name = 'Wilma Rubble';
UPDATE 0
=# COMMIT;
COMMIT
Note that there is no Betty in the database but the transaction still committed.
PostgreSQL even knows that there was no update: "UPDATE 0"
This is not considered a transaction failure by the database and thus does not roll back.

Verify:
=# select * from accounts;
 idpk |         name         | balance 
------+----------------------+---------
    2 | Wilma Flinstone      | 1000.00
    3 | Barney Rubble        | 1500.00
    1 | Fred Flinstone       |    0.00
(3 rows)
See transaction isolation to raise exceptions or to control conflicts with concurrent transations.
PostgreSQL transaction isolation

PostgreSQL Foreign Keys:
  • CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. This will allow one delete of a record from parent table to automatically spawn a delete from a child table, using only one delete statement. To avoid deleting the child record, one must first set the foreign key value of the parent record to NULL.
    • ON DELETE CASCADE
  • SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier.
    • ON DELETE SET NULL
  • NO ACTION: Do not delete record if there is a related foreign key value in the referenced table. One must delete the parent record first.
  • RESTRICT: Rejects the delete or update operation for the parent table.
Example:
CREATE TABLE employee (
    IDpk    SERIAL PRIMARY KEY,
    ssn     CHAR(11) NOT NULL, 
    name    CHAR(64),
    phone   CHAR(32),
    dept_fk INTEGER NOT NULL
);

CREATE TABLE department (
    IDpk        SERIAL PRIMARY KEY,
    dept_name   CHAR(64)
);

ALTER TABLE employee ADD FOREIGN KEY(dept_fk) REFERENCES department (IDpk) ON DELETE CASCADE;

INSERT INTO department (dept_name) VALUES ('accounting');
INSERT INTO department (dept_name) VALUES ('engineering');

INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-6789','Matt','1-800-555-1212', 1);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-7890','Mark','1-801-555-1212', 2);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-8901','Luke','1-802-555-1212', 1);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-9012','John','1-803-555-1212', 2);

Verify:
=#  SELECT * FROM employee;
 idpk |     ssn     |          name           |              phone               | dept_fk 
------+-------------+-------------------------+----------------------------------+---------
    1 | 123-45-6789 | Matt                    | 1-800-555-1212                   |       1
    2 | 123-45-7890 | Mark                    | 1-801-555-1212                   |       2
    3 | 123-45-8901 | Luke                    | 1-802-555-1212                   |       1
    4 | 123-45-9012 | John                    | 1-803-555-1212                   |       2
(4 rows)
=# SELECT * FROM department;
 idpk |             dept_name                             
------+------------------------------------
    1 | accounting                                                      
    2 | engineering                                                     
(2 rows)

Now we demonstrate the cascading delete:

=# DELETE FROM department WHERE dept_name='engineering';
DELETE 1
=# SELECT * FROM employee;
 idpk |     ssn     |          name           |              phone               | dept_fk 
------+-------------+-------------------------+----------------------------------+---------
    1 | 123-45-6789 | Matt                    | 1-800-555-1212                   |       1
    3 | 123-45-8901 | Luke                    | 1-802-555-1212                   |       1
(2 rows)
What does this mean? If the "engineering" department record is removed, then the employees in the group are fired and removed from the employee table. The other employees are retained.

Links:

Books:

PostgreSQL
by Korry Douglas, Susan Douglas
ISBN #0735712573, New Riders

Amazon.com
PostgreSQL Essential Reference
by Barry Stinson
ISBN #0735711216, New Riders

Amazon.com
PostgreSQL: Developer's Handbook
by Ewald Geschwinde, Hans-Juergen Schoenig, Hans-Jurgen Schonig
ISBN #0672322609, SAMS

Amazon.com
Practical PostgreSQL
John C. Worsley, Joshua D. Drake
ISBN #1565928466, O'Reilly

Amazon.com
Beginning Databases with PostgreSQL
by Richard Stones, Neil Matthew
ISBN #1861005156, Wrox Press Inc

Amazon.com