Transcript DDL : Tables

SQL’s Data Definition Language
(DDL)



DDL statements define, modify and
remove objects from data dictionary
tables maintained by the DBMS
Whenever you use a DDL statement, the
DBMS changes metadata (“data about
data”) maintained in the data dictionary
Objects that can be defined include:
table, view, sequence, index
Oracle’s Data Dictionary




Data dictionary tables maintain data about the
structure of databases
Users cannot directly insert, update or delete
information in the data dictionary
DDL statements cause the DBMS to add/update
or delete information in the data dictionary tables
Oracle’s data dictionary views such as
ALL_OBJECTS, ALL_TABLES,
ALL_CONSTRAINTS, ALL_COLUMNS can be
queried using SELECT statements and contain
information about objects you have access to
SQL’s DDL Statements




Objects are defined using the CREATE
statement
Some objects can be modified using the
ALTER statement
Objects are removed using the DROP
statement
Oracle’s data dictionary views such as
USER_OBJECTS, USER_TABLES,
USER_CONSTRAINTS, USER_COLUMNS,
USER_SEQUENCES, USER_INDEXES can
be queried using SELECT statements and
contain information about objects you have
created
Schema


The DataBase Administrator (DBA) has
set up your Oracle account with
permission for you to create objects in a
schema (schema has same name as
your account)
A schema (same as DB2’s collection) is
a collection of related tables, views,
sequences and indexes, i.e. a database
System Datatypes

System-defined datatypes include:
– CHAR(n): Fixed length character string often used
for Primary and Foreign Key numeric fields and also
for codes such as ProgramCode(CPA/CPD/CNS/…);
eg. program_code CHAR(3)
– VARCHAR2(n): Variable length character string often
used for names;
eg. last_name VARCHAR2(30)
– NUMBER(n,r): Real numbers with n digits in total
and with r digits to the right of the decimal point; eg
hrly_pay_rate NUMBER(5,2) can store hourly pay
rates from 0.00 to 999.99
– INTEGER: Integer values up to 2*(10**9)
– DATE: dates in the format DD-MON-YYYY or
MM/DD/YYYY
Create a Table

You define a table and its columns using the
CREATE TABLE statement using the basic
format:
CREATE TABLE tablename (
column1name datatype(length),
column2name datatype(length),…
column255name datatype(length))
eg. CREATE TABLE student
(id CHAR(9),
fname VARCHAR2(30),
lname VARCHAR2(30),
gpa decimal(2,1) )
Create a Table (ctd)

You can also create a table based on an
existing table:
CREATE TABLE table1name
AS
SELECT col1name, …, colnname
FROM table2name
WHERE …
eg. CREATE TABLE honourstudent
AS
SELECT id, lname
FROM student
WHERE gpa = 4.0
Change Table Structure

Change structure of a table (i.e. add
columns or constraints, remove columns
or constraints, change properties of a
column) using the ALTER TABLE
statement:
ALTER TABLE tablename (
ADD col1name datatype(length),
DROP col2name,
MODIFY col3name datatype(length),
MODIFY col4name NOT NULL, … )
ALTER TABLE Examples



ALTER TABLE student
ADD (address VARCHAR(40))
ALTER TABLE student
MODIFY (lname VARCHAR2(35) )
ALTER TABLE student
DROP COLUMN gpa
Remove Table from Data
Dictionary


Remove table from data dictionary
(and also deletes all data in table)
DROP TABLE tablename
Deletes all data in table and frees
storage but leaves table in data
dictionary
TRUNCATE TABLE tablename
Constraints




Constraints protect the integrity of data
in a database
Constraints are defined on tables and
columns in tables
Constraint types: PRIMARY KEY,
FOREIGN KEY, NOT NULL, CHECK,
UNIQUE
All constraints should be given a name,
except for NOT NULLs, so error
messages produced will include the
name of the constraint violated
Constraints (ctd)



Information about constraints is
maintained in the data dictionary and
can be queried using the view
USER_CONSTRAINTS
Constraints may be defined as part of a
CREATE TABLE statement
Constraints may be added, dropped or
disabled (but not modified) by using an
ALTER TABLE statement
Constraint Example

CREATE TABLE student
( id CHAR(9) PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
lname VARCHAR2(30) NOT NULL,
sem_num SMALLINT,
soc_ins CHAR(9) CONSTRAINT
soc_ins_uq UNIQUE(soc_ins),
prog_code CHAR(3) NOT NULL,
CONSTRAINT student_prog_code_fk
FOREIGN KEY (prog_code)
REFERENCES program(progcode) )
Constraint Examples
ALTER TABLE student
ADD CONSTRAINT student_sem_num
CHECK (sem_num BETWEEN 1 AND 6)
 ALTER TABLE student
DROP UNIQUE (soc_ins_uq)
 ALTER TABLE student
DROP PRIMARY KEY
 ALTER TABLE student
DROP CONSTRAINT student_sem_num
 ALTER TABLE student
DISABLE CONSTRAINT
student_prog_code_fk
