Transcript Create Table

Creating Tables
9-1
Objectives
•
•
•
Create a table containing integrity constraints.
•
Recognize the indexes that are created
automatically by constraints.
•
Create a table by populating it with rows from
another table.
9-2
Identify table naming conventions.
Describe the datatypes that can be used when
specifying column definitions.
Database Objects
•
An Oracle database can contain multiple data
structures.
– Table
– View
•
9-3
Basic unit of data storage
Subset of data from one or more
tables
–
–
Sequence Generates primary key values
Index
Improves the performance of
some queries
–
Synonym
Gives alternative names to
objects
Define the structures in the database design.
Naming Rules
•
•
•
•
Must begin with a letter
•
Must not be an Oracle Server reserved word
9-4
Can 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
Creating Tables: Syntax
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] [, …]);
You specify:
•
•
•
•
9-5
Table name
Column name, column datatype and size
Schema – same as the owners name
DEFAULT expr – specify the default value if the
value is omitted in the INSERT statement
Referencing Another User's Tables
•
Tables belonging to other users are not in the
user's schema.
•
You should use the owner's name as a prefix to
the table.
•
A schema is a collection of objects including
tables, views, sequences, synonyms …
9-6
T
•
S
i
p
n
e
s
c
e
i
r
t
f
y
a
d
e
f
a
u
l
t
v
h
a
l
e
u
e
D
f
o
E
r
F
a
A
c
o
U
l
u
L
m
T
n
O
d
u
p
r
.
... hiredate DATE DEFAULT SYSDATE,...
i
t
n
g
i
o
a
n
n
Table Instance Chart: DEPT
Column name
DEPTNO
DNAME
LOC
Key type
PK
NN/UK
NN, U
NN
Datatype
NUMBER
VARCHAR2 VARCHAR2
Length
2
14
Sample data
10
20
30
40
Finance
Sales
Sales
Sales
FK table
FK column
9-8
13
Create a Table from a Table Instance
Chart
1. Create a script file. Add CREATE TABLE
table_name syntax.
2. Map the column names, datatypes, and lengths.
3. Map the NOT NULL constraints, except PRIMARY
KEY, as column level constraints.
4. Map the PRIMARY KEY constraint.
5. Map the UNIQUE, CHECK, and FOREIGN KEY
constraints.
6. Save and execute the script file.
9-9
Datatypes
Datatype
Description
VARCHAR2(size)
Variable length character values
CHAR(size)
Fixed length character values
NUMBER
Floating point numbers
NUMBER(p,s)
Number values with precision and scale
DATE
Date and time values
LONG
Variable length character values up to 2
GB
RAW and LONG RAW
Variable length character for binary data
9-10
C
C
R
E
A
T
E
T
A
B
L
E
•
d
C
r
e
e
p
a
t
t
e
a
t
a
b
l
e
.
2
(
9-11
d
e
p
t
n
o
n
u
m
b
e
r
(
2
r
e
a
t
i
n
g
a
T
a
b
l
e
Creating a Table by Using a Subquery:
Syntax
•
Create a table and insert rows by combining the
CREATE TABLE command and AS subquery
option.
CREATE TABLE table
[column(, column...)]
AS subquery;
• Match number of specified columns to number of
subquery columns.
• Define columns with column names, and default
values.
9-12
Creating a Table by Using a Subquery:
Example
•
Create a table containing all employees in
department number 10 in the EMP table.
CREATE TABLE
AS
SELECT
emp_10
empno, ename, sal*12 ANNSAL,
hiredate
FROM
emp
WHERE
deptno = 10;
Table created.
•
9-13
Only the NOT NULL constraint will be copied.
Confirming Table Creation
Confirm the existence of a database table along
with its column names by using the SQL*Plus
DESCRIBE command.
SQL> DESCRIBE emp_10
Name
Null?
Type
------------------------- -------- -------EMPNO
NOT NULL
NUMBER(4)
ENAME
VARCHAR2(10)
ANNSAL
NUMBER
HIREDATE
DATE
9-14
Constraints
•
•
Enforce rules at the table level.
•
The following constraint types are valid in Oracle:
Prevent the deletion of a table if there are
dependencies.
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK – specifies a condition that must be
true.
9-15
Constraint Guidelines
•
Name a constraint or the Server can generate a
name by using the SYS_Cn format.
•
Create a constraint
– At the same time as the table is created.
– After the table has been created.
•
9-16
Define a constraint at the column or table level.
Constraint: Syntax
•
Column-constraint level
column [CONSTRAINT constraint_name]
constraint_type,
• Table-constraint level
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
9-17
Define Constraints: Example
SQL> CREATE TABLE emp(
empno
ename
……
deptno
number(4),
varchar2(10),
number(7,2) NOT
NULL,
CONSTRAINT emp_empno_pk
PRIMARY KEY
(EMPNO));
9-18
The NOT NULL Constraint
•
Ensures that null values are not permitted for the
column
•
Is defined at the column-constraint level
Example
CREATE TABLE friend (...
phone VARCHAR2(15) NOT NULL,...
last_name VARCHAR2(25)
CONSTRAINT friend_last_name_nn
NOT NULL,...);
9-19
The UNIQUE Constraint
•
Designates a column or combination of columns
so that no two rows in the table can have the
same value for this key
•
Allows null values if the UNIQUE key is based on
a single column
•
Is defined at either the table or column-constraint
level
•
Automatically creates a UNIQUE index
... phone VARCHAR2(10)
CONSTRAINT emp_phone_uk UNIQUE,...
9-20
The PRIMARY KEY Constraint
•
Creates a primary key for the table; only one
primary key is allowed for each table
•
•
Enforces uniqueness of columns
•
Is defined at either the table or column constraint
level
•
Automatically creates a UNIQUE index
Does not allow null values in any part of the
primary key
... id
9-21
NUMBER(7)
CONSTRAINT emp_id_pk PRIMARY KEY,...
The FOREIGN KEY Constraint
•
Designates a column or combination of columns
as a foreign key
•
Establishes a relationship between the primary or
unique key in the same table or between tables
•
Is defined at either the table or column constraint
level
•
Must match an existing value in the parent table
or be NULL
... deptno
NUMBER(2)
CONSTRAINT emp_deptno_fk
REFERENCES dept(deptno),...
9-22
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
•
ON DELETE CASCADE
– Allows deletion in the parent table and deletion
of the dependent rows in the child table
•
9-23
ON DELETE SET NULL
The CHECK Constraint
•
•
Defines a condition that each row must satisfy
Expressions that are not allowed:
– References to pseudocolumns CURRVAL,
NEXTVAL, LEVEL, or ROWNUM
– Calls to SYSDATE, UID, USER, or USERENV
functions
– Queries that refer to other values in other rows
•
Is defined at either the table-constraint level or the
column-constraint level
... deptno
NUMBER(2)
CONSTRAINT emp_deptno_ck
CHECK (DEPTNO BETWEEN 10 AND 99),...
9-24
Create Table: Example
SQL> CREATE TABLE s_dept
2 (id
NUMBER(7)
3
CONSTRAINT s_dept_id_pk PRIMARY KEY,
4
name
VARCHAR2(25)
5
CONSTRAINT s_dept_name_nn NOT NULL,
6
region_id NUMBER(7)
7
CONSTRAINT s_dept_region_id_fk REFERENCES
8
s_region (id),
9
CONSTRAINT s_dept_name_region_id_uk UNIQUE
10
(name, region_id));
9-25
Create Table: Example
SQL> CREATE TABLE s_emp
2 (id
NUMBER(7)
3
CONSTRAINT s_emp_id_pk PRIMARY KEY,
4
last_name
VARCHAR2(25)
5
CONSTRAINT s_emp_last_name_nn NOT NULL,
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
9-26
first_name
VARCHAR2(25),
userid
VARCHAR2(8)
CONSTRAINT s_emp_userid_nn NOT NULL
CONSTRAINT s_emp_userid_uk UNIQUE,
start_date
DATE
DEFAULT SYSDATE,
comments
VARCHAR2(25),
manager_id
NUMBER(7),
title
VARCHAR2(25),
dept_id
NUMBER(7)
CONSTRAINT s_emp_dept_id_fk REFERENCES
s_dept (id),
salary
NUMBER(11,2),
commission_pct NUMBER(4,2)
CONSTRAINT s_emp_commission_pct_ck CHECK
(commission_pct IN(10,12.5,15,17.5,20)));
Summary
•
Build tables in a database by using the SQL
CREATE TABLE command.
CREATE TABLE [schema.]table
(column datatype [column_constraint],
...
[table_constraint]);
• Construct a table with the following features:
– Table name
– Column names, datatypes, and lengths
– Integrity constraints
9-27
Summary
•
Constraint types
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
•
UNIQUE indexes
– Are created automatically with PRIMARY KEY
and UNIQUE constraints.
•
SQL*Plus DESCRIBE command
– Displays the structure of the table.
9-28
Practice Overview
•
•
9-29
Creating new tables containing constraints
Verifying that the tables exist
Practice 1
•
Create DEPARTMENT table based on the following
table instance chart. Enter the syntax in a script
file and execute the script file to create the table.
Confirm the table created.
Column name
ID
NAME
Key type
PK
NN/UK
NN, U
NN
Datatype
NUMBER
VARCHAR2
Length
7
25
FK table
FK column
9-30
Practice 2
•
9-31
Populate the DEPARTMENT table with data from
DEPT table. Include only columns that you need.
Practice 3
•
Create EMPLOYEE table based on the following
table instance chart. Enter the syntax in a script
file and execute the script file to create the table.
Confirm the table created.
Column name
EID
Key type
PK
NN/UK
NN, U
ENAME
DEPTID
NN
FK table
DEPARTMENT
FK column
ID
Datatype
NUMBER
VARCHAR2 NUMBER
Length
7
25
9-32
7
Practice 4
•
Populate the EMPLOYEE table with data from EMP
table. Include only columns that you need.
9-33