next up previous
Next: Assertions Up: Referential Integrity Previous: Database Modification

Referential Integrity in SQL

  1. An addition to the original standard allows specification of primary and candidate keys and foreign keys as part of the create table command:

  2. An example illustrates several features mentioned so far:

     create table  customer
    

    (cname char(20) not null,

    street char(30),

    city char(30),

    primary key (cname))

     create table  branch 
    

    (bname char(15) not null,

    bcity char(30),

    assets integer,

    primary key (bname)

    check (assets >= 0))

     create table  account 
    

    (account# char(10) not null,

    (bname char(15),

    balance integer,

    primary key (account#)

    foreign key (bname) references branch,

    check (balance >= 0))

     create table  depositor 
    

    (cname char(20) not null,

    account# char(10) not null,

    primary key (cname, account#)

    foreign key (cname) references customer,

    foreign key (account#) references account)

  3. Notes on foreign keys:
  4. Given and complexity and arbitrary nature of the way constraints in SQL behave with null values, it is the best to ensure that all columns of unique and foreign key specifications are declared to be nonnull.

next up previous
Next: Assertions Up: Referential Integrity Previous: Database Modification

Osmar Zaiane
Tue Jun 9 15:12:55 PDT 1998