© 2007 by Prentice Hall3-1
Introduction to Oracle 10gChapter 3Creating, Modifying, Renaming, and Deleting Database Tables
James Perry and Gerald Post
© 2007 by Prentice Hall3-2
Chapter Outline
• Designing a Database• Understanding Oracle User Accounts• Further Instructions for Personal Oracle Users• Creating Tables• Defining and Using Constraints• Altering a Table and Its Constraints• Displaying Tables’ Names and Constraints• Dropping, Reinstating, and Renaming Tables• Creating Tables Based on Other Tables
© 2007 by Prentice Hall3-3
Table 3.1 Oracle character data types
Type Oracle Designation
Description
Fixed CHAR(size) Fixed-length character data of size characters padded with spaces. Maximum size is 2000 bytes.
Fixed National NCHAR(size) Same as CHAR except stores National characters of maximum length 2000 bytes.
Variable VARCHAR2(size) Variable-length character data of size characters. Maximum size is 4000 bytes.
Variable National NVARCHAR2(size) Variable-length character data of size National characters. Maximum size is 4000 bytes.
Memo LONG Character data of variable length up to 2 gigabytes. (Not recommended. Use CLOB data type instead.)
© 2007 by Prentice Hall3-4
Table 3.2 Oracle numeric data types
Type Oracle Designation
Description
INT, INTEGER, SMALLINT NUMBER(38) An integer with up to 38 digits of precision.
Fixed precision NUMBER(p,s) A variable length number. Precision is the maximum number of digits, scale is the maximum number of digits to the right of the decimal point.
FLOAT,DOUBLE PRECISION
NUMBER A floating-point number with up to 38 digits of precision.
© 2007 by Prentice Hall3-5
Table 3.3 Oracle date and time data types
Type Oracle Designation
Description
INT, INTEGER, SMALLINT NUMBER(38) An integer with up to 38 digits of precision.
Fixed precision NUMBER(p,s) A variable length number. Precision is the maximum number of digits, scale is the maximum number of digits to the right of the decimal point.
FLOAT,DOUBLE PRECISION
NUMBER A floating-point number with up to 38 digits of precision.
© 2007 by Prentice Hall3-6
Table 3.4 Oracle image data types
Oracle Designation Description
BLOB Binary LOB stores binary, unstructured data up to 128 terabytes.
CLOB Character LOB stores character data for very large objects—up to 128 terabytes.
NCLOB Variable length Unicode national character data up to 128 terabytes.
© 2007 by Prentice Hall3-7
Table 3.5 Customer column names and data types
Columnname
Data type Maximumlength
Special conditions
CustomerID Integer primary key
FirstName National, variable length string 30 not null
LastName National, variable length string 30 not null
Address National, variable length string 40
City National, variable length string 30
State National, variable length string 20
Zipcode National, variable length string 20
HomePhone National, variable length string 20
CellPhone National, variable length string 20
WorkPhone National, variable length string 20
© 2007 by Prentice Hall3-8
Table 3.6 Constraint prefixes and example constraint names
Constraint Type Constraint Type Prefix
Example Constraint Name
CHECK ck ck_customers_zipcode
FOREIGN KEY fk fk_customers_properties
NOT NULL nn nn_agents_lastname
PRIMARY KEY pk pk_agentid
UNIQUE un un_contactreason_description
© 2007 by Prentice Hall3-9
Table 3.7 ContactReason, data types, and constraint
Column name Data type Length Constraint
ContactReason NVARCHAR2 15 PRIMARY KEY
Description NVARCHAR2 50
© 2007 by Prentice Hall3-10
Table 3.8 Structure of the Agents table
Column name Data type Length Constraint
AgentID INTEGER PRIMARY KEY
FirstName NVARCHAR2 30 NOT NULL
LastName NVARCHAR2 30 NOT NULL
HireDate DATE
BirthDate DATE
Gender NVARCHAR2 10 Only allowed values are ‘M’ and ‘F’
WorkPhone NVARCHAR2 20
CellPhone NVARCHAR2 20 UNIQUE
HomePhone NVARCHAR2 20
Title NVARCHAR2 20
TaxID NVARCHAR2 20
LicenseID NVARCHAR2 20
LicenseDate DATE
LicenseExpire DATE
LicenseStatusID INTEGER
© 2007 by Prentice Hall3-11
Table 3.9 Selected objects available through data dictionary views
Object Description
CONS_COLUMNS Table columns having constraints
CONSTRAINTS Table constraints
INDEXES Table indexes
OBJECTS All database objects
SEQUENCES Sequences generating unique keys
TAB_COLUMNS Table columns. (USER_TAB_COLUMNS are the current user’s columns)
TABLES Database tables
USERS Names of all database users (ALL_USERS)
VIEWS Database views
© 2007 by Prentice Hall3-12
Table 3.10 Some of the user constraints columns
Column Description
owner Owner of the constraint (username).
constraint_name Name of the constraint.
constraint_type Type: P, R, C, U, V or O.
table_name Table containing the constraint.
status Either ENABLED or DISABLED.
© 2007 by Prentice Hall3-13
Table 3.11 Customer columns and their constraints
Column Name Data Type Length Constraint
CustomerID Integer Primary key; default 0
CompanyName String Variable up to 50 NOT NULL
City String Variable up to 50 NOT NULL
Nation String Variable up to 50 (none)
ContactID Integer (none)
BaseCurrency String Variable up to 50 (none)
© 2007 by Prentice Hall3-14
3.1 Representing tables by listing their names and their column names
Customers(CustomerID, FirstName, LastName, Address, City, State, Zipcode, HomePhone, CellPhone, WorkPhone)
Listings(ListingID, PropertyID, ListingAgentID, SaleStatusID, BeginListDate, EndListDate, AskingPrice)
SaleStatus(SaleStatusID, StatusText)
© 2007 by Prentice Hall3-15
3.2 Simplified initial design of a Customers real estate table
Customers(CustomerID, FirstName, LastName, …, PropertyAddress, SqFt, Bedrooms, …)
seller information property information
© 2007 by Prentice Hall3-17
3.4 Changing another user’s password
changing a password to Columbus
Logging in and changing the password to a new one
© 2007 by Prentice Hall3-20
3.7 Creating a table using the SQL*Plus interface
SQL statement to create Customers table.
Oracle built the table this way.
© 2007 by Prentice Hall3-23
3.10 Displaying NOT NULL constraints using the DESCRIBE statement
NOT NULL in the Null? column
© 2007 by Prentice Hall3-31
3.18 Marking multiple columns unused
columns about to be marked “unused”
© 2007 by Prentice Hall3-33
3.20 Displaying table names using a data dictionary view
names of tables you created