Back

SQL - Creating relations

When we talked about datatypes in SQL, we said that we can directly associate an attribute with a predefined data type.  Another way of specifying a domain is using the SQL create domain statement:

create domain NAME as char(15);
This creates a domain and assigns a name to it.  Now we can use that domain when defining a new attribute, instead of using a predefined SQL type.  A useful consequence of using this kind of approach is the fact that, if, for example, we need the NAME domain to be extended to char(20), we only need to make one change - at the declaration of the domain.

 

In SQL, permanent relations - tables - are created using the create table statement.  The statement must include the specification of the table name and attribute names and domains.  Optionally, the statement can also include constraint specifications, such as primary or foreign key constraints.  A typical form of the create table statement is:

create table table_name
(
   column_name1 datatype [constraint for column1]
   column_name2 datatype [constraint for column2]
   column_name3 datatype [constraint for column3]
   ...
   [integrity constraint 1],
   [integrity constraint 2]
   ...
);
where table_name is the name of the relation (table) being created, column_name1 the name of an attribute, and datatype a predefined datatype (with optional specification of precision, scale value or length) or the name of a declared domain.  Constraints for columns are optional and most often contain the not null specification, which means that null values are not allowed.

 

Other constraints regarding the value of columns (attributes) can contain specifications of uniqueness, referential integrity (referencing another table's [primary] key) or other limitations which can be specified using the check constraint.  Typical integrity constraints are:

  • Uniqueness specification with the following form:
    primary key (column_name1, column_name2, ...)
    This means that the values on the attributes which constitute the primary key must be unique (they are by default, and must always be, not null).

  • Referential integrity (foreign key specification):
    foreign key (list of referencing columns) references name_of_ref_table(list of referenced columns)
    where list of referencing columns is in the form of column_name1, column_name2, ..., and the listed columns are the attributes constituting the foreign key.  The list of referenced columns is a list of attributes from the name_of_ref_table table referenced by the foreign key.

  • Check constraint, in the form of:
    check(P)
    where P is a predicate/condition which has to be satisfied for each entity of the table.

 

Now we will see a couple of examples - statements for creating relations of the uni database we modeled earlier:

create table faculty
(
   FacName char(30) not null, 
   Address char(50),
   primary key(FacName)
);

create table phone
(
   FacName char(30) not null,
   FacPhone char(10) not null,
   primary key(FacName, FacPhone),
   foreign key(FacName) references faculty(FacName)
);

create table person
(
   PersonID long not null,
   Name char(50) not null,
   DateOfBirth date not null,
   Address varchar(50),
   primary key(PersonID)
);
In the following articles we will cover authorization and privileges, editing/deleting relations and attributes, and start learning about basic SQL queries.