Chapter 2: Creating And Modifying Database Tables

1
Chapter 2 Creating And Modifying Database Tables

• Guide to Oracle 10g

2
Objectives

• After completing this chapter, you should be able
  to
• Use structured query language (SQL) commands to
  create, modify, and drop database tables
• Explain Oracle 10g user schemas
• Define Oracle 10g database tables
• Create database tables using SQLPlus
• Debug Oracle 10g SQL commands and use online
  help resources available through the Oracle
  Technology Network (OTN)

3
Objectives (continued)

• View information about your database tables using
  Oracle 10g data dictionary views
• Modify and delete database tables using SQLPlus

4
Introduction to SQL

• Structured query language (SQL)
• Standard query language for relational databases
• Consists of about 30 commands
• Enables users to create database objects and
  manipulate and view data
• SQL-99
• Most recent version

5
Introduction to SQL (continued)

• Structured query language (SQL) (continued)
• Basic categories for SQL commands
• Data definition language (DDL)
• Data manipulation language (DML)
• Reserved words
• SQL command words

6
Oracle 10g User Accounts

• User account
• Created for each user
• Identified using unique username and password
• User schema
• Area of database belonging to user
• Database objects
• Also called schema objects
• Objects in user schema

7
Defining Oracle 10g Database Tables

• Tables
• Primary data objects in relational database
• Constraints
• Restrictions on data values that column can store
• Oracle naming standard
• Rules that Oracle corporation has established for
  naming all database objects

8
Defining Oracle 10g Database Tables (continued)

• Oracle naming standard
• One to 30 characters long
• Contain letters, numbers, and special symbols ,
  _, and
• Begin with character
• CREATE TABLE SQL syntax
• CREATE TABLE tablename
• (columnname1 data_type,
• columnname2 data_type, )

9
Oracle 10g Data Types

• Data type
• Specifies kind of data that column stores
• Provides means for error checking
• Enable DBMS to use storage space more efficiently
• Basic types
• Character
• Number
• Date/time
• Large object

10
Character Data Types

• VARCHAR2
• Variable-length character data
• Syntax
• columnname VARCHAR2(maximum_size)
• CHAR
• Fixed-length character data
• Syntax
• columnname CHAR(maximum_size)

11
Character Data Types (continued)

• Unicode
• Standardized technique that provides way to
  encode data in diverse languages
• NVARCHAR2
• Counterpart of VARCHAR2
• Uses Unicode coding
• NCHAR
• Counterpart of CHAR
• Uses Unicode encoding

12
Number Data Types

• NUMBER
• Used for all numeric data
• Syntax
• columnname NUMBER (precision, scale)
• Precision
• Total number of digits both to left and right of
  decimal point

13
Number Data Types (continued)

• Integer number syntax
• columnname NUMBER(precision)
• Fixed-point number
• Contains specific number of decimal places
• Column declaration specifies both precision and
  scale
• Example
• price NUMBER(5, 2)

14
Number Data Types (continued)

• Floating-point number
• Contains variable number of decimal places
• Syntax
• columnname NUMBER
• Example
• s_gpa NUMBER

15
Date And Time Data Types

• Datetime data subtypes
• Store actual date and time values
• DATE
• TIMESTAMP
• Interval data subtypes
• Store elapsed time interval between two datetime
  values
• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

16
Date And Time Data Types (continued)

• DATE
• Stores dates from December 31, 4712 BC to
  December 31, AD 4712
• Default date format
• DD-MON-YY
• Default time format
• HHMISS AM
• Syntax
• columnname DATE

17
Date And Time Data Types (continued)

• TIMESTAMP
• Stores date values similar to DATE data type
• Also stores fractional seconds
• Syntax
• columnname TIMESTAMP (fractional_seconds_precision
  )

18
Date And Time Data Types (continued)

• INTERVAL YEAR TO MONTH
• Stores time interval expressed in years and
  months
• Syntax
• elapsed_years-elapsed_months

19
Date And Time Data Types (continued)

• INTERVAL DAY TO SECOND
• Stores time interval expressed in days, hours,
  minutes, and seconds
• Syntax
• elapsed_days elapsed_hourselapsed_minutese
  lapsed_seconds

20
Large Object (LOB) Data Types

• Store binary data such as
• Digitized sounds or images
• References to binary files from word processor or
  spreadsheet
• General syntax
• columnname Lob_data_type

21
Large Object (LOB) Data Types (continued)
22
Constraints

• Rules that restrict data values that can be
  entered into column
• Types of constraints
• Integrity constraints
• Value constraints
• Table constraint
• Restricts data value with respect to all other
  values in table

23
Constraints (continued)

• Column constraint
• Limits value that can be placed in specific
  column
• Irrespective of values that exist in other table
  rows
• Constraint definitions should be placed either
• At end of CREATE TABLE command after table
  columns declared
• Within each column definition

24
Constraints (continued)

• Constraint naming convention
• tablename_columnname_constraintid

25
Common ConstraintID Abbreviations
26
Integrity Constraints

• Primary key
• Syntax (within table definition)
• CONSTRAINT constraint_name PRIMARY KEY
• Syntax (at end of table definition)
• CONSTRAINT constraint_name PRIMARY KEY
  (columnname)

27
Integrity Constraints (continued)

• Foreign key
• Column constraint
• Specifies that value user inserts in column must
  exist as primary key in referenced table
• Syntax (placed at end of table definition)
• CONSTRAINT constraint_name
• FOREIGN KEY (columnname)
• REFERENCES primary_key_tablename
  (primary_key_columnname)

28
Integrity Constraints (continued)

• Foreign key (continued)
• Syntax (placed within table definition)
• CONSTRAINT constraint_name
• REFERENCES primary_key_tablename
• (primary_key_columnname)
• Composite key
• Syntax
• CONSTRAINT constraint_name
• PRIMARY KEY (columnname1, columnname2 )

29
Integrity Constraints (continued)

• Value constraints
• Column-level constraints
• Restrict data values that users can enter
• Commonly used value constraints
• CHECK conditions
• NOT NULL constraint
• DEFAULT constraint
• UNIQUE constraint

30
Creating Database Tables Using SQLPlus

• Start SQLPlus
• Type username and password
• Type SQL commands at SQL prompt
• End each command with semicolon ()
• Press Enter to submit commands

31
Creating Database Tables Using SQLPlus
(continued)

• SQLPlus interpreter
• Checks command for syntax errors
• Submits command to database
• SQL commands are not case sensitive
• When creating database tables that contain
  foreign key references
• Must first create table in which foreign key is
  primary key

32
SQL Command to Create the LOCATION Table
33
Creating and Editing SQL Commands Using a Text
Editor

• Good approach for entering commands
• Type commands into text editor such as Notepad
• Copy commands, then paste into SQLPlus
• Execute commands
• Script
• Text file that contains several related SQL
  commands

34
Using Oracle Online Help Resources to Debug SQL
Commands

• Syntax error
• SQLPlus interpreter displays error information
• Line number within command that caused error
• Position of error within line
• Error code and description of error

35
Using Oracle Online Help Resources To Debug SQL
Commands (continued)

• Oracle 10g error codes have
• 3-character prefix (such as ORA)
• 5-digits
• Causes of SQL command errors are not always
  readily apparent
• Need to retrieve more information about error
• Connect to Oracle Technology Network (OTN) Web
  Site and search for error code

36
Using Oracle Online Help Resources To Debug SQL
Commands (continued)

• Last resort debugging technique
• Create table multiple times
• Each time adding column declaration
• Repeat process until you find declaration causing
  error
• Drop table command
• To delete table
• syntax DROP TABLE tablename

37
Exiting SQLPlus

• Type exit at SQL prompt
• Click File on menu bar, and then click Exit
• Click Close button on program window title bar

38
Creating a Table with a Foreign Key Constraint
39
Viewing Information about Tables

• DESCRIBE command
• View column names and data types of table
• Syntax
• DESCRIBE tablename
• Oracle 10g data dictionary
• Consists of tables that contain information about
  structure of database

40
Viewing Information about Tables (continued)

• Oracle10g data dictionary (continued)
• System creates data dictionary in user schema
  named SYS
• Users do not directly manipulate data dictionary
• View
• Database object DBMS bases on actual database
  table
• Enables DBMS to present table data in different
  format based on needs of users

41
Viewing Information about Tables (continued)

• Data dictionary views categories
• USER
• ALL
• DBA
• Syntax
• SELECT view_columnname1, view_columnname2 FROM
  prefix_object

42
Database Objects with Data Dictionary Views
43
Modifying and Deleting Database Tables

• Plan tables carefully to avoid having to change
  structure of database tables later
• Unrestricted action
• Some specifications of tables can always be
  modified
• Restricted action
• Table specifications that can be modified only in
  certain situations

44
Unrestricted Actions when Modifying Database
Tables
45
Deleting and Renaming Existing Tables

• DROP TABLE command
• Delete table
• Syntax
• DROP TABLE tablename
• DROP TABLE tablename CASCADE CONSTRAINTS

46
Deleting and Renaming Existing Tables (continued)

• RENAME TO command
• Syntax
• RENAME old_tablename TO new_tablename

47
Adding Columns to Existing Tables

• Add new column to table
• Syntax
• ALTER TABLE tablename
• ADD(columnname data_declaration constraints)

48
Modifying Existing Column Data Definitions

• Modify existing columns data declaration
• Syntax
• ALTER tablename
• MODIFY(columnname new_data_declaration)

49
Deleting a Column

• Data stored in deleted column removed from
  database
• Syntax
• ALTER TABLE tablename
• DROP COLUMN columnname

50
Renaming a Column

• Syntax
• ALTER TABLE tablename
• RENAME COLUMN old_columnname TO new_columnname

51
Adding and Deleting Constraints

• Add constraint to existing table
• Syntax
• ALTER TABLE tablename
• ADD CONSTRAINT constraint_name constraint_definiti
  on
• Remove existing constraint
• Syntax
• ALTER TABLE tablename
• DROP CONSTRAINT constraint_name

52
Enabling and Disabling Constraints

• Constraint enabled
• DBMS enforces constraint when users attempt to
  add new data to database
• Disable existing constraint syntax
• ALTER TABLE tablename
• DISABLE CONSTRAINT constraint_name
• Enable existing constraint syntax
• ALTER TABLE tablename
• ENABLE CONSTRAINT constraint_name

53
Summary

• SQL commands include
• Data description language (DDL) commands
• Data manipulation language (DML) commands
• Each user account owns table and data objects in
  own area of database
• Called user schema
• When creating database table specify table name,
  column names, data type, and column sizes

54
Summary (continued)

• Constraints restrict data values that users can
  enter into database columns
• When SQL commands have errors interpreter
  reports
• Line number
• Position of character causing error
• Returns error code and description
• Use describe command to display table info