Data Definition Language

1
Data Definition Language
2
DDL

• Create Table
• Alter Table
• Create Index

3
Table Creation

• A database is built one table at a time
• Table creation include defining
• Table Name
• Column Name
• Column Data Type
• Column Size
• Column Constraints
• Primary Key
• Foreign Keys

4
Create Table Syntax
Create Table table-name ( column definition1,
column definition n, Constraint
defintion)
5
Defining the table
Student will be the name of the table to be
created

• CREATE TABLE student

This part is required
Next we need to add column definitions for every
attribute in the table
6
Column Definition

• Column-name data-type column-options
• Column name
• 1-31 characters, alpha_numeric
• Must be unique within table

7
Add Column Information

• CREATE TABLE student
• (StudentNo

Column Name
8
Data Types

• Character (string) types
• Char
• Varchar
• Numeric
• Integer
• Float
• Real
• Numeric

• Date/Time
• Date
• Time
• Timestamp

9
Add Data Type

• CREATE TABLE student
• (StudentNo Integer

Valid Data Type
10
Column Options - Null

• Whether a column allows nulls specifies whether
  you are allowed to create a row that has no value
  for this column. A Null is different than a 0 or
  spaces
• Null Nulls are allowed. This is the default
• Not Null Nulls are not allowed. You must
  specify a value when a row is created.

11
Column Options - Default

• If no value is supplied for column on insert of a
  row, defaults will supply that value
• Can be used with the Null/Not Null option
• With Default default-option

12
Default Options

• Default option
• Data type default
• Default
• Constant Value
• Default(abc)
• Special Register
• User
• Default user
• Current Timestamp
• Default current_timestamp

13
Adding Column Options

• CREATE TABLE student
• (StudentNo Integer Not Null

We have added a not null option. A value must
always be provided for this column
14
More Column Options

• Constraint constraint-name options
• Primary Key
• Unique
• References Clause
• Check (check-condition)

15
Column Options

• Constraint constraint-name is optional, but
  recommended. If you do not define a
  constraint-name, the system will generate a name
• Many of these options can be put on the same line
  as the column making it a column constraint, or
  they can be put after all the columns are defined
  as a table constraint
• If the constraint, is for multiple columns, then
  it must be defined as a table constraint

16
Primary Key Constraint

• Constraint constraint-name Primary Key
• Only one Primary Key constraint is allowed per
  table.
• If two or more columns make up a primary key, it
  must be defined as a table constraint

17
Unique Constraint

• Is similar to a Primary Key constraint, except
  multiple Unique constraints can be defined.
• If two or more columns are making up the
  constraint, it must be a table constraint

18
Adding Constraints

• CREATE TABLE student
• (StudentNo Integer Not Null Primary Key,

This column will be a primary key
Note the comma on the end, signaling the end of
this column definition.
19
Adding More Rows

• CREATE TABLE student
• (StudentNo Integer Not Null Primary Key,
• StudentName char(25) Not Null,
• StudentClass char(2) Not Null default
  (FR),
• SocialSecurityNbr char(11) Not Null Unique)

20
Put it Together
Supplier
Supplier_Part
Part
21
Supplier Table
Supplier
Create table supplier ( s_no char(2) not null,
sname char(15) not null with default,
status numeric(2) not null with default,
city char(15) not null with default,
constraint supplier_pk primary key (s_no))
Notice that primary key is defined as a table
constraint.
22
Part Table
Create table part ( p_no char(2) not null,
pname char(10) not null with default,
color char(10) not null with default 'Black',
weight smallint not null With default
check (weight between 1 and 20), city char(15)
not null With default, Constraint part_pk
Primary key (p_no))
Part
23
Referential Constraints

• In our ER/D we had the concept of a relationship
• That concept is carried to the database and
  called a Foreign Key constraint

24
Foreign Key Constraint

• Constraint constraint-name column-name references
  referenced table name rule clause

25
Rule Clause

• On foreign key relationships, actions can be
  defined for the following situations
• On update
• On delete
• On insert -- is implied by the definition of the
  FK
• Not all DBMSs implement these situations

26
Rule Clause Actions

• No Action
• Restrict
• Cascade
• Set Null (not implemented in SQL Srvr)
• Not All DBMSs implement all actions

27
How the Rule Clause Works

• I delete an Order and a LineItem exists for it
• Do I allow it?
• Do I restrict it?
• Do I delete the LineItem?
• I want to update the LineItem to a new Order
• Do I allow it?
• Do I restrict it?

Order
LineItem
LineItems are for Orders
28
Check Condition Constraint

• Constraint constraint-name check (check
  condition)
• Check Condition
• A check-constraint is a search-condition that
  must evaluate to not false.

29
SupplierPart
SupplierPart
Create table SupplierPart ( s_no char(2) not
null, p_no char(2) not null, qty smallint
not null with default, constraint
SupplierPart_pk primary key (s_no, p_no),
constraint SupplierPart_FK1 foreign key (s_no)
references supplier, constraint
SupplierPart_FK2 foreign key (p_no) references
part)
30
Alter Table

• Alter table allows you to change a table
  definition after it is already defined
• Not all characteristics of a table can be changed
• Alter capabilities vary by DBMS

31
What Can An Alter Table Do?

• Adding one or more columns to a table
• Adding or dropping a primary key
• Adding or dropping one or more unique or
  referential constraints
• Adding or dropping one or more check constraint
  definitions

32
Alter Table Syntax
Alter table table-name add column_x column
definition Alter table table-name add
constraint definition Alter table table-name
drop constraint constraint name
33
Supplier Table with Alter Table
Create table supplier ( s_no char(2) not null,
sname char(15) not null with default,
status numeric(2) not null with default,
city char(15) not null with default
) Alter table supplier add primary key
(s_no)
34
Part Table with Alter Table
Create table part ( p_no char(2) not null,
pname char(10) not null with default,
color char(10) not null with default 'Black',
weight smallint not null With default,
city char(15) not null With default ) Alter
table part add primary key (p_no)
35
SupplierPart Table with Alter
Create table SupplierPart ( s_no char(2) not
null, p_no char(2) not null, qty smallint
not null with default ) Alter table
supplierpart add primary key (s_no,
p_no) Alter table supplierpart add constraint
SupplierPart_FK1 foreign key (s_no) references
supplier Alter table supplierpart add
constraint SupplierPart_FK2 foreign key (p_no)
references part
36
Using Alter for PK/FK

• Sometimes you will be forced to use Alter for
  adding foreign keys
• Remember, a Primary Key must be defined on a
  table being referenced by a Foreign Key
• In a circular relationship all Primary Keys must
  be defined before any Foreign keys can be defined
• Consider

37
Circular Relationship

• Table A reference Table B, therefore Table B must
  exist
• Table B reference Table C, therefore Table A must
  exist
• Table C reference Table A, therefore Table A must
  exist
• All tables must be defined before any foreign
  keys can be defined

Table A
Table B
Table C
38
Creating a Circular Relationship

• Create TableA primary key
• Create TableB primary key
• Create TableC primary key
• Alter TableA add foreign key
• Alter TableB add foreign key
• Alter TableC add foreign key

39
Or

• Create TableA
• Create TableB
• Create TableC
• Alter TableA add primary key
• Alter TableB add primary key
• Alter TableC add primary key
• Alter TableA add foreign key
• Alter TableB add foreign key
• Alter TableC add foreign key

40
RI Rules

• SQL Server implements two RI rules
• No Action
• Cascade
• No Action is the default rule

41
Coding RI Rules

• Code as part of the foreign key constraint
• Can be defined for
• Update
• Delete
• Alter table ClassSection
• Add constraint CS_FK Foreign key
• (Instructor_ID) References Instructor
• on delete Cascade
• on update No Action

42
Results of Example

• If a row is deleted from the parent table
  (Instructor) all rows for the child table
  (ClassSection) having that Instructor ID value
  will be deleted
• If an instructor ID is updated in the Instructor
  table, and the old value for instructor id is in
  any row of the ClassSection table, the update
  will be rejected

43
Indexes

• Indexes are structures separate from tables that
  are used to
• Enforce uniqueness on a column or set of columns
• Allow for faster access based on a column or set
  of columns

44
Unique Indexes

• Force column values to be unique
• Is implicitly defined when a Primary Key or
  Unique constraint is defined
• Create Unique Index indexName on Student (SSN)

45
Duplicate Indexes

• Indexes do not have to enforce uniqueness
• Create Index indexName on Student (last_name,
  first_name)