Codd's Twelve Rules - Rule 10 - Integrity Independence
|
Rule 10
|
Integrity Independence
|
|
Rule
|
Integrity
constraints specific to a particular relational database must be definable in
the relational data sublanguage and storable in the catalog, not in the
application programs.
A
minimum of the following two integrity constraints must be supported:
1. Entity integrity:
No component of a primary key is allowed to have a null value. That is, no
records can have NULL values in its Primary Key attribute.
2. Relational integrity:
For each distinct non-null foreign key value in a relational database, there
must exist a matching primary key value from the same domain. In other
words, if a foreign key cannot have null values as its component then it must
refer a matching primary key value with the same set of permitted values to
accept any new records.
|
|
Description
|
This
rule insists that the declaration of integrity constraints must be part of
the language that is used to define the database structure. Also, these
integrity constraints should be stored as part of Data dictionary.
For
example, if you define a new table in Oracle using SQL, then SQL must provide
facilities to define the integrity constraints. These integrity constraints
are stored as part of SYSTEM tablespace.
|
|
Example
(Green color - primary key, red color - foreign key)
|
CREATE TABLE Emp(Eno CHAR(5) PRIMARY KEY, Ename VARCHAR(25), Phone NUMBER(10), dno NUMBER(3), FOREIGN KEY dno REFERENCES Dept(dno));
In this
definition PRIMARY KEY means UNIQUE + NOT NULL, and we have defined
PRIMARY KEY as part of table definition itself using SQL.
Secondly,
if we have attribute dno as a NON-NULL foreign key, then there should be a table
definition like the one follows and dno should refer the PRIMARY KEY;
CREATE
TABLE Dept(Dno NUMBER(3) PRIMARY KEY, Dname VARCHAR(35), Dlocation
VARCHAR(30), Phone NUMBER(10));
|
|
Some DBMS that fulfills this property
|
SQL Server, Oracle,
MySQL, IBM DB2
|