next up previous
Next: Embedded SQL Up: Data-Definition Language Previous: Domain Types in SQL

Schema definition in SQL

  1. An SQL relation is defined by:

     create table r ( tex2html_wrap_inline1854 

    integrity- tex2html_wrap_inline1856 ,

    ..., integrity- tex2html_wrap_inline1856 )

    where r is the relation name, tex2html_wrap_inline1730 is the name of an attribute, and tex2html_wrap_inline1864 is the domain of that attribute. The allowed integrity-constraints include

     primary key  tex2html_wrap_inline1866  



  2. Example.

     create table branch (

    bname char(15) not null

    bcity char(30)

    assets integer

    primary key (bname)

    check (assets >= 0))

  3. The values of primary key must be not null and unique. SQL-92 consider not null in primary key specification is redundant but SQL-89 requires to define it explicitly.
  4. Check creates type checking functionality which could be quite useful. E.g.,

     create table student (

    name char(15) not null

    student-id char(10) not null

    degree-level char(15) not null

    check (degree-level in (``Bachelors'', ``Masters'', ``Doctorate'')))

  5. Some checking (such as foreign-key constraints) could be costly, e.g.,

     check (bname in (select bname from branch))

  6. A newly loaded table is empty. The insert command can be used to load it, or use special bulk loader untilities.
  7. To remove a relation from the database, we can use the drop table command:

     drop table r

    This is not the same as

     delete r

    which retains the relation, but deletes all tuples in it.

  8. The alter table command can be used to add or drop attributes to an existing relation r:

     alter table r add A D 

    where A is the attribute and D is the domain to be added.

     alter table r drop A 

    where A is the attribute to be dropped.

Osmar Zaiane
Fri May 22 19:39:12 PDT 1998