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.
FLOAT/REAL: floating point numbers
TIMESTAMP: date and time values
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.
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
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.
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.
create table Student ( ID integer, name varchar(25), primary key (ID) );
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.
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 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.
create table People ( SIN integer primary key, name text, OHIP text unique ); create table Volunteers ( email text primary key, OHIPnum text references People(OHIP) );
Column-based check constraints
Defined with a single column and constrain its values in every row.
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
Check conditions are not as picky as
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.
( constraint <name> check (<condition>,...) )
This will product more useful error messages if the constraint is violated.
Check constraints cannot express complex constraints across tables. But assertions across tables are expensive and are not supported by PostgreSQL.
Define responses after certain triggering events.
create trigger OnUpdate before insert on Data for each row execute procedure ProcedureName();
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).
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
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.
create table Took ( ... foreign key (sID) references Student on delete cascade ... );