SQL DDL

1
SQL DDL
2
DDL Data Definition Language

• CREATE TABLE
• CREATE INDEX
• ALTER TABLE
• DROP TABLE
• DROP INDEX
• There are about 20 variations of the CREATE,
  ALTER and DROP commands

3
Create Table Syntax

• CREATE TABLE table_name (

column_name_1 column_name_2 column_name_N table_at
tributes )
data_type data_type data_type
attributes , attributes , ... attributes ,
Note SQL is case insensitive and spacing
insensitive, but very sensitive to structure,
keywords, delimiters, etc.
4
Create Table Syntax

• CREATE TABLE table_name (

column_name Use letters digits Avoid special
symbols First Name
data_type
attributes ,
5
Create Table Syntax

• CREATE TABLE table_name (

column_name
data_type Char(X) Varchar(X) Int Decimal(X,Y) Mon
ey Datetime
attributes ,
6
Create Table Syntax

• CREATE TABLE table_name (

column_name
data_type
attributes , Null or Not Null Default
Values Primary Key References (i.e.,
Foreign Key) Check Constraint (i.e.,
Validation Rule)
7
Sample Database
Note Parents before children
8
Employee Table

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null

What is the difference between Char(20) and
Varchar(20)? What is the difference between
Varchar(20) and Varchar(MAX)?
9
Employee Table - Simple PK

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Varchar(10) Primary Key

By default, primary keys are clustered.
10
Employee Table - Simple PK

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Int Primary Key

By default, primary keys are clustered.
11
Employee Table - Simple PK

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Int Primary Key
  Clustered

By default, primary keys are clustered.
12
Employee Table - Simple PK

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Int Primary Key
  Nonclustered

By default, primary keys are clustered. You can
request a non-clustered PK if you want to use
clustering on one of your secondary indices.
13
Employee Table - Simple PK

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Int Primary Key

User-supplied unique integer
14
Employee Table - Autonumber PK

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Int Identity Primary
  Key

DB-supplied unique integer 1, 2, 3, ...
15
Employee Table - Autonumber PK

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Int Identity(1000,10)
  Primary Key

DB-supplied unique integer 1000, 1010, 1020, ...
16
Employee Table - Check Constraint

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Int Identity Primary
  Key ,
• PayType Char(1) Not Null Default
  'H'
• Check (PayType 'H' Or PayType
  'F')

17
Employee Table - Check Constraint

• CREATE TABLE tblEmployee (
• FirstName Varchar(20) Not Null ,
• MiddleName Varchar(20) Null ,
• LastName Varchar(30) Not Null ,
• EmployeeID Int Identity Primary
  Key ,
• PayType Char(1) Not Null Default
  'H'
• Check (PayType 'H' Or PayType
  'F') ,
• PayRate Money Not Null
• Check (PayRate gt 0) )

18
Check Number of Characters

• LEN(X) counts number of characters in X
• LastName Varchar(30) Not Null
• Check ( LEN(LastName)gt2 ) ,
• Zip Char(5) Null
• Check ( Len(Zip)5 OR Zip IS NULL )

19
Check Number of Characters

• LEN(X) counts number of characters in X
• LastName Varchar(30) Not Null
• Check ( LEN(LastName)gt2 ) ,
• Zip Varchar(10) Null
• Check ( Len(Zip)5 OR LEN(Zip)10
• OR Zip IS NULL )

20
Paycheck Table - Foreign Key

• CREATE TABLE tblPaycheck (
• PayDate DateTime Not Null ,
• EID Int Not Null
• References tblEmployee(EmployeeID)

21
Paycheck Table - Simple Check

• CREATE TABLE tblPaycheck (
• PayDate DateTime Not Null ,
• EID Int Not Null
• References tblEmployee(EmployeeID) ,
• NetPay Money Not Null Check (NetPay gt 0)
  ,
• GrossPay Money Not Null Check (GrossPay gt 0)

22
Paycheck Table - Composite PK

• CREATE TABLE tblPaycheck (
• PayDate DateTime Not Null ,
• EID Int Not Null
• References tblEmployee(EmployeeID) ,
• NetPay Money Not Null Check (NetPay gt 0)
  ,
• GrossPay Money Not Null Check (GrossPay gt 0)
  ,
• Primary Key ( PayDate , EID )

By default, primary keys are clustered.
23
Paycheck Table - Composite PK

• CREATE TABLE tblPaycheck (
• PayDate DateTime Not Null ,
• EID Int Not Null
• References tblEmployee(EmployeeID) ,
• NetPay Money Not Null Check (NetPay gt 0)
  ,
• GrossPay Money Not Null Check (GrossPay gt 0)
  ,
• Primary Key Clustered ( PayDate , EID )

By default, primary keys are clustered.
24
Paycheck Table - Composite PK

• CREATE TABLE tblPaycheck (
• PayDate DateTime Not Null ,
• EID Int Not Null
• References tblEmployee(EmployeeID) ,
• NetPay Money Not Null Check (NetPay gt 0)
  ,
• GrossPay Money Not Null Check (GrossPay gt 0)
  ,
• Primary Key Nonclustered ( PayDate , EID )

You can request a non-clustered PK if you want to
use clustering on one of your secondary indices.
25
Paycheck Table - Composite Check

• CREATE TABLE tblPaycheck (
• PayDate DateTime Not Null ,
• EID Int Not Null
• References tblEmployee(EmployeeID) ,
• NetPay Money Not Null Check (NetPay gt 0)
  ,
• GrossPay Money Not Null Check (GrossPay gt 0)
  ,
• Primary Key ( PayDate , EID ) ,
• Check (NetPay lt GrossPay) )

26
Create Index Syntax

• CREATE INDEX index_name
• ON table_name (
• column_name_1 order ,
• column_name_2 order ,
• column_name_N order )
• order ASC or DESC (ASC is default)

27
Create Index Syntax

• CREATE NONCLUSTERED INDEX index_name
• ON table_name (
• column_name_1 order ,
• column_name_2 order ,
• column_name_N order )
• By default, secondary indices are non-clustered.

28
Create Index Syntax

• CREATE CLUSTERED INDEX index_name
• ON table_name (
• column_name_1 order ,
• column_name_2 order ,
• column_name_N order )
• You can request a clustered secondary index if
  the
• table does not already have a clustered index.

29
Create Index Syntax

• CREATE INDEX index_name
• ON table_name (
• column_name_1 order ,
• column_name_2 order ,
• column_name_N order )
• By default, secondary indices allow duplicate
  values.

30
Create Index Syntax

• CREATE UNIQUE INDEX index_name
• ON table_name (
• column_name_1 order ,
• column_name_2 order ,
• column_name_N order )
• You can create a unique index, if needed.
• This would allow 11 relationships to be handled.
  (FK)

31
Secondary Indices

• CREATE INDEX idxNameSort
• ON tblEmployee ( LastName , FirstName )
• CREATE INDEX idxSalarySort
• ON tblEmployee ( PayRate DESC )
• CREATE INDEX idxHighPay
• ON tblPaycheck ( PayDate, NetPay DESC )

32
Alter Table Syntax

• ALTER TABLE table_name
• ADD column_name data_type attribute
• DROP COLUMN column_name
• ALTER COLUMN column_name new_data_type
• new_attribute

33
Employee Table Revisions

• ALTER TABLE tblEmployee
• ADD Gender Char(1) Not Null Default 'M'
  
• Check (Gender 'F' Or Gender 'M' )
• ALTER TABLE tblEmployee
• DROP COLUMN MiddleName

34
Drop Syntax

• DROP TABLE table_name
• DROP INDEX table_name.index_name
• DROP TABLE tblPaycheck
• DROP INDEX tblEmployee.idxSalarySort