Oracle Tables: Creation

1
Oracle Tables Creation Modification

• Data Definition Language Statements
• Naming Rules and Conventions
• Various Data Types
• Integrity and Value Constraints
• Creating, Altering, Removing,
• Renaming and Viewing Tables

2
Naming Rules

• Naming Rules for Tables, Attributes and Other
  Objects
• Name can be 1 to 30 characters long
• Name must start with a letter (a-z, A-Z)
• Name may contain letters, numbers (0-9), dollar
  sign (), number sign () and underscore (_)
• Spaces are not allowed in a name
• Other special characters are not allowed
• Keywords are not allowed
• Names are not case-sensitive

3
Valid Name or not?

• STUDENT
• STUDENT_COURSE_REGISTRATION_TABLE
• MAJOR CODE
• MAJOR_CODE
• X
• CREATE
• PROJECT2000
• PROJECT2000
• STUDENTREGTABLE
• STUDENT
• STUDENTS_ID

4
Data Types

• Each attribute is assigned a data type.
• VARCHAR2.
• variable-length character type
• default size is 1, maximum size is 4000
• size is specified within parentheses
• spaces are not added for shorter values
• error occurs for longer values
• most appropriate type for an attribute whose
  values do not have a fixed length, For example,
  LastName, City.

5
Data Types (cont.)

• CHAR
• Fixed-length character value
• default length is 1, maximum length is 2000
• trailing spaces are added for shorter values
• error occurs for longer values
• most appropriate type for an attribute whose
  values have a fixed length, For example,
  MiddleInitial, ZipCode, Soc_Sec_Number.

6
Data Types (cont.)

• NUMBER
• For integer, fixed-decimal and floating-decimal
  numbers
• Used for any attribute that is going to be used
  in math caculations, e.g. SALARY, COMMISSION, or
  PRICE
• precision and scale can be specified
• Precision is the total number of digits in the
  number, both to the left and to the right of the
  decimal point, whereby the decimal point is not
  counted
• Scale is the total number of digits to the right
  of the decimal point
• The precision can range from 1 to 38.
• The scale can range form -84 to 127

7
Data Types (cont.)

• Examples for NUMBER
• Integer
• EmployeeId has values of 111, 246, 123, 433,
• NUMBER(3)
• fixed-point decimal number
• PRICE has values of 2.95, 3.99, 24.99,
• NUMBER(4,2)
• What is the maximum price for this data type?

8
Data Types (cont.)

• DATE
• For date and time values, range is between
  1/1/4712 B.C. and 12/31/9999 A.D.
• Default date format is DD-MON-YY, and default
  time format is HHMMSS A.M.
• If only date is entered, time defaults to
  midnight.
• If only time is entered, the date defaults to the
  first day of the current month.
• For example, DateOfBirth, HireDate.
• Why AGE is not advisable to user in a table?

9
Common and Advanced Data Types
10
Constraints

• Constraints enforce rules at table level.
• Constraints make a database a true relational
  database, because they are used
• to implement integrity rules of a RDB and
• to implement data integrity at the individual
  attribute level

11
Types of Constraints

• Integrity Constraints
• PRIMARY KEY and FOREIGN KEY define the primary
  key and the foreign keys with the table and
  primary key it references
• Value Constraints
• NOT NULL, UNIQUE and CHECK define if NULL values
  are disallowed, if UNIQUE value are required, and
  if only certain values are allowed in an attribute

12
Naming a Constraint

• Same naming rules discussed in the earlier slide.
• Constraint name must be unique under a users
  schema.
• How to achieve it systematically?

13
Naming a Constraint (continued)
 
General Naming Convention lttablenamegt_ltcolumn_name
gt_ltconstrainttypegt For example, Dept_DeptId_pk Emp
loyee_DeptId_fk
 
14
Defining a Constraint

• A constraint can be defined at the time of
  tables creation, or can be added to the table
  afterwards.
• Column Level.
• Defined along with an attributes definition.
• All constraints except for the FOREIGN KEY and
  composite PRIMARY KEY can be defined at the
  column level. Why?
• The general syntax is
• Column datatype CONSTRAINT constraint_name
  constraint_type,

15
Defining a Constraint (continued)

• Table Level constraint
• references one or more columns/attributes
• is defined separately from the definitions of the
  columns/attributes
• defined after all columns/attributes definitions.
• All constraints except for the NOT NULL
  constraint can be defined at the table level.
• General syntax is
• CONSTRAINT Constraint_name constraint_type
  (Column, ),

16
Primary Key Constraint

• Is also known as the entity integrity constraint
• It creates a primary key for the table
• A table can have only one primary key constraint
• Primary key cannot have null value and can only
  have unique value

17
Examples to Primary Constraint

• DEPT table used DeptId column as a primary key
• At the column level,
• DeptId NUMBER (2) CONSTRAINT dept_deptid_pk
  PRIMARY KEY,
• At the table level,
• CONSTRAINT dept_deptid_pk PRIMARY KEY (DeptId),
• If a table uses composite key, the key can only
  be declared at the table level. No column level
  primary key constraint in this case!
• E.g. DEPENDENT table,
• CONSTRAINT dependent_emp_dep_pk PRIMARY KEY
  (EmployeeId, DependentId),

18
Foreign Key Constraint

• Is also know as referential integrity constraint.
• Uses a column or columns as a foreign key and
  establishes a relationship with the primary key
  of the same table or another table.
• E.g. FacultyId in the STUDENT table references
  the primary key FacultyId in the FACULTY table.
• CONSTRAINT student_facultyid_fk FOREIGN KEY
  (FacultyId) REFERENCES faculty (FacultyId),
• One may put ON DELETE CASCADE thereafter to allow
  deletion of a record/row in the parent table and
  deletion of the dependent rows/records in the
  child table.

19
NOT NULL Constraint

• This constraint ensures that the column has a
  value and the value is not a NULL (unknown or
  blank) value.
• Is space or 0 a NULL value?
• Do we need a NOT NULL constraint for primary key?
• is a foreign allowed to have a NULL value?
• This constraint cannot be entered at the table
  level. Why?

20
Examples of NOT NULL Constraint

• If you do not want to the name column in
  FACULTY table have a null value, the column level
  constraint is defined by
• name VARCHAR2 (15) CONSTRAINT faculty_name_nn NOT
  NULL, or
• name VARCHAR2 (15) NOT NULL,
• in the 2nd case Oracle will name it with SYS_Cn
  format.

21
UNIQUE KEY Constraint

• This constraint requires that every value in a
  column or set of columns be unique.
• If it is applied to single column, the column is
  known as unique key
• If it is applied to a set of columns, the group
  is known as the composite unique key
• Null values are allowed here, unless NOT NULL is
  also applied

22
Examples to UNIQUE KEY Constraint

• DeptName column in DEPT should not have duplicate
  values.
• At table level
• CONSTRAINT dept_dname_uk UNIQUE (DeptName),
• At column level
• DeptName VARCHAR2 (12) CONSTRAINT dept_dname_uk
  UNIQUE,
• What about composite unique key?

23
CHECK constraint

• This constraint defines a condition that every
  row must satisfy.
• There can be more than one CHECK constraint on a
  column
• This constraint can be defined at column level as
  well as at table level
• DeptId NUMBER (2) CONSTRAINT dept_deptid_cc CHECK
  ((DeptId gt10) AND (DeptIdlt99)), or
• CONSTRAINT dept_deptid_cc CHECK ((DeptId gt10)
  AND (DeptIdlt99)),

24
DEFAULT value (not a constraint)

• DEFAULT value ensures that a particular column
  will always have a value when a new row is
  inserted
• It gets overwritten if a user enters another
  value
• It is used if a null value is inserted.
• E.g. if most of the students live in Arkansas,
  AR can used as a default value for the State
  column in the STUDENT table
• State CHAR (2) DEFAULT AR,

25
CREATE TABLE Statement
General Syntax CREATE TABLE schema.
tablename (column/attribute1 datatype
CONSTRAINT constraint_name constraint_type, (co
lumn/attribute2 datatype CONSTRAINT
constraint_name constraint_type ,
CONSTRAINT constraint_name constraint_type (
column, ), )
schema is optional, and it is same as the users
name. tablename is the name of the table given by
the user. column/attribute is the name of a
single column/attribute. datatype is the columns
data type and size. constraint_name is the name
of constraint. constraint_type is the integrity
or value constraint. Can you list all constraint
types?
26
Example to CREATE TABLE
SQLgt CREATE TABLE student 2 (StudentId
CHAR(5), 3 Last VARCHAR2 (15) NOT
NULL, 4 First VARCHAR2 (15) NOT
NULL, 5 Street VARCHAR2 (25), 6
City VARCHAR2 (15), 7
State CHAR (2), 8 Zip CHAR
(5), 9 StartTerm CHAR (4), 10
BirthDate DATE, 11 FacultyId NUMBER
(3), 12 MajorId NUMBER (3), 13
Phone CHAR (10), 14 CONSTRAINT
student_studentid_pk PRIMARY KEY (StudentID))
27
Oracle Data Dictionary

• Oracle data dictionary stores all information
  about the objects of the DB (meta-data/meta-inform
  ation).
• Table management related meta-data
• Names of tables
• Names of attributes
• Domain of the attributes
• View names and view definition
• Integrity constraints

28
Oracle Data Dictionary (cont.)

• User management related meta-data
• Name of the user
• Storage area and quote
• Permissions/privileges
• Query processing related meta-data
• Number of tuples
• Index names
• Indexed tables
• Indexed attributes
• Type of the indexes

29
Oracle Data Dictionary (cont.)

• Data dictionary is a part of the database
• Meta-information is stored in system tables
• The system tables are accessible like other
  tables.
• The objects managed by the dictionary
• Tables, views, user,

30
Oracle System Tables
31
Examples of Data Dictionary
SQLgt desc dictionary Name
------------------- TABLE_NAME
COMMENTS
SQLgt select count() from dictionary
COUNT() ---------- 359
SQLgt select TABLE_NAME, COMMENTS from dictionary
2 where TABLE_NAME like 'TABLE' TABLE_NAME
COMMENTS -----------------------------------------
--------------------------------------------------
--------- ALL_ALL_TABLES Description of all
object and relational tables accessible to the
user ALL_NESTED_TABLES Description of nested
tables in tables accessible to the
user ALL_OBJECT_TABLES Description of all object
tables accessible to the user
32
1 More Example of Data Dictionary
SQLgt select TABLE_NAME, OWNER from
ALL_TABLES TABLE_NAME OWNER ------------------
-------------------------------------------
------------------------------ DUAL SYS SYSTEM_
PRIVILEGE_MAP SYS TABLE_PRIVILEGE_MAP SYS STMT_A
UDIT_OPTION_MAP SYS AUDIT_ACTIONS SYS PSTUBTBL
SYS DEF_TEMPLOB SYSTEM OGIS_SPATIAL_REFEREN
CE_SYSTEMS MDSYS MDDICTVER
MDSYS CS_SRS
MDSYS HELP
SYSTEM TABLE_NAME OWNER --------------------
-----------------------------------------
------------------------------ STUDENT
XWXU SAMPLE
XWXU 13 rows selected.
33
Viewing Information
Viewing A Users Table Names. SELECT table_name
FROM user_tables SELECT FROM
user_tables Viewing STUDENT Tables
Structure. DESCRIBE STUDENT Why we did not need a
semicolon at the end? Viewing STUDENT Tables
Constraints. SELECT CONSTRAINT_NAME,
CONSTRAINT_TYPE             FROM
USER_CONSTRAINTS WHERE TABLE_NAME
STUDENT
34
Altering a Table

• In a perfect scenario, the tables that you create
  will not need any structural modifications. In
  reality, that is not the case. Even perfect
  tables need changes.
• Some modifications are allowed, some are not.
• Modifications allowed.
• Add a new attribute
• Delete foreign key constraint
• Delete primary key constraint, which also removes
  any references to it from other tables in the
  database
• Increase size of an attribute. For example,
  VARCHAR2 (15) can be changed to VARCHAR2 (20)

35
Altering a Table (cont.)

• Modifications allowed with restrictions
• Adding a constraint provided existing data
  already conform to the constraint
• Adding a foreign key constraint is allowed only
  if the current values are NULL or exist in the
  referenced tables primary key.
• Adding a primary key constraint is allowed if the
  current values are NOT NULL and are unique.
• Adding a UNIQUE constraint is possible if the
  current data values are unique.
• Adding CHECK constraint is possible if the
  current data values comply with the new
  constraint.
• Adding a DEFAULT value is possible if there is no
  data in the column.
• Decrease an attributes size or change its data
  type provided there is no data in that attribute.

36
Altering a Table (cont.)

• Modifications not allowed
• Changing an attributes name or
• Removing an attribute.
• An attribute can be removed in Oracle8i
• What to do if you really need to make
  modification which is not allowed?
• DROP it and re-create it!

37
ALTER TABLE Statement
General Syntax ALTER TABLE TableName ADD/MODIFY/D
ROP attribute DataType ALTER TABLE
TableName ADD/DROP CONSTRAINT ConstraintName
ConstraintType (column, ) REFERENCES TableName
(ColumnName) For example, ALTER TABLE student
ADD SocSecNum CHAR(9) ALTER TABLE student MODIFY
Zip VARCHAR2(10) ALTER TABLE student ADD
CONSTRAINT student_facultyid_fk FOREIGN KEY
(FacultyId) REFERENCES faculty (FacultyId)
38
Dropping a Column

• Oracle 8 does not allow you to remove a column,
  but Oracle 8i does.
• Only one column can be dropped at a time.
• The dropped column may or may not contain any
  data.
• There must be at least one column left in the
  table.
• It is not possible to recover a dropped column
  and its data.
• ALTER TABLE tablename DROP COLUMN columnname
• Oracle 8i also allows a user to mark columns as
  unused
• ALTER TABLE tablename SET UNUSED (columnname)
• Drop all unused columns
• ALTER TABLE tablename DROP UNUSED COLUMNS

39
Other DDL Statements
Dropping a Table. (If there is no reference to
it) DROP TABLE TableName Renaming a
Table. RENAME OldName TO NewName Truncating a
Table. (Removes all rows) TRUNCATE TABLE
TableName
40
Dropping a Table

• Drop a table from you database if
• The table is not needed
• The table structure has too much flaws
• Note when a table is dropped, all data and the
  tables structure are permanently deleted.
• All associated indexes are removed
• Tables views and synonyms become invalid
• DROP cannot be reversed. It will not question you
  with an Are you sure?
• You can drop a table only if you are the owner or
  have a higher privilege
• DROP TABLE tablename
• E.g. DROP TABLE sample

41
Rename a Table

• You can rename a table provide you are the owner
  of the table
• RENAME oldtablename TO newtablename
• E.g.
• RENAME sample to example

42
Truncating a Table

• Truncating a table is removing all records/rows
  from the table.
• The structure of the table stays intact.
• To perform truncating a table you must be the
  owner of the table with the DELETE TABLE
  privilege.
• Truncation operation is not reversible.
• It releases storage space.
• TRUNCATE TABLE tablename

43
Error Codes

• If there is an error happened, the Oracle DBMS
  will return a error code which is not very user
  friendly.
• Use online documentation to get an explanation of
  the error
• http//goofy.is.ualr.edu7777/oracledocs/index.htm
  
• Click on Oracle8i Server and SQLPlus
• And further click on Oracle8i Error Messages

44
Spooling

• Redirect all statements, queries, commands, and
  results to a file for later review or printout.
• To start spooling, use spool commend in
  SQLPlus prompt
• SQLgt SPOOL file_name.ext
• To stop spooling
• SPOOL OFF

45
Assignment

• Database systems using oracle
• Lab Activity (pp 77-78) 1 2
• Spooling the results and turn a hardcopy
• Due 10/27/03!