Chatper 10-Using DDL Statements to Create and Manage Tables

Objectives

• After completing this lesson,you should be able to do the following:
• Categorize the main database objects
• Review the table structure
• List the data types that are available for columns.
• Create a simple table
• Explain how constraints are created at the time of table creation.
• Describe how schema objects work.

Lesson Agenda

• Database objects
  •  -Naming rules
• CREATE TABLE statement
  •  -Access another user`s tables
  •  -DEFAULT option
• Data types
• Overview of constrains:NOT NULL,PRIMARY KEY,FOREIGN KEY,CHECK constraints
• Creating a table using a subquery
  • ALTER TABLE
  •  -Read-only tables
• DROP TABLE statement 

Naming Rules

Table names and column names:

• Must begin with a letter
• Must be 1-30 character long
• Must contain only A-Z,a-z,0-9,_,$,and #
• Must not duplicate the name of another object owned by the same user
• Must not be an Oracle server-reserved word

CREATE TABLE Statement

• You must have:
•  -CREATE TABLE privilege.假如你想在别人的schema里面创建表的话，你必须用后CREATE ANY TABLE privilege.
•  -A storage area 

CREATE TABLE [schema.]table

 (column datatype [DEFAULT expr][, ...]);

• You specify:
  •  -Table name
  •  -Column name,column data type,and column size
• Demo:CREATE A SMIPLE TABLE

Referencing Another User`s Tables

• Tables belonging to other users are not in the user`s schema.
• You should use the owner`s name as a prefix to those tables.

DEFAULT Option

• Specify a default value for a column during an insert

 ...hire_date DATE DEFAULT SYSDATE,...

• Literal values,expressions,or SQL functions are legal values
• Another column`s name or a pseudocolumn are illegal values
• The default data type must match the column data type. 

demo:
CREATE TABLE T(
        ID NUMBER(8),
        NAME CHAR(10),
        SEX CHAR(1) DEFAULT '1'
    );

Oracle Data Types

可参考号文章http://www.cnblogs.com/arcer/archive/2013/04/11/3014298.html

Including Constraints

• Constraints enforce rules at he table level
• Constraints prevent the deletion of a table if there are dependencies.
• The following constraint types are valid:
  • -NOT NULL
  • -QUIQUE
  • -PRIMARY KEY
  • -FOREIGN KEY
  • -CHECK

Constraint Guidelines

• You can name a constraint,or the Oracle server generates a name by using SYS_Cn format.
• Create a constraint at either of the following times:
  •  -At the same time as the creation of the table
  •  -After the creation of the table
• Define a constraint at he column or table level
• View a constraint in the data dictionary

Define Constraints

Syntax: 　　

CREATE TABLE [schema.]table (  column datatype [DEFAULT expr]  [column_constraint],  ...  [table_constraint][,...] ); 

　  Column-level constraint syntax: column [CONSTRAINT constraint_name] constraint_type,

 　 Table-level constaint syntax: column,... [CONSTRAINT constraint_name] constraint_type (column,...),

Demo

Example of a column-level constraint: 　　

CREATE TABLE employees(  employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY,  first_name VARCHAR2(20),  ... )

Example of a table-level constaint: 　　

CREATE TABLE employees(  employee_id NUMBER(6),  first_name VARCHAR2(20),  ...  job_id  VARCHAR2(10) NOT NULL,  CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID) );

UNIQUE Constraint 假如一列是Unqiue constraint，那么如果这一列中存在两个以上的NULL值得话，是否 违反Unique constraint呢？答案，否；