Database Systems Eero Kettunen

1
CREATE TABLE Statement

• CREATE TABLE statement creates the table
  structure with possible constraints.
• Basic syntax (more detailed description in the
  following examples)
• CREATE TABLE table_name (
• column_name datatype , ...
• , ltconstraintgt ...
• )

2
Basic Data Types

• Basic categories string and numeric
• Basic string types
• CHAR(n) string of length n
• VARCHAR(n) string of maximum length n
• Basic numeric types
• INT or INTEGER integers
• DECIMAL(n,d) decimal numbers (total n digits
  maximum, from which d digits are reserved for
  digits after the decimal point)

3
Basic Data Types

• String or numeric?
• phone number
• zip code
• product number
• capacity
• weight
• Numeric can be used in arithmetic operations
• String strings of numbers not used in arithmetic
  operations, leading zeros stay where they are

4
Basic Data Types

• Date and time datatypes are basicly numeric
  datatypes
• DATE not in T-SQL
• TIME not in T-SQL
• TIMESTAMP not in T-SQL
• Datetime datatypes in T-SQL
• DATETIME (1.1.1753 - 31.12.9999, accuracy 3.33
  milliseconds)
• SMALLDATETIME (1.1.1900 - 6.6.2079, accuracy 1
  minute)

5
Basic Data Types

• With datetime datatypes you can
• compare dates as dates rather than strings (!)
• calculate, e.g., this date 1 day
• substract dates this date - that date, whats
  the time between
• Experiment (getdate() returns current datetime)
• SELECT orderdate 1 FROM orders
• SELECT getdate() - orderdate FROM orders
• SELECT CAST(getdate() - orderdate AS INT) FROM
  orders

6
Default values

• CREATE TABLE Employee (
• empid CHAR(5),
• name VARCHAR(30) DEFAULT Smith,
• room VARCHAR(5) DEFAULT 10
• )

7
PRIMARY KEY Constraintonly one for each table

• CREATE TABLE Employee (
• empid CHAR(5) PRIMARY KEY,
• name VARCHAR(30),
• room VARCHAR(5)
• )
• Duplicate values are not allowed in empid column.

8
PRIMARY KEY Constraint

• Alternative syntax
• CREATE TABLE Employee (
• empid CHAR(5),
• name VARCHAR(30),
• room VARCHAR(5),
• PRIMARY KEY (empid) )
• Notice! More than one column in the primary key
• PRIMARY KEY (a, b)

9
UNIQUE Constraint(cf. secondary key)

• CREATE TABLE Employee (
• empid CHAR(5) PRIMARY KEY,
• name VARCHAR(30) UNIQUE,
• room VARCHAR(5)
• )
• Duplicate values are not allowed in name column,
  NULL values are allowed.

10
UNIQUE Constraint

• Alternative syntax
• CREATE TABLE Employee (
• empid CHAR(5) PRIMARY KEY,
• name VARCHAR(30),
• room VARCHAR(5),
• UNIQUE (name) )
• Notice! More than one column in the UNIQUE
• UNIQUE (a, b)
• -- is different from UNIQUE(a), UNIQUE(b) !

11
FOREIGN KEY Constraint(referential integrity
constraint)

• CREATE TABLE Room (
• room VARCHAR(5) PRIMARY KEY,
• capacity INTEGER DEFAULT 2 )
• CREATE TABLE Employee (
• empid CHAR(5) PRIMARY KEY,
• name VARCHAR(30),
• room VARCHAR(5) REFERENCES Room )
• -- also REFERENCES Room(room)

12
FOREIGN KEY Constraint

• Alternative (recommended) syntax
• CREATE TABLE Employee (
• empid CHAR(5) PRIMARY KEY,
• name VARCHAR(30),
• room VARCHAR(5),
• FOREIGN KEY (room) REFERENCES Room )
• Notice! More than one column in the foreign key
• FOREIGN KEY (a, b) REFERENCES Example(a, b)

13
Preserving Referential Integrity
FOREIGN KEY (room) REFERENCES Room
referencing table
referenced table
10
14

• Adding a new row to EMLOYEE or updating room to
  a new value in EMPLOYEE
• room must be found in ROOM.room

14
Preserving Referential Integrity
FOREIGN KEY (room) REFERENCES Room
referencing table
referenced table

• Adding a new row to ROOM
• no restrictions (with respect to this FK setting,
  but notice the PK setting in ROOM)

15
Preserving Referential Integrity
referencing table
referenced table
3
2
FOREIGN KEY (room) REFERENCES Room

• Updating room to a new value in ROOM
• if the old value is refered to in EMPLOYEE, the
  operation is prevented

16
Preserving Referential Integrity
referencing table
referenced table
2
3
2
FOREIGN KEY (room) REFERENCES Room ON UPDATE
CASCADE

• Updating room to a new value in ROOM
• update is cascaded automatically to referencing
  rows in EMPLOYEE

17
Preserving Referential Integrity
referencing table
referenced table
FOREIGN KEY (room) REFERENCES Room

• Deleting a row from ROOM
• if the row is refered to in EMPLOYEE, the
  operation is prevented

18
Preserving Referential Integrity
referencing table
referenced table
FOREIGN KEY (room) REFERENCES Room ON DELETE
CASCADE

• Deleting a row from ROOM
• deletion is cascaded automatically to referencing
  rows in EMPLOYEE

19
Preserving Referential Integrity

• Example
• FOREIGN KEY (room) REFERENCES Room ON DELETE
  CASCADE ON UPDATE CASCADE
• T-SQL (SQL Server 7.0)
• on update cascade / on delete cascadeoptions are
  not included in the foreign key clause syntax.

20
NOT NULL Constraint

• CREATE TABLE Employee (
• empid CHAR(5) PRIMARY KEY,
• name VARCHAR(30) NOT NULL,
• room VARCHAR(5) NOT NULL
• )
• Use if you dont want to allow NULL values in
  some column or columns.
• Included in the PRIMARY KEY constraint (not in
  all systems).

21
Recap

• CREATE TABLE Employee (
• empid CHAR(5),
• name VARCHAR(30) NOT NULL,
• room VARCHAR(5) NOT NULL DEFAULT 10,
• PRIMARY KEY (empid),
• UNIQUE (name),
• FOREIGN KEY (room) REFERENCES Room
• )

22
CHECK Constraint

• CHECK (ltboolean expressiongt)
• Such data manipulation is prevented which would
  result in truth value FALSE in the ltboolean
  expressiongt.
• CREATE TABLE Room (
• room VARCHAR(5) PRIMARY KEY,
• capacity INTEGER CHECK (capacity gt 1) )
• capacity can be NULL (capacity gt 1 evaluates
  UNKNOWN)

23
CHECK Constraint

• When used with the column definition, the
  constraint is checked when a value is updated in
  that column or a new row is inserted in the
  table.
• When put after the column definitions, the check
  is performed when a row is updated or inserted.
• CREATE TABLE Room (
• room VARCHAR(5) PRIMARY KEY,
• capacity INTEGER,
• CHECK (capacity gt 1 OR (capacity 0 AND room
  0)) )

24
CHECK Constraint

• Subqueries can be used, however, remember that
  the check is performed only when manipulating
  data on the table where the check constraint is!
• CREATE TABLE Room (
• room VARCHAR(5) PRIMARY KEY,
• capacity INTEGER,
• CHECK (capacity in (select c from caplist)) )

25
Naming Constraints

• CONSTRAINT constraint_name ltconstraintgt
• CREATE TABLE Employee (
• empid CHAR(5),
• name VARCHAR(30) CONSTRAINT nn_name NOT NULL,
• room VARCHAR(5),
• CONSTRAINT pk_empid PRIMARY KEY (empid),
• CONSTRAINT uq_name UNIQUE (name),
• CONSTRAINT fk_room FOREIGN KEY (room)
  REFERENCES Room
• )

26
Other Constraints

• ASSERTION constraints every data manipulation
  operation launches the check.
• CREATE ASSERTION alwayskeepthisCHECK ( (select
  max(capacity) from Room) (select max(c) from
  clist) )
• Not implemented in any product (as far as I
  know...)
• TRIGGERs can be used in implementing constraints
  (future topic in this course).

27
ALTER TABLE Statement(dont learn by heart)

• Syntaxes vary a lot in implementations.
• ALTER TABLE Employee ADD salary DECIMAL(5,2)
  DEFAULT 1000
• ALTER TABLE Employee DROP COLUMN salary
• ALTER TABLE Room ALTER capacitySET DEFAULT 1 or
  DROP DEFAULT -- T-SQL -- CREATE DEFAULT and
  DROP DEFAULT statements

28
ALTER TABLE Statement

• Quite useful with constraints!
• ALTER TABLE Employee DROP CONSTRAINT pk_empid
• ALTER TABLE EmployeeADD CONSTRAINT pk PRIMARY
  KEY (empid)
• ALTER TABLE Employee ADD CONSTRAINT roomcheck
  CHECK (room between 0 AND 599 )

29
DROP TABLE Statement

• DROP TABLE table_name
• Removes the table (if the foreign key constraints
  dont prevent this operation).