Table Definition

Format for defining a simple table will use a statement of the following t pe:

CREATE TABLE table name (column definition list,
PRIMARY KEY (column name))

The use of a primary key is optional. However, without a primary key a table cannot be relational.

During processing of the statement table definition is checked for syntax. Defined properties are stored in the database schema and an empty table is constructed.

Example:

CREATE TABLE small_country 
(name CHAR (16),
Gdp DECIMAL (4, 1),
Cars INTEGER,
Population DECIMAL (6, 1),
PRIMARY KEY (name))

Any newly created table must have a different name to any existing table, otherwise the existing table data would be lost.

The same column names can be used in several tables.

Convention places primary key last but can be anywhere in the definition.

Data Types

There are numerous data types:

Character String Types

CHARACTER (len) - String values of fixed length. Default length is 1. Spaces added if string is not specified length. (max length is 32767)

CHARACTER VARYING (len) - String values of maximum length. Default length is 1. No spaces added if string is not specified length.

Exact Numeric Data Types

INTEGER - Positive or negative. (4 bytes - -2147483647 to +2147483647)

SMALLINT - Positive or negative with a precision (max number of digits) less than INTEGER. (2 bytes - -32767 to + 32767)

NUMERIC(p, scale) - Decimal with exactly p digits, scale of which is after the decimal point. (default precision 30, default scale 6)

DECIMAL(p, scale) - Decimal with at least p digits, scale of which is after the decimal point. (default precision 30, default scale 6)

Approximate Numeric Data Types

FLOAT(p) - Floating point number of at least p digits. (4 or 8 bytes dependent on precision p)

REAL - Floating point number. (4 bytes)

DOUBLE PRECISION - Floating point number of greater precision than REAL. (8 bytes)

Bit String Data Types

BIT(len) - Bit string of fixed length.

BIT VARYING(len) - Bit string of length up to len.

Date-time Data Types

DATE - Calendar date of year/month/day i.e. 2003/06/15 in that order. From 0001/01/01 to 9999/12/31.

TIME(p) - Time values of hour, minutes, seconds and fractions of a second (up to p decimal places) in that order.

TIMESTAMP(p) - Date-Time values of a date with time to p precision.

INTERVAL - Periods of time values of either year/month or day/time.

Nulls

To prevent nulls being entered in tables requires the use of NOT NULL:

CREATE TABLE small_country 
(name CHAR (16),
Gdp DECIMAL (4, 1) NOT NULL,
Cars INTEGER NOT NULL,
Population DECIMAL (6, 1) NOT NULL,
PRIMARY KEY (name))

Not forgetting that primary keys cannot be NULL.

Default Values

The DBMS can assign default values where none is specified. A specification for a default can be included for a column by using DEFAULT.

CREATE TABLE small_country 
(name CHAR (16),
Gdp DECIMAL (4, 1) NOT NULL,
Cars INTEGER NOT NULL DEFAULT 0,
Population DECIMAL (6, 1) NOT NULL,
PRIMARY KEY (name))

Any default value will be a constant value for the purpose.

Defaults can also be assigned as special variables (USER and CURRENT DATE). In such circumstances the value would be created at the time of insertion.

Domains

Domains can be defined in SQL:

CREATE DOMAIN credit_points AS SMALLINT
NOT NULL DEFAULT 60,

CREATE DOMAIN must include a data type but can optionally specify:

• NOT NULL
• Default values
• Constraints

Using the domain credit_points the column credit in the course table can be defined:

credit credit_points

This gives the column credit the values from the CREATE DOMAIN statement.

Removing a table

Table rows can be deleted using DELETE FROM. However the table remains even if all rows have been deleted. To remove a table requires a DROP TABLE statement:

DROP TABLE <table name>

This removes the table from the schema including all data within it. There are other consequences to DROP TABLE statements.

Modifying a table

Tables can be modified using the ALTER TABLE statement:

ALTER TABLE <table name> <alter action>

Modifications possible using the ALTER TABLE statement:

• add column
• drop column
• alter a column through setting its default value
• alter a column by dropping its default value
• add an integrity constraint
• drop an integrity constraint

To add a column:

ADD <column definition>

Simple ALTER TABLE statement:

ALTER TABLE mod_student
ADD phone_no VARCHAR(15)

Adding a column, without a default value, to a table that is not empty populates the column with NULL values, therefore a column that is added cannot be NOT NULL.

To populate with appropriate values requires an UPDATE statement:

UPDATE mod_student
SET phone_no = 
   (SELECT phone_no
    FROM telephone
    WHERE telephone.student_id =
      mod_student.student_id)

To alter a column default value:

ALTER <column name> SET <default definition>

e.g.

ALTER TABLE student
   ALTER counsellor_no SET DEFAULT '1941'

Non standard SQL as in SQL Anywhere:

ALTER TABLE student
   MODIFY counsellor_no DEFAULT '1941'      //No SET

Dropping a default:

ALTER <column name> DROP DEFAULT

e.g.

ALTER TABLE student
   ALTER counsellor_no DROP DEFAULT

SQL Anywhere does not support DROP DEFAULT. To overcome this SET DEFAULT to NULL.

Comments, suggestions, ideas to
Stuart Banner