SQL Part 3

SQL Part 3 – Integrity Constraints

Learning Outcomes:

Describe the basic concepts of relational databases.

Key (such as primary key, foreign key and candidate key), and Integrity (such as entity integrity, referential integrity and domain integrity).

Relevant past paper:

DSE ICT Elec A(SP-2016): SP 1abf. PP 1aii,b,2e,4aiii. 2012 1a,ci. 2013 2a,bi,d. 2014 1b, 2d, 4aiii,c. 2015 3b. 2016 3c, di.

AS CA(2000-2013): 2001 2. 2003 2a. 2004 1a. 2005 6a,cii. 2006 6a,b,c,di. 2007 10g. 2008 5a. 2009 2a. 2011 3ac. 2012 1ab, 9ai. 2013 5b, 9ei.

AL CS 2005 P1 8b.

Key

• A key is: one or more selected attributes used to identify a record.
• A candidate key is: one or more attributes used collectively that can be used to uniquely identify a record in a table. A table can have multiple candidate keys.
• A composite key is: a candidate key. At the same time, it contains 2 or more attributes used collectively.
• A primary key is: the candidate key that is chosen (by database designer) to uniquely identify a record in a table. A table can only have 1 primary key.
• A foreign key is: not a candidate key of a table but is a primary key of another table. It can be a null value.

Commonly, you are required to identify the primary keys and foreign keys of different tables. 

Tips: Very often in 1 of the tables, it contains 2 foreign keys which is used collectively as primary key.

Integrity constraints: Entity integrity, Referential integrity, Domain integrity

Entity integrity – primary key

It ensures the values entered into primary key is unique and not null.

In SQL, it can be defined while creating table.

• CREATE TABLE <table name>
  (<field name 1> <data type 1> PRIMARY KEY,
  <field name 2> <data type 2>,…);

For composite key:

• CREATE TABLE <table name>
  (<field name 1> <data type 1>,
  <field name 2> <data type 2>,
  …,
  PRIMARY KEY(<field name 1>, <field name 2>));

Referential integrity – foreign key

It ensures data consistency between coupled table. Having foreign key improves referential integrity so that non-existent key cannot be entered. It helps to uniquely identify a record in another table.

Suppose I have a 2 tables:

ABC:

Tableone (primary key)
003

XYZ:

Tabletwo (primary key)		Tableone
A01		003

1. Correct description: Tableone in table XYZ is a foreign key to table ABC.
2. To delete the record 003 in ABC, the record A01 in XYZ must be deleted first.
   It is because if the record 003 in ABC is to be deleted first, the record A01 in XYZ with Tableone=’003’ cannot refer to the corresponding record in ABC as Tableone in XYZ is a foreign key to table ABC. Record 003 in ABC could not be deleted first as it violates referential integrity.

In SQL, it can be defined while creating table or changing table structure subsequently.

• CREATE TABLE XYZ
  (Tabletwo CHAR(3) PRIMARY KEY,
  …,
  Tableone CHAR(3),
  FOREIGN KEY (Tableone) REFERENCES ABC(Tableone));

• ALTER TABLE XYZ
  ADD FOREIGN KEY (Tableone) REFERENCES ABC(Tableone);

  

Domain integrity – data type, data size

It ensures the new data entered is within the data values allowed. i.e. only certain values are allowed.