Data Definition Language

To create a table schema in SQL, you must use the create table command as follows:

      create table Supplier (
        sId integer primary key,
        name char(40) not null,
        city char(30) not null,
        address char(100) not null
      );
    

This will create the Supplier table schema (also known as relation schema); this table will later hold values we pass into it. An attribute of the relation schema is specified by giving a name, a data type and a list of constraints.

(By default, tables will be added to the public database schema, but you can also define more database schemas to segment your database into groups.)

Data types can be: integer, numeric, char, date, and many more.

• integer: decimal number
• numeric(LEN,DGT): specify a floating point number of maximum total length LEN and with a maximum of DGT digits right of the decimal point.
• char(LEN): a string of LEN characters. When using char, the string will be padded with spaces, as opposed to varchar(LEN), which will just store the shorter string.
• date: an ISO-8601 date string. (If you want to store a time and date, use the timestamp type.)

Common constraints are: primary key, references, not null, check, default.

• primary key: Designate this column as the primary key of the table: it must be unique and must not be null and it should identify a row of the table without ambiguity.
  You can also set a tuple of columns as the primary key. This is where you use table constraints:

          create table SP (
            sId integer references Supplier(sId),
            pId integer references Part(pId),
            primary key (sId,pId)
          );
        

• references: specifies that this column refers to a column in a different table. This is most useful in connecting tables, where you extract the relevant parts from two tables (see above).
• not null: the column must never hold the null value (null means "unknown"); sometimes it is useful to not enter all information right away, therefore only essential fields should have the not null constraint.
• check: check that any value in this column satisfies the given expression. For example: age integer not null check (age>0).
• default: specify a default value if none is given (i.e. if null is inserted into the column).

The created table is empty at first, so you have to insert some rows into it before you can operate on the data.