Schema definition in SQL

1. An SQL relation is defined by:

    create table r (  
   
   		 		 		 		 		 integrity-   ,
   
   		 		 		 		 		 ..., integrity-   )
   
   

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

    primary key    
   

   and

    check(P)
   
   

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.