//
you're reading...
SQL

A Short Guide Oracle SQL [II] – DDL & Constraints

Welcome to the second Part of SQL fundamentals. This part basically deal with DDL statements and constraints.

Describe a Table’s Structure

Use the DESCRIBE [DESC] to get the structure of a table, view/data dictionary views or synonym etc. It’s a SQL*Plus command.

DESCRIBE [schema].object_name;

DESCRIBE emp
Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    


DESCRIBE scott.emp
Name     Null     Type         
-------- -------- ------------ 
EMPNO    NOT NULL NUMBER(4)    
ENAME             VARCHAR2(10) 
JOB               VARCHAR2(9)  
MGR               NUMBER(4)    
HIREDATE          DATE         
SAL               NUMBER(7,2)  
COMM              NUMBER(7,2)  
DEPTNO            NUMBER(2)

DESCRIBE can be used on SQL Developer as SQL Developer supports many of the SQL*Plus commands.

A more detailed DESCRIBE syntax is –

DESCRIBE [schema.]object_name[@db_link];

DB Link – A name of the remote database where the object resides.

Data Definition Language Statements

—- DDL statements used to create, modify and remove the database objects.
—- A DDL statement implicitly committed.

Creating Table

You can create table using CREATE TABLE command or using subquery (AS clause).

CREATE TABLE [schema.]table 
       ({column data type  [DEFAULT expr] [column_constraint] | table_constraint } 
        [,{column data type  [DEFAULT expr] [column_constraint] table_constraint} ] … ) 
        [AS subquery]

A simple CREATE TABLE statement looks like this-

CREATE TABLE student
( 
   studentID		NUMBER(5),
   s_first_name		VARCHAR2(15),
   s_middle_name  	VARCHAR2(15),
   s_last_name    	VARCHAR2(15),
   s_dob          	DATE,
   s_sex          	CHAR,
   s_reg_date		DATE,
   s_add_street   	VARCHAR2(20),
   s_add_house_no 	NUMBER(4),
   s_add_city   	VARCHAR2(20),
   s_add_pin_code   	NUMBER(6)
);

More detail about CREATE TABLE and its syntax can be found on SQL Language Reference – CREATE TABLE.

Oracle Object name convention

  • A table or column name can be 1 to 30 characters (bytes) in length and must begin with a letter, A-Z or a-z. however, names of databases are limited to 8 bytes and database links can be as long as 128 bytes.
  • An Object name may contain letters, numbers. Use of the special character _, $ and # are also legal. But it can’t contain any blank spaces, hyphen or any other characters though it can be override using double quotes.
  • Case-insensitive; e.g., student, STUDENT, and StUDenT are all the same table, if there is no use of Double quotes.
  • A table or view cannot have the name of another table or view under the same schema.
  • More than one object cannot share the same name under the same namespace.
  • An Oracle reserved word cannot be used as an object name.
  • A total of 1000 column can exist in a table.

Data Type

A table must have a datatype for each of its columns. The data types are used to specify the type of data that will be stored in each column of the table.

CHAR(n)

    • Stores fixed length alphanumeric data incl. special characters (_, $ and #).
    • A CHAR column can store up to 2,000 characters (bytes).
    • The minimum size and default for CHAR is 1 (if omitted).

VARCHAR2(n)

    • The most commonly used datatype.
    • VARCHAR2 stores variable-length alphanumeric data, incl. special characters (_, $ and #).
    • The minimum size of VARCHAR2 is 1 character (byte) and can store up to 4,000 characters (bytes). You must specify minimum size for VARCHAR2 column.

The main difference between VARCHAR2 and CHAR is that the CHAR data type is a fixed-length data type, and any unused spaces padded with blank spaces.

For example, a column defined as CHAR(7) and containing only the five-character-string ADMIN, will have two blank characters padded at the end to make the total length of 7. But the same column is stored with a VARCHAR2(7) datatype, it stores five characters only.

DATE

    • The DATE data type stores date and time information.
    • The default display format is defined by NLS_DATE_FORMAT. The standard date format is DD-MON-RR.
    • The size is 7 bytes (fixed) or can be zero (if empty).
    • It stores century, year, month, day, hour, minute, and second information.
    • It can store date range from January 1, 4712 BC, to December 31, 9999 AD.

NUMBER(p,s)

    • Stores only numeric data; no text, hyphens, or dashes are permitted.
    • NUMBER(p,s) where p is the precision and s is the scale.
    • A column defined as NUMBER(7,2) can have a maximum of five digits before the decimal point and two digits after the decimal point. A value of 12345.678 is stored as 12345.68, a value of -99998.999 stored as -99999 and storing value 123456.78 will through ORA-01438 error.
    • A scale can be negative or positive. If we specify a column’s datatype as NUMBER(7,-2) the value 12345.67, stored as the 12300.

CLOB

    • Use CLOB datatype , which allows you, to store to store large amounts of textual data.
    • Maximum size of data it can store can be calculated using (4 gigabytes – 1) * (database block size).
    • A table can have more than one CLOB column.

LONG

    • LONG data type supported only for backward compatibility.
    • Only one LONG column per table is permissible and Character functions can’t be used on a LONG column.
    • A constraint can’t be defined on LONG column and it can’t be used with GROUP BY, ORDER BY, DISTINCT, CONNECT BY clause. Use BLOB, CLOB or LOB instead.

Other most generally used data types are TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, BLOB, RAW, BFILE etc.

CONSTRAINTS

Rules that enforce business practices, rules etc to ensure integrity of data. A constraint name cannot exceed 30 characters. A constraint can be defined at the time of table creation or added to existing table using ALTER TABLE command.

PRIMARY KEY

    • It is used to uniquely identify a row in a table. A primary key column can’t be NULL, and the data in the column must be unique.
    • A unique index automatically created by Oracle to enforce this if one does not exist.
    • A table can have only one Primary Key constraint.
    • It is possible to apply PK constraint on more than one column. This is called Composite Primary Keys. The combination of values in the composite PK must be unique and all column(s) must have some value in it.

FOREIGN KEY

    • It enforces the Parent-child relationship between tables. The constraint ensures that if a value is entered in a specified column of a child table, there must be a corresponding value exists in the parent table or the record isn’t inserted.
    • The columns don’t have to assign identical name but they must have same data type and length.
    • A FK can have NULL value in it.
    • It is recommended to index foreign key(s).

NOTE
A parent record can’t be deleted if corresponding child rows exists. In these case when there is a child record use ON DELETE CASCADE clause in constraint definition. ON DELETE CASCADE automatically deletes corresponding child row(s) when you remove a parent record.

CONSTRAINT  cl_proj_fk_stu_ID  FOREIGN KEY(cl_proj_stu_ID)
           REFERENCES student(studentID)
           ON DELETE CASCADE

Another clause ON DELETE SET NULL converts the corresponding foreign key value to NULL.

UNIQUE

    • Ensures that all data values stored in a specified column are unique compared to other data of the column.
    • Unique constraint allows NULL values to the column.
    • A unique index is created on the column(s) on which Unique constraint is applied by oracle, if one not existed.

CHECK

    • Checks the whether or not specified condition is true before the data being inserted to a table.
    • The condition specified in the CHECK cannot refer to the values of other rows.
    • You cannot use functions with CHECK Constraint that are not deterministic and use of Pseudo-column is not allowed.

NOT NULL

    • Ensures that a specified column always contain a value.

Constraints can be defined at Column level or Table level.

Column Constraint: A column constraint is specified as part of a column definition and applies only to that column.
Table Constraint: A table constraint is declared independently from a column definition and can apply to more than one column in a table.

Note

  • Table constraints must be used when constraint is applied for more than one column of a table.
  • All constraint except NOT NULL constraint can be created at table level. The NOT NULL constraint can only be defined at column-level.
  • Multiple constraints can be assigned to a single column.
  • USER_CONSTRAINTS and USER_CONS_COLUMNS data dictionary views can be used to verify existing constraints.
  • A constraint can be disabled or enabled using DISABLE and ENABLE clause with ALTER TABLE command.
  • If you want to modify a constraint, you first drop it and then re-create it.
  • The VALIDATE and NOVALIDATE clause in the constraints indicate whether or not existing data must conform to the constraint.
  • By default constraints are checked during the execution of each statement. This is called Nondeferrable Constraint. You can change this behavior by declaring a constraint using DEFERRABLE clause. In this case there will be no constraint checking until the transaction is completed.
  • To make an existing constraint Deferrable, you must drop and recreate the constraint with Deferrable clause.
  • You can mark a Deferrable constraint as INITIALLY IMMEDIATE or INITIALLY DEFERRED.

DEFAULT clause – DEFAULT specify value to column(s) if no value is supplied by INSERT statement.

The following two table definitions gives us a detailed look at CREATE TABLE statement.

CREATE TABLE student
( 
   studentID		NUMBER(5),
   s_first_name  	VARCHAR2(15)	CONSTRAINT  stu_nn_first  NOT NULL,
   s_middle_name  	VARCHAR2(15),
   s_last_name    	VARCHAR2(15)	CONSTRAINT  stu_nn_last  NOT NULL,
   s_dob          	DATE		CONSTRAINT  stu_chk_dob  CHECK(s_dob > 01-JUL-1990),
   s_sex          	CHAR,
   s_reg_date		DATE		DEFAULT	   SYSDATE,
   s_hostel_no		NUMBER(2),
   s_room_no		NUMBER(3),
   s_pin_code   	NUMBER(6),
     CONSTRAINT  stu_pk_ID  PRIMARY KEY(studentID),
     CONSTRAINT  stu_chk_sex  CHECK(s_sex in ('M','F')),
     CONSTRAINT  stu_uk_hos_room  UNIQUE(s_hostel_no,s_room_no)
);

The statement creates a table “STUDENT” with one Primary Key studentID, two NOT NULL constraint on column s_first_name and s_last_name, two CHECK constraint to verify data in the column s_dob and s_sex, a DEFAULT value for column s_reg_date and one UNIQUE constraint on the combination of column s_hostel_no and s_room_no. Every constraint explicitly given a name.

CREATE TABLE class_project
( 
   cl_project_code	VARCHAR2(5),       
   cl_project_title	VARCHAR2(50)	CONSTRAINT  cl_proj_nn_title  NOT NULL,
   cl_project_marks	NUMBER(5,2),
   cl_proj_stu_ID	NUMBER(5),
   cl_proj_mod		NUMBER(3),
     CONSTRAINT  cl_proj_pk_code  PRIMARY KEY(cl_project_code),
     CONSTRAINT  cl_proj_fk_stu_ID  FOREIGN KEY(cl_proj_stu_ID)
           REFERENCES student(studentID)
);

This command creates another table called “CLASS_PROJECT” with one Primary Key cl_project_code, one NOT NULL constraint on column cl_project_title and one FOREIGN KEY constraint defined on the column cl_proj_stu_ID which reference to the its parent table column “STUDENT.studentID”.
Here also constraints have been explicitly given a name.

Use USER_TABLES data dictionary view to get the information about the tables owned by you/current user, ALL_TABLES view gives you information about table to which you have privilege and DBA_TABLES shows information about all the tables in database

With Oracle 11g, you can mark a column as VIRTUAL Column.

Creating Table from Existing Table

The other method for creating a table is using subquery with AS clause. This is also known as CTAS.

CREATE TABLE table_name[(column_list)] 
     AS (subquery); 
CRAETE TABLE copy_student
AS 
SELECT * FROM student;

If you want to give different name to column(s) in new table from the existing ones, use the column list in the CREATE TABLE statement otherwise it can be omitted.

If you provide a column list with CREATE TABLE statement make sure you define a column corresponding to the every column returned by the subquery. Also column must be in the same order as the column(s) listed in the subquery’ SELECT statement.

CREATE TABLE copy_cls_project(project_code, project_title, project_marks, student_id, prj_mod)
AS
SELECT * FROM class_project;

CREATE TABLE info_cls_project
AS
(SELECT cl_project_code, cl_project_title, cl_proj_stu_id, cl_proj_mod
 FROM class_project);

The following statement only create the structure for the new table from the existing one because the condition specified in the WHERE clause always be false.

CREATE TABLE copy_student
AS 
SELECT * FROM student
WHERE 1=2;

The WHERE clause restricted rows according to the condition specify.

    • CTAS approach will not work if the query refers to columns of LONG datatype.
    • Tables created with CTAS approach never inherit constraint with the exception of explicitly defined NOT NULL constraint. All other constraint must be defined on table as per requirement.

Modifying or Altering Table Structures

Change the structure of table using ALTER TABLE statement.
For example, adding or removing of column(s) or constraints, modify the width of column(s) datatype or rename a column etc.

ALTER TABLE student  
  ADD s_add_street  VARCAHR2(20); 

ALTER TABLE class_project  
  MODIFY cl_project_title(100); 

ALTER TABLE class_project  
  ADD CONSTRAINT cl_proj_fk_mod FOREIGN KEY (cl_proj_mod)  
    REFERENCES faculty (f_staff_ID); 

ALTER TABLE student  
  DROP CONSTRAINT stu_chk_dob; 

ALTER TABLE student  
  DROP (s_add_street); 

ALTER TABLE class_project  
  RENAME COLUMN cl_proj_stu_id TO cl_proj_stuid; 

You can drop multiple columns each separated by comma.

ALTER TABLE student  
  DROP (s_add_street, s_pin_code); 

SET UNUSED clause can be used with ALTER TABLE statement to mark a column as unused instead of dropping it.

ALTER TABLE student
  SET UNUSED (s_dob);

Later unused column can be dropped by DROP UNUSED COLUMN clause.

If a table contains only one column and you want to drop that column, operation will failed with ORA-12983.

Renaming a Table

A table, view or synonym can be renamed using RENAME command.

RENAME copy_student TO new_student;

Another way to RENAME a table is

ALTER TABLE copy_student RENAME TO new_student; 

Removing Table

You can use DROP TABLE command to remove a table from the database. DROP TABLE command removes the specified table, its data along with any index, constraint etc if any.

DROP TABLE tablename [CASCADE CONSTRAINTS] [PURGE]

DROP TABLE new_student;

Once a table has been dropped, it can no longer accessed by any command. Starting with Oracle Database 10g, a dropped table can be recovered using FLASHBACK DROP command if table is dropped without PURGE clause. You cannot recover a table which is dropped using PURGE clause.

CASCADE CONSTRAINTS clause used to remove all referential integrity constraints

Truncating Table

TRUNCATE TABLE statement removes the entire contents of the specified table but its definition/structure remains intact. There is no way to recover the data. It de-allocates all space occupied by the rows of the specified table.
In other words, “It resets the high-water mark in the table and all indexes.

TRUNCATE TABLE <table_name>;

TRUNCATE TABLE new_student;

TRUNCATE can be thought as DELETE command but it does not have a WHERE clause and since as a DDL statement it automatically issues an implicit COMMIT. It also works fast (irrespective of table size) comparison to DELETE since the operation does not generate any UNDO information.

To use TRUNCATE you must have DROP ANY TABLE privilege. (A very powerful indeed, generally used by DBAs.)

Comment

Another DDL statement COMMENT can be used to add a description about table or table column(s), view, materialized view etc in data dictionary.

COMMENT ON COLUMN class_project.cl_proj_mod 
   IS 'ID OF THE PROJECT MODERATOR';

COMMENT ON TABLE class_project 
   IS 'TABLE CONTAINS INFO ABOUT STUDENT’S CLASS PROJECT ';

The data dictionary views * _TAB_COMMENTS and *_ COL_COMMENTS view can be used to view the comments on a particular table or column.

REFERENCES
ORACLE DATABASE CONCEPTS – SQL
ORACLE DATABASE CONCEPTS – DATA INTEGRITY
ORACLE DATABASE CONCEPTS – Overview of Tables
ORACLE SQL LANGUAGE REFERENCE

Advertisements

Discussion

3 thoughts on “A Short Guide Oracle SQL [II] – DDL & Constraints

  1. fabulas……bhai..

    Posted by Alok Kumar | December 11, 2012, 01:09

Trackbacks/Pingbacks

  1. Pingback: A Short Guide to Oracle SQL [I] « musingdba - December 6, 2012

  2. Pingback: A Short Guide to Oracle SQL [III] « musingdba - December 14, 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: