DDL and DCL

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

 Creating Tables
– Create the table.

CREATE TABLE dept

(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
create_date DATE DEFAULT SYSDATE);
Table created.

– Confirm table creation.

DESCRIBE dept
 Constraints
– Create a constraint at either of the following times:
• At the same time as the table is created
• After the table has been created
– Define a constraint at the column or table level.
– View a constraint in the data dictionary.

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
 Constraints...
– Column-level constraint:
column [CONSTRAINT constraint_name] constraint_type,

– Table-level constraint:

column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
 Defining Constraints
– Column-level constraint:
CREATE TABLE employees(
employee_id NUMBER(6)
CONSTRAINT emp_emp_id_pk PRIMARY KEY, 1
first_name VARCHAR2(20),
...);

– Table-level constraint:
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
... 2
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
 The NOT NULL Constraint
• The NOT NULL constraint ensures that the column
has a value and the value is not a null value

• A space or a numeric zero is not a null value

• At the column level ONLY, the constraint is defined

by:
Name VARCHAR2(15) CONSTRAINT faculty_name_nn NOT NULL,
 UNIQUE Constraint
• Defined at either the table level or the column
level:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));
 FOREIGN KEY Constraint
DEPARTMENTS

PRIMARY
KEY
…

EMPLOYEES
FOREIGN
KEY

… Not allowed
INSERT INTO (9 does not
exist)
Allowed
 FOREIGN KEY Constraint
• Defined at table level only:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
 FOREIGN KEY Constraint:
Keywords
– FOREIGN KEY: Defines the column in the child
table at the table-constraint level
– REFERENCES: Identifies the table and column in
the parent table
 CHECK Constraint

– Defines a condition that each row must

satisfy

..., salary NUMBER(2)

CONSTRAINT emp_salary_min
CHECK (salary > 0),...
 CREATE TABLE: Example
CREATE TABLE employees
( employee_id NUMBER(6)
CONSTRAINT emp_employee_id PRIMARY KEY
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
CONSTRAINT emp_email_uk UNIQUE
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
CONSTRAINT emp_salary_ck CHECK (salary>0)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
CONSTRAINT emp_dept_fk REFERENCES
departments (department_id));
 Violating Constraints

UPDATE employees
SET department_id = 55
WHERE department_id = 110;

UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPT_FK)
violated - parent key not found

• Department 55 does not exist.

 Violating Constraints
• You cannot delete a row that contains a
primary key that is used as a foreign key in
another table.
DELETE FROM departments
WHERE department_id = 60;

DELETE FROM departments

*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found
 Creating a Table
by Using a Subquery
– Create a table and insert rows by combining the
CREATE TABLE statement and the AS subquery
option.

CREATE TABLE table

[(column, column...)]
AS subquery;
– Match the number of specified columns to the number
of subquery columns.
– Define columns with column names and
default values.
 Creating a Table
by Using a Subquery
CREATE TABLE dept80
AS
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM employees
WHERE department_id = 80;
Table created.

DESCRIBE dept80
 ALTER TABLE Statement
• Use the ALTER TABLE statement to:
– Add a new column
– Modify an existing column
– Define a default value for the new column
– Drop a column
 Adding a New Column to an
Existing Table
• The general syntax to add a column to an
existing table is

ALTER TABLE tablename

ADD columnname datatype;

SQL> ALTER TABLE student

2 ADD SocialSecurity CHAR(9);
Table altered.
SQL>
 Modifying an Existing Column
• The general syntax to modify an existing column is

ALTER TABLE tablename

MODIFY columnname newdatatype;

where newdatatype is the new data type or the new size for the
column.

SQL> ALTER TABLE student

2 MODIFY SocialSecurity VARCHAR2(11);
Table altered.
SQL>
 Adding a Constraint

• To add a constraint using ALTER TABLE, the syntax for table

level constraint is used. The general syntax of ALTER TABLE is
ALTER TABLE tablename
ADD [CONSTRAINT constraint_name]
constraint_type (column, …),

SQL> ALTER TABLE COURSE

2 ADD CONSTRAINT COURSE_PREREQ_FK FOREIGN KEY (PREREQ)
3 REFERENCES COURSE(COURSEID);
Table altered.
SQL>
 Dropping a Column
• The general syntax is
ALTER TABLE tablename DROP COLUMN columnname;
 Renaming a Table
– Additional DDL statements include the RENAME
statement, which is used to rename a table, view,
sequence.
– Syntax
– RENAME old_name TO new_name;
– In the syntax:
– old_name is the old name of
the table, view, sequence.
– new_name is the new name
of the table, view, sequence.
 Dropping a Table
– All data and structure in the table are deleted.
– All indexes are dropped.
– All constraints are dropped.
– You cannot roll back the DROP TABLE statement.

DROP TABLE dept80;

Table dropped.
 Dropping a Table
• The general syntax is
DROP TABLE tablename [CASCADE CONSTRAINTS];

• For example,
DROP TABLE sample;

• Oracle displays a “Table dropped” message when a table is

successfully dropped.
• If you add optional CASCADE CONSTRAINTS clause, it removes
foreign key references to the table also.
 Sequences
• Often
In most
weversions
want toofassign
SQL we
eachcanrow
useaautoincrementing
unique number fields to
do
– this
These are useful as primary keys

– Usually the first entry is assigned 1, the next 2, and so on, but Oracle
lets you change this
 Sequences
• In Oracle we use a Sequence
– A sequence is a source of numbers
– We can declare several sequences, giving each a
name, a start point, and a step size
– We can then generate unique numbers by asking
for the next element from a sequence
 Sequences in Oracle
• To declare a sequence:
CREATE SEQUENCE <name>
[START WITH <value>]
[INCREMENT BY <value>]
– If no START WITH or INCREMENT BY values
are given they default to 1
• To get the next value from a sequence
<sequence name>.nextVal
 Sequence Example
• Creating a sequence
CREATE SEQUENCE mySeq START WITH 1
• Using a sequence
SELECT mySeq.nextVal FROM DUAL;
INSERT INTO Student
(stuID, stuName, stuAddress)
VALUES
(mySeq.nextVal, 'Steve Mills',
'13 Elm Street')
 Oracle Data Dictionary
• To find out what tables and sequences you
have defined use
SELECT table_name
FROM user_tables
– The user_tables table is maintained by Oracle
– It has lots of columns, so don’t use
SELECT * FROM user_tables
 Oracle Data Dictionary
• To find the details of a table use
DESCRIBE <table name>
• Example:
SQL> DESCRIBE Student;
Name Null? Type
------------ -------- ----------
STUID NOT NULL NUMBER(38)
STUNAME NOT NULL VARCHAR2(50)
STUADDRESS VARCHAR2(50)
STUYEAR NUMBER(38)
 DCL: Data Control Language
• Controlling Access to database objects such as tables
and views
• Example : Granting “Mary” the access to Table “student” (for inserting,
updating and deleting)
– GRANT INSERT, UPDATE, DELETE ON Emp TO Mary
– GRANT <privileges> ON <object name>
TO <grantee> [ <comma> <grantee> ... ]
[ WITH GRANT OPTION ]
– WITH GRANT OPTION: allows the grantee to further grant privileges
– Can be limited to a column of a table, Ex: GRANT UPDATE(name) ON emp TO
Mary
– To revoke privileges : REVOKE