SQL Data Definition Language I

Tom Kelliher, CS 318

Feb. 1, 2002



Recommendation: Use a special section of your notebook to collect SQL statement details. It will grow in terms of number of statements and aspects of statements; plan accordingly.


Have a look through the PostgreSQL Documentation on the course web site to familiarize yourself with PostgreSQL.

From Last Time

Intro to relational data model.


  1. SQL, psql info.

  2. SQL DDL: creating tables, primary and candidate keys, constraints, and foreign keys.

  3. Exercise.

Coming Up


SQL Variants, Useful psql Meta-Commands

  1. SQL-92.

    Most DBMSs support most of this, with extensions.

  2. SQL:99.

    Some DBMSs support parts of this.

  3. SQL 3.

    Some DBMSs support parts of this?

Some useful psql meta-commands:

  1. \i <filename> --- Read and execute a list of SQL commands in the text file

  2. \d <table> --- List <table>'s description.

  3. \l --- List available databases on the system.

  4. \dS --- Display list of system tables. Example queries on two system tables:
    SELECT * FROM pg_user;
    SELECT * FROM pg_tables;

  5. \dp --- List permissions in current database.

  6. \dt --- List tables in current database.

Run \? in psql for the entire list and see the man page for psql.


What we need to do:

  1. Create tables.

  2. Create assertions within tables.
    1. Domain constraints: NOT NULL, DEFAULT.

    2. Semantic constraints (cannot order a negative number of widgets).

    3. Keys: primary, candidate, and foreign.

  3. Create assertions between (external to) tables.

  4. Create data types (or restrict existing types).

Creating Tables

   CrsCode        CHAR(6),
   DeptId         CHAR(4),
   CrsName        CHAR(20),
   Descr          CHAR(100) );  -- Use text?

Some data types in PostgreSQL:

  1. Numeric:
    1. INTEGER --- four bytes. Also SMALLINT and BIGINT.

    2. REAL --- single precision. Also DOUBLE PRECISION.

    3. SERIAL --- unique serial number. Some caveats --- see manual.

  2. Character:
    1. CHAR(n) --- fixed length string of n characters.

    2. VARCHAR(n) --- variable length string of at most n characters.

    3. TEXT --- variable length string without limit. Not SQL standard.

  3. Others: Monetary, date/time, boolean, etc.

    See manual.

(Refer to the online documentation for details.

Primary and Candidate Keys

   CrsCode        CHAR(6),
   DeptId         CHAR(4),
   CrsName        CHAR(20),
   Descr          CHAR(100),
   PRIMARY KEY (CrsCode),
   UNIQUE (DeptId, CrsName) );
  1. One PRIMARY KEY. Table usually indexed on this.

  2. One UNIQUE constraint for each remaining candidate key.

Specifying Constraints

  1. Domain and semantic constraint examples:
    CREATE TABLE Course (
       CrsCode        CHAR(6) NOT NULL,
       Limit          INTEGER DEFAULT '10',
       Division       CHAR(5),
       DeptId         CHAR(4),
       CrsName        CHAR(20),
       Descr          CHAR(100),
       CHECK (Limit > 0 AND Limit <= (SELECT MAX(Seats) from Room)),
       CHECK (Division IN ('Upper', 'Lower')) );
    1. Use for intra-table constraints.

    2. Chicken and egg problem: use ALTER TABLE to add checks once tables are populated.

  2. Assertion example:
    CREATE ASSERTION CoursesShallNotBeEmpty
                 SELECT * FROM Teaching Te
                 WHERE NOT EXISTS (
                    SELECT * FROM Transcript Tr
                    WHERE Te.CrsCode = Tr.CrsCode
                       AND Te.Semester = Tr.Semester )));
    1. NOT EXISTS: empty set test. EXISTS?

      Briefly explain the correlated sub-query: for loop similarity.

    2. Note: this will fail before the registration period!

    3. Use for inter-table constraints.

    4. Tables must first exist!

    5. PostgreSQL does not have assertions! Use triggers?

Foreign Keys

   ProfId         INTEGER,
   CrsCode        CHAR(6),
   Semester       CHAR(6),
   PRIMARY KEY (CrsCode, Semester),
   FOREIGN KEY (ProfID) REFERENCES Professor (Id) );
  1. In PostgreSQL the referenced key must be a primary key!


See handout.

Thomas P. Kelliher
Wed Jan 30 10:29:14 EST 2002
Tom Kelliher