Title: SQL DDL
1SQL DDL
2DDL 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
3Create 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.
4Create Table Syntax
- CREATE TABLE table_name (
column_name Use letters digits Avoid special
symbols First Name
data_type
attributes ,
5Create Table Syntax
- CREATE TABLE table_name (
column_name
data_type Char(X) Varchar(X) Int Decimal(X,Y) Mon
ey Datetime
attributes ,
6Create 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)
7Sample Database
Note Parents before children
8Employee 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)?
9Employee 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.
10Employee 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.
11Employee 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.
12Employee 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.
13Employee 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
14Employee 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, ...
15Employee 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, ...
16Employee 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')
17Employee 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) )
18Check 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 )
19Check 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 )
20Paycheck Table - Foreign Key
- CREATE TABLE tblPaycheck (
- PayDate DateTime Not Null ,
- EID Int Not Null
- References tblEmployee(EmployeeID)
21Paycheck 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)
22Paycheck 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.
23Paycheck 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.
24Paycheck 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.
25Paycheck 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) )
26Create 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)
27Create 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.
28Create 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.
29Create 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.
30Create 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)
31Secondary Indices
- CREATE INDEX idxNameSort
- ON tblEmployee ( LastName , FirstName )
- CREATE INDEX idxSalarySort
- ON tblEmployee ( PayRate DESC )
- CREATE INDEX idxHighPay
- ON tblPaycheck ( PayDate, NetPay DESC )
32Alter 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
33Employee 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
34Drop Syntax
- DROP TABLE table_name
- DROP INDEX table_name.index_name
- DROP TABLE tblPaycheck
- DROP INDEX tblEmployee.idxSalarySort