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
domain to be extended to
, 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]
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
specification, which means that
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,
- 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:
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,
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,
In the following articles we will cover authorization and privileges, editing/deleting relations and attributes, and start learning about basic SQL queries.