Transcript chap02

Creating and Modifying
Database Tables
Chapter 2
Guide to Oracle10G
1
Objectives
• Become acquainted with Structured Query
Language (SQL)
• Learn about Oracle9i user schemas
• Learn how to define Oracle9i database tables
• Create database tables using SQL*Plus
Guide to Oracle10G
2
Objectives
• Learn how to debug Oracle9i SQL commands
and use Oracle Corporation online help
resources
• Learn how to view information about your
database tables using Oracle9i data
dictionary views
• Modify and delete database tables using
SQL*Plus
Guide to Oracle10G
3
Introduction to SQL
• Structured Query Language (SQL): the
standard query language for relational
databases
• Data definition language (DDL)
– Create new database objects
– Modify or delete existing objects.
• Data manipulation language (DML)
– Insert, update, delete, and view database data.
Guide to Oracle10G
4
Oracle10G User Accounts
• User account - identified by a unique
username and password
• User schema - all of the objects that the user
creates and stores in the database
• Object owner has privileges to perform all
possible actions on an object
Guide to Oracle10G
5
Defining Oracle10G Database Tables
• To create a new table specify:
– Table name
– Name of each data field
– Data type and size of each data field
• Constraints: restrictions on the data values that a field can store
• Oracle Naming Standard:
– Series of rules Oracle Corporation established for naming all
database objects
– Objects must be from 1 to 30 characters long
– Can contain letters, numbers, and the special symbols $, _, and #
– Must begin with a character
Guide to Oracle10G
6
Creating a Table
CREATE TABLE tablename
(fieldname1data_type,
(fieldname2 data_type,
…)
Guide to Oracle10G
7
Oracle10G Data Types
• Data type: specifies the kind of data that a field
stores
• Assigning a data type provides a means for error
checking
• Data types enable the DBMS to use storage space
more efficiently by internally storing different types of
data in different ways
Guide to Oracle10G
8
Character Data Types
• VARCHAR2
– Stores variable-length character data up to a
maximum of 4,000 characters
– Values in different records can have a different
number of characters
– fieldname VARCHAR2(maximum_size)
Guide to Oracle10G
9
Character Data Types
• CHAR
– Fixed-length character data up to a maximum size
of 2,000 characters
– Data values for different records all have the same
number of characters
– DBMS adds trailing blank spaces to the end of the
entry to make the entry fill the maximum_size
value
– Data longer than maximum_size causes an error
– fieldname CHAR[(maximum_size)]
Guide to Oracle10G
10
Character Data Types
• NVARCHAR2 and NCHAR
– Analogous to VARCHAR2 and CHAR but use
Unicode rather than ASCII
– Used to hold character data in languages other
than English
Guide to Oracle10G
11
Number Data Types
• Stores negative, positive, fixed, and floating point
numbers between 10–130 and 10125, with precision
up to 38 decimal places
• General Syntax: fieldname NUMBER [([precision,]
[scale])]
• Integer: fieldname NUMBER(precision)
• Fixed point: fieldname NUMBER[([precision],[scale])]
• Floating point: fieldname NUMBER
Guide to Oracle10G
12
Date and Time Data Types
• DATE
– Dates from December 31, 4712 BC to December 31, 4712
AD
– Default format DD-MON-YY
– Default time format HH:MI:SS A.M.
– fieldname DATE
• TIMESTAMP
– Similar to DATE but stores fractional seconds
– fieldname TIMESTAMP (fractional_seconds_precision)
Guide to Oracle10G
13
Date and Time Data Types
• INTERVAL YEAR TO MONTH
– Time interval, expressed in years and months
– +02-11 specifies a positive time interval of 2 years and 11 months
– fieldname INTERVAL YEAR[(year_precision)] TO MONTH
• INTERVAL DAY TO SECOND
– Time interval, expressed in days, hours, minutes, and seconds
– -04 03:20:32.00: 4 days, 3 hours, 20 minutes, and 32 seconds
– fieldname INTERVAL DAY[(leading_precision)] TO
SECOND[(fractional_seconds_precision)]
Guide to Oracle10G
14
Large Object (LOB) Data Types
Guide to Oracle10G
15
Constraints
•
Rules that restrict the data values that you can enter into a field in a
database table
•
Integrity constraints: define primary and foreign keys
•
Value constraints: define specific data values or data ranges that
must be inserted into columns and whether values must be unique or
not NULL
•
Table constraint: restricts the data value with respect to all other
values in the table
•
Field constraint: limits the value that can be placed in a specific field,
irrespective of values that exist in other table records
Guide to Oracle10G
16
Oracle Constraint Naming
Convention
• tablename_ fieldname_constraintID
Guide to Oracle10G
17
Integrity Constraints
• Define primary key fields
• Specify foreign keys and their corresponding
table and column references
• Specify composite keys
Guide to Oracle10G
18
Value Constraints
• Check conditions: field value must be a specific value or fall
within a range of values
• NOT NULL constraints: specify whether a field value can be
NULL
• Default constraints: specify that a field has a default value that
the DBMS automatically inserts for every record, unless the user
specifies an alternate value
• Unique constraints: specify that a field must have a unique value
for every table record
Guide to Oracle10G
19
Creating Database Tables Using
SQL*Plus
• Type SQL commands at the SQL prompt
• End each command with a semicolon (;)
• Not case sensitive
Guide to Oracle10G
20
Log On to SQL*Plus
Guide to Oracle10G
21
SQL*Plus Program Window
Guide to Oracle10G
22
Create a Table
Guide to Oracle10G
23
Table Creation Sequence
• When creating tables with foreign key references,
create referenced tables first
• Always create tables without foreign keys before
those with foreign keys
Guide to Oracle10G
24
Using Notepad
• Useful to use Notepad to edit sql commands
– Commands can be edited without retyping
– Commands can be saved
– Saving multiple sql commands in a file creates a
script
Guide to Oracle10G
25
Errors
• When an error occurs error information is displayed:
–
–
–
–
Line number
Position on line
Error code
Description of error
• Error codes
– 3 letter prefix (I.e. ORA)
– 5 digit code
– More information on errors can be found at
http://otn.oracle.com
Guide to Oracle10G
26
Exiting SQL*Plus
• Three ways to exit SQL*Plus:
– Type exit at the SQL prompt
–Click File on the menu bar, and then click Exit
– Click the Close button on the program window title
bar
• Database session ends when SQL*Plus exits
Guide to Oracle10G
27
Create Table with Foreign Key
Constraint
Guide to Oracle10G
28
Viewing Information About Tables
• describe tablename: displays column names and
data types
• Data dictionary: tables that contain information
about the structure of the database.
– USER: shows the objects in the current user’s schema
– ALL: shows both objects in the current user’s schema and
objects that the user has privileges to manipulate
– DBA: allows users who are database administrators to view
information about all database objects
Guide to Oracle10G
29
Viewing Tables in the Database
Guide to Oracle10G
30
Viewing Constraints on One Table
Guide to Oracle10G
31
Modifying and Deleting Database
Tables
• Modify existing database tables by:
–
–
–
–
Changing the name of a table
Adding new columns
Deleting columns that are no longer needed
Changing the data type or maximum size of an existing
column
• Unrestricted action: some specifications can always
be modified
• Restricted action: specifications modified only in
certain situations
Guide to Oracle10G
32
Unrestricted Action
Guide to Oracle10G
33
Restricted Actions
Guide to Oracle10G
34
Deleting and Renaming Tables
• To delete:
– Drop table [tablename]
– Use with caution
– To delete foreign key constraints, add “cascade constraints”
• To rename:
– Rename old_tablename to new_tablename
– DBMS automatically transfers to the new table integrity
constraints, indexes, and privileges that referenced the old
table.
Guide to Oracle10G
35
Adding Fields to Existing Tables
• To add a field:
– ALTER TABLE tablename ADD(fieldname
data_declaration constraints);
Guide to Oracle10G
36
Modifying Existing Field Data
Definitions
• Can only change datatype to compatible data
type (i.e. varchar2 to char)
• ALTER tablename MODIFY(fieldname
new_data_declaration);
Guide to Oracle10G
37
Deleting a Field
• ALTER TABLE tablename DROP COLUMN
fieldname;
• Can be used to rename a field – first drop,
then add
Guide to Oracle10G
38
Adding and Deleting Constraints
• Add a constraint: ALTER TABLE tablename
ADD CONSTRAINT constraint_name
constraint_definition;
• Remove a constraint: ALTER TABLE
tablename DROP CONSTRAINT
constraint_name;
Guide to Oracle10G
39
Enabling and Disabling Constraints
• When modifying a database it can be useful to
disable constraints
• Constraints are enabled by default
• To disable a constraint: ALTER TABLE tablename
DISABLE CONSTRAINT constraint_name;
• To enable a constraint: ALTER TABLE tablename
ENABLE CONSTRAINT constraint_name;
Guide to Oracle10G
40
Summary
• SQL commands include:
– Data description language (DDL) commands: create, modify,
Deleted database objects
– Data manipulation language (DML) commands: insert,
update, delete, view database data
• To create a table:
– specify the table name, the name of each data field, and the
data type and size of each data field
• Data types ensure correct data values
• Constraints restrict values of database fields
• SQL*Plus commands are not case sensitive
Guide to Oracle10G
41
Summary
• Errors include line number, position, error
code
• Use DESCRIBE command to display a table’s
fieldnames and data types
• Tables can be modified or deleted but some
changes are restricted
Guide to Oracle10G
42