© 2007 by prentice hall3-1 introduction to oracle 10g chapter 3 creating, modifying, renaming, and...

© 2007 by Prentice Hall3-1

Introduction to Oracle 10gChapter 3Creating, Modifying, Renaming, and Deleting Database Tables

James Perry and Gerald Post


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


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.)


Table 3.2 Oracle numeric data types


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.


Table 3.3 Oracle date and time data types


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.


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.


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


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


Table 3.7 ContactReason, data types, and constraint

Column name Data type Length Constraint

ContactReason NVARCHAR2 15 PRIMARY KEY

Description NVARCHAR2 50


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


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


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.


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)


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)


3.2 Simplified initial design of a Customers real estate table

Customers(CustomerID, FirstName, LastName, …, PropertyAddress, SqFt, Bedrooms, …)

seller information property information


3.3 Attempting to log in with a new user account having no privileges


3.4 Changing another user’s password

changing a password to Columbus

Logging in and changing the password to a new one


3.5 Removing a user


3.6 Examining selected Oracle data types


3.7 Creating a table using the SQL*Plus interface

SQL statement to create Customers table.

Oracle built the table this way.


3.8 Creating and displaying a table comment


3.9 Column comment example

comment inserted into the State column


3.10 Displaying NOT NULL constraints using the DESCRIBE statement

NOT NULL in the Null? column


3.11 Defining a primary key

type this SQL command


3.12 Creating the Agents table with constraints


3.13 Designating default values for selected table columns


3.14 Tables with foreign key references to each other


3.15 Adding constraints with the ALTER TABLE statement


3.16 Adding a column to a table

newly added ContactDate column


3.17 Dropping a column from a table


3.18 Marking multiple columns unused

columns about to be marked “unused”


3.19 Describing the Listings table


3.20 Displaying table names using a data dictionary view

names of tables you created


3.21 Some of the columns in the user_tables view


3.22 Displaying details about a table’s columns


3.23 Listing your table constraints


3.24 Reviewing column constraints for the Listings table


3.25 Displaying more complete constraint information


3.26 Seven rows showing schema SYS’ table comments


3.27 DROP TABLE attempt with resulting error message


3.28 Reinstating a previously dropped table


3.29 Creating a table from another table

© 2007 by prentice hall3-1 introduction to oracle 10g chapter 3 creating, modifying, renaming, and...

Documents

prentice hall3

prentice halltable

table constraints

table names

listings table

agents table

table comment

selected table columns