Database Systems
T. Amani Al-Kebsi Lecture 8
Using DDL Statements
to Create and Manage Tables
SQL Commands:
SQL Commands:
SQL Commands:
CREATE TABLE Statement
Creating a basic table involves naming the
table and defining its columns and each
column's data type.
Naming Rules
Table names and column names:
➢ Must begin with a letter
➢ Must be 1–30 characters long
➢ Must contain only A–Z, a–z, 0–9, _, $, and #
➢ Must not duplicate the name of another object owned by the same
user
➢ Must not be an Oracle server–reserved word
Data Types
Data Type Description
VARCHAR2(size) Variable-length character data
CHAR(size) Fixed-length character data
NUMBER(p,s) Variable-length numeric data
DATE Date and time values
LONG Variable-length character data (up to 2 GB)
CLOB Character data (up to 4 GB)
RAW and LONG Raw binary data
RAW
BLOB Binary data (up to 4 GB)
BFILE Binary data stored in an external file (up to 4 GB)
TIMESTAMP Date with fractional seconds
Constraints
– Constraints enforce rules at the table level.
– Constraints prevent the deletion of a table if there are dependencies.
– The following constraint types are valid:
› NOT NULL
› UNIQUE
› PRIMARY KEY
› FOREIGN KEY
› CHECK
Constraints
– Create a constraint at either of the following times:
› At the same time as the creation of the table
› After the creation of the table
– Define a constraint at the column or table level.
NOT NULL Constraint
– The NOT NULL constraint ensures that the column contains
no null values.
– Columns without the NOT NULL constraint can contain null
values by default.
– NOT NULL constraints must be defined at the column
level.
UNIQUE Constraint
– A UNIQUE key integrity constraint requires that every value
in a column or a set of columns (key) be unique—that is,
no two rows of a table can have duplicate values in a
specified column or a set of columns.
– UNIQUE constraints enable the input of nulls unless you
also define NOT NULL constraints for the same columns.
UNIQUE Constraint
– UNIQUE constraints can be defined at the column level or
table level. You define the constraint at the table level
when you want to create a composite unique key.
– A composite key is defined when there is not a single
attribute that can uniquely identify a row. In that case, you
can have a unique key that is composed of two or more
columns, the combined value of which is always unique
and can identify rows.
PRIMARY KEY Constraint
– A PRIMARY KEY constraint creates a primary key for the
table.
– The PRIMARY KEY constraint is a column or a set of
columns that uniquely identifies each row in a table. This
constraint enforces the uniqueness of the column or
column combination and ensures that no column that is
part of the primary key can contain a null value.
FOREIGN KEY Constraint
– The FOREIGN KEY (or referential integrity) constraint
designates a column or a combination of columns as a
foreign key and establishes a relationship with a primary
key or a unique key in the same table or a different table.
– A foreign key value must match an existing value in the
parent table or be NULL.
FOREIGN KEY Constraint
– FOREIGN KEY constraints can be defined at the column or
table constraint level. A composite foreign key must be
created by using the table-level definition.
FOREIGN KEY Constraint
› FOREIGN KEY Constraint: Keywords
– The foreign key is defined in the child table and the table containing the
referenced column is the parent table. The foreign key is defined using a
combination of the following keywords:
✓ FOREIGN KEY is used to define the column in the child table at the
table-constraint level.
✓ REFERENCES identifies the table and the column in the parent table.
✓ ON DELETE CASCADE indicates that when a row in the parent table is
deleted, the dependent rows in the child table are also deleted.
✓ ON DELETE SET NULL indicates that when a row in the parent table is
deleted, the foreign key values are set to null.
FOREIGN KEY Constraint
› FOREIGN KEY Constraint: Keywords (Continue)
– The default behavior is called the restrict rule, which disallows the
update or deletion of referenced data.
– Without the ON DELETE CASCADE or the ON DELETE SET NULL
options, the row in the parent table cannot be deleted if it is
referenced in the child table.
CHECK Constraint
– The CHECK constraint defines a condition that each row
must satisfy.
– A single column can have multiple CHECK constraints that
refer to the column in its definition.
– There is no limit to the number of CHECK constraints that
you can define on a column.
CREATE TABLE Statement
CREATE TABLE Statement
› Example of creating a department table
create table dept(
Dept_no number(5),
Dept_Name varchar2(40),
constraint PK_dept primary key(dept_no))
CREATE TABLE Statement
› Example of creating an employee table
create table emp(
Emp_no number(10),
Emp_name varchar2(50) not null,
Emp_email varchar2(30),
Emp_pass varchar2(10) default 123,
Dept_no number(5),
constraint PK_emp primary key(Emp_no),
constraint Uni_email unique(Emp_email ),
constraint FK_dept foreign key(dept_no) references dept(dept_no) on
delete cascade)
Create Table Using another
Table (Using a Subquery)
A copy of an existing table can be created using a
combination of the CREATE TABLE statement and the
SELECT statement.
The new table has the same column definitions. All columns
or specific columns can be selected.
When you create a new table using existing table, new table
would be populated using existing values in the old table.
Create Table Using another
Table (Using a Subquery)
Syntax:
The basic syntax for creating a table from another table is :
Create Table Using another
Table (Using a Subquery)
• Example
The basic syntax for creating a table from another table is :
Create Table Using another
Table (Using a Subquery)
• Example
The basic syntax for creating a table from another table is :
Create table dept_copy
As
Select * from dept
ALTER TABLE Statement
› Use the ALTER TABLE statement to:
– Add a new column
– Modify an existing column definition
– Rename a column
– Add a constraint to an existing column
– Remove a constraint from a column
– Drop a column
ALTER TABLE Statement
› Alter table to add a new column
• Syntax
Allter table table_name add column_name datatype size
• Example
Alter table emp add phone_num number(10)
ALTER TABLE Statement
› Alter table to edit the datatype and
size of column
• Syntax
Allter table table_name modify column_name datatype size
• Example
Alter table emp modify phone_num number(15)
ALTER TABLE Statement
› Alter table to rename an existing
column
• Syntax
Allter table table_name rename column old_column_name to
new_column_name
• Example
Alter table emp rename column phone_num to emp_phone_num
ALTER TABLE Statement
› Alter table to add a constraint to an
existing column
• Syntax
Allter table table_name add constraint constraint_name
constraint_type(column_name)
• Example
Alter table emp add constraint uni_ph_num unique(emp_phone_num)
ALTER TABLE Statement
› Alter table to remove a constraint
from a column
• Syntax
Allter table table_name drop constraint constraint_name
• Example
Alter table emp drop constraint uni_ph_num
ALTER TABLE Statement
› Alter table to remove a column
• Syntax
Allter table table_name drop column column_name
• Example
Alter table emp drop column emp_phone_num
RENAME Statement
› Renaming a Table
• Syntax
rename old_table_name to new_table_name
• Example
rename emp to employee
DROP TABLE Statement
› Dropping a Table
– The DROP TABLE statement moves a table to the recycle bin or
removes the table and all its data from the database entirely.
DROP TABLE Statement
• Syntax
drop table table_name
• Example
drop table emp
References
SQL TUTORIAL , Simply Easy Learning by tutorialspoint.com.
Oracle Slides.
