Referential integrity

Introduction to Relational Databases in SQL

Timo Grossenbacher

Data Journalist

Referential integrity

  • A record referencing another table must refer to an existing record in that table
  • Specified between two tables
  • Enforced through foreign keys
Introduction to Relational Databases in SQL

Referential integrity violations

Referential integrity from table A to table B is violated...

  • ...if a record in table B that is referenced from a record in table A is deleted.
  • ...if a record in table A referencing a non-existing record from table B is inserted.
  • Foreign keys prevent violations!
Introduction to Relational Databases in SQL

Dealing with violations

CREATE TABLE a (
 id integer PRIMARY KEY,
 column_a varchar(64), 
 ...,
 b_id integer REFERENCES b (id) ON DELETE NO ACTION
);

CREATE TABLE a (
 id integer PRIMARY KEY,
 column_a varchar(64), 
 ...,
 b_id integer REFERENCES b (id) ON DELETE CASCADE
);
Introduction to Relational Databases in SQL

Dealing with violations, contd.

ON DELETE...

  • ...NO ACTION: Throw an error
  • ...CASCADE: Delete all referencing records
  • ...RESTRICT: Throw an error
  • ...SET NULL: Set the referencing column to NULL
  • ...SET DEFAULT: Set the referencing column to its default value
Introduction to Relational Databases in SQL

Let's look at some examples!

Introduction to Relational Databases in SQL

Preparing Video For Download...