How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
How to Use SQL with Microsoft Access
How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming

How to Use Referential Integrity Rules and the SQL MATCH Predicate

Referential integrity rules require that the values of a column or columns in one SQL table match the values of a column or columns in another table. You refer to the columns in the first table as the foreign key and the columns in the second table as the primary key or unique key.

For example, you may declare the column EmpDeptNo in an EMPLOYEE table as a foreign key that references the DeptNo column of a DEPT table. This matchup ensures that if you record an employee in the EMPLOYEE table as working in department 123, a row appears in the DEPT table where DeptNo is 123.

If the members of the foreign key/primary key pair both consist of a single column, the situation is pretty straightforward. However, the two keys can consist of multiple columns. The DeptNo value, for example, may be unique only within a Location; therefore, to uniquely identify a DEPT row, you must specify both a Location and a DeptNo.

If both the Boston and Tampa offices have a department 123, you need to identify the departments as (‘Boston’, ‘123’) and (‘Tampa’, ‘123’). In this case, the EMPLOYEE table needs two columns to identify a DEPT. Call those columns EmpLoc and EmpDeptNo. If an employee works in department 123 in Boston, the EmpLoc and EmpDeptNo values are ‘Boston’ and ‘123’. And the foreign-key declaration in the EMPLOYEE table looks like:

FOREIGN KEY (EmpLoc, EmpDeptNo)
 REFERENCES DEPT (Location, DeptNo)

Drawing valid conclusions from your data becomes immensely complicated if the data contains nulls. That’s because sometimes you want to treat such data one way, and sometimes you want to treat it another way. The UNIQUE, SIMPLE, PARTIAL, and FULL keywords specify different ways of treating data that contains nulls.

If your data does contain null values, drop out of speed-reading mode now and read the following list slowly and carefully. Each entry in the list given here presents a different situation with respect to null values — and tells how the MATCH predicate handles it.

Here are scenarios that illustrate the rules for dealing with null values and the MATCH predicate:

  • The values are both one way or the other. If neither of the values of EmpLoc and EmpDeptNo are null (or both are null), then the referential integrity rules are the same as for single-column keys with values that are null or not null.

  • One value is null and one isn’t. If, for example, EmpLoc is null and EmpDeptNo is not null — or EmpLoc is not null and EmpDeptNo is null — you need new rules. When implementing rules, if you insert or update the EMPLOYEE table with EmpLoc and EmpDeptNo values of (NULL, ‘123’) or (‘Boston’, NULL), you have six main alternatives: SIMPLE, PARTIAL, and FULL, each with or without the UNIQUE keyword.

  • The UNIQUE keyword is present. A matching row in the subquery result table must be unique in order for the predicate to evaluate to a True value.

  • Both components of the row value expression R are null. The MATCH predicate returns a True value regardless of the contents of the subquery result table being compared.

  • Neither component of the row value expression R is null, SIMPLE is specified, UNIQUE is not specified, and at least one row in the subquery result table matches R. The MATCH predicate returns a True value. Otherwise it returns a False value.

  • Neither component of the row value expression R is null, SIMPLE is specified, UNIQUE is specified, and at least one row in the subquery result table is both unique and matches R. The MATCH predicate returns a True value. Otherwise it returns a False value.

  • Any component of the row value expression R is null and SIMPLE is specified. The MATCH predicate returns a True value.

  • Any component of the row value expression R isn’t null, PARTIAL is specified, UNIQUE isn’t specified, and the non-null part of at least one row in the subquery result table matches R. The MATCH predicate returns a True value. Otherwise it returns a False value.

  • Any component of the row value expression R is non-null, PARTIAL is specified, UNIQUE is specified, and the non-null parts of R match the non-null parts of at least one unique row in the subquery result table. The MATCH predicate returns a True value. Otherwise it returns a False value.

  • Neither component of the row value expression R is null, FULL is specified, UNIQUE is not specified, and at least one row in the subquery result table matches R. The MATCH predicate returns a True value. Otherwise it returns a False value.

  • Neither component of the row value expression R is null, FULL is specified, UNIQUE is specified, and at least one row in the subquery result table is both unique and matches R. The MATCH predicate returns a True value. Otherwise it returns a False value.

  • Any component of the row value expression R is null, and FULL is specified. The MATCH predicate returns a False value.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Retrieve SQL Data with PHP for HTML5 and CSS3 Programming
How to Protect Data with SQL Transactions
10 Common SQL Mistakes
How to Use UPDATE, DELETE, and INSERT SQL Statements
Advertisement

Inside Dummies.com