On SQL DDL

,

Types

Table attributes have types. Here are some built-in types:

  • CHAR(n): fixed-length string of n characters, padded with blanks if necessary.
  • VARCHAR(n): variable length string of up to n characters.
  • TEST: variable-length, unlimited.
  • INT/INTEGER: integer
  • FLOAT/REAL: floating point numbers
  • BOOLEAN
  • DATE, TIME, TIMESTAMP: date and time values

User-Defined Types

We can define types in terms of a built-in type. It allows us to add additional constraints to the constraints already defined by the built-in types. We define user-defined types using the create domain syntax.

1
2
3
create domain Grade as int
	default null
	check (value >= 0 and value <= 100);

This declares a type Grade that’s “inherited” from INT with the additional requirement that the values of the type Grade can be null and must be between 0 and 100.

As shown in the example, we can specify default value for a specific type.

Type Default v.s. Attribute Default

  • Type default: default value for every attribute defined to be of that type
  • Attribute default: for that one attribute in that one table

Key Constraints

Declaring that a set of one or more attribues are the primary key for a relation means:

  • They form a key
  • Their values will never be null
    Declaring primary keys prevent redundant data and differentiate between duplicate data.

Each table must have 0 or 1 primary key! Cannot declare moe than one primary key! If an attribute is declared a primary key, it cannot be null and must be unique.

The database management system uses the primary key for indexing and search optimization.

A table can have multiple keys but at most one primary keys. The primary key keyword declares an attribute the primary key.

1
2
3
4
create table Student (
	ID integer primary key,
	name varchar(25)
);

Instead of declaring an attribute the primary key when defining the attribute, we can also decalre it at the end of the schema definition.

1
2
3
4
5
create table Student (
	ID integer,
	name varchar(25),
	primary key (ID)
);

Uniqueness Constraints

Declaring a set of one or more attributes unique means:

  • They form a queue
  • Their values can be NULL (note the difference here between a unique attribute and a primary key)
    Also, unlike a primary key, there can be multiple unique attributes.
1
2
3
4
create table Student (
	ID integer unique,
	name varchar(25)
);

There’s nothing preventing us from inserting a row with multiple NULL values for the unique attributes.

Foreign Key Constraints

#foreign_key

Foreign key references the primary key or unique attributes of another table. The attributes being referenced as foreign keys must be either the primary key, or unique in the table being referenced from.

1
2
3
4
5
6
7
8
9
create table People (  
	SIN integer primary key,  
	name text,  
	OHIP text unique
);  
create table Volunteers (  
	email text primary key,  
	OHIPnum text references People(OHIP)
);

Check Constraints

Column-based check constraints

Defined with a single column and constrain its values in every row.

1
2
3
4
5
create table Application (
	sID integer,
	previousAppointments integer
		check (previousAppointments >= 0)
);

The condition can be anything that could go in a WHERE clause, including subquery (although the use of subqueries is not supported by psql).

The constraints are checked only when a tuple is inserted into the relation or when some values of that attribute is updated.

Row-based check constraints

Defined as a separate element of the table schema, so it can refer to any column of the table. Again, the condition can be anything that could go into a WHERE clause.

Check conditions are not as picky as WHERE: WHERE fails if there is NULL is involved (if one of the condition evaluates to undefined due to a NULL value) but CHECK will permit it. This underscores the importance of having NOT NULL conditions when needed.

We can name constraints.

1
2
3
4
(
	constraint <name> 
		check (<condition>,...)
)

This will product more useful error messages if the constraint is violated.

Cross-table constraints

Check constraints cannot express complex constraints across tables. But assertions across tables are expensive and are not supported by PostgreSQL.

Triggers

Define responses after certain triggering events.

1
2
3
4
create trigger OnUpdate
before insert on Data
for each row
execute procedure ProcedureName();

Reaction Policies

In a schema with cross-table references, we might want to define some reaction policies when some operation is performed on a certain table that can affect other tables in the schema (for example, if R references some row in S, and the row being referenced is removed from S after some operation. A simple CHECK will not be enough because CHECK only runs after insertion or deletion, but will not react to events happened in a different table).

Policies:

  • cascade: propagate the change to the reference table
  • set null: set the referring attribute(s) to null
  • restrict: disallow the deletion/update

Events we can react to:

  • on delete
  • on update
  • on delete on update

Suppose table R refers to table S. We can define “fixes” that propogate changes backwards from S to R, but we cannot define fixes that propogate changes forward from R to S.

Syntax:

1
2
3
4
5
6
create table Took (
	...
	foreign key (sID) references Student
		on delete cascade
	...
);