Sunday, April 09, 2006

Oracle : DDL & constraints

NAME OF TABLE/COLUMS : Must begin with a letter.
SIZE : 1-30 characters
Allowed characters in a name : A-Z,a-z,0-9,_,$,#

Note : Name must not be duplicate another object owned by the owner. And it must not be an Oracle reserved word. Names are not case sensetive.


VARCHAR2(max_size) : Variable length Character data. (Min size:1; Max size:4000)

CHAR([size]) : Fixed length Character data, Oracle pads the values with blanks upto the declared length of the column. (default/Min size:1; Max size:2000)

NUMBER([n,m]) : n: total number of digits(1 to 38, default 38), m: number of decimal points(upto 38).

DATE : between Jan 1,4712 BC to Dec 31, 9999 AD. DATE columns are 7 bytes in length.

LONG : Variable length Character data, upto 2 gigabyte.

CLOB : Character data upto 4 gigabyte.
RAW(MAX_size) : Raw binary data. Max size is 2000.
LONG RAW : Raw binary data of variable length upto 2 gigabyte.
BLOB : Binary data upto 4 gigabyte.
BFILE : Binary data stored in external file; upto 4 gigabyte.
ROWID : A 64 base number system representing the unique address of a row in its table. These could be physical
ROWIDs or logical ROWIDs.

Single bytes or multibyte UNICODE character based data types :

NVARCHAR2(MAX_size), NCHAR ([size]), NCLOB : same as above but stores UNICODE data.


Summary of available DDL commands :

CREATE TABLE tablename
ALTER TABLE tablename

DROP TABLE tablename
TRUNCATE tablename
RENAME ... TO ...
COMMENT ON TABLE tablename IS '....' ;

Explainations :

CREATE TABLE [schema.]table
(column_name datatype [DEFAULT expression] [[CONSTRAINT constraint_name] constraint type]
[,column_name ....]
[,[CONSTRAINT constraint_name] constraint type (column,column...)]
[,CONSTRAINT ....] );

DEFAULT can be literal values, expressions, SQL functions. These can't be another column name or pseudocolumn.

CREATE TABLE tablename [(column [DEFAULT expression] [ [CONSTRAINT constraint_name] constraint type], [...])]
SELECT [*|column list] FROM tablename2;

ALTER TABLE table_name
ADD (column [datatype] [DEFAULT expr] [CONSTRAINT constraint_name constraint_type] | [NOT NULL]);

Note : The new columns is initially null for all the rows.

ALTER TABLE table_name
MODIFY (column [datatype] [DEFAULT expr] [CONSTRAINT constraint_name constraint_type] | [NOT NULL]);

ALTER TABLE table_name
DROP (column, column,....) [CASCADE CONSTRAINTS];

ALTER TABLE table_name

ALTER TABLE table_name
SET UNUSED COLUMN column_name;

ALTER TABLE table_name
SET UNUSED (column_name, column_name,.....);

ALTER TABLE tablename

alter table my_emp2
drop Primary Key
drop Unique (Lname)
modify (id number(6))
add (id2 number(8,4));

DROP TABLE tablename;

RENAME tablename1 TO tablename2;


Note : TRUNCATE is faster than DELETE TABLE for following reasons:

COMMENT ON TABLE tablename IS 'this is test table';

COMMENT ON COLUMN tablename.column IS ' testing column comment';

ALTER TABLE tablename
ADD [CONSTRAINT constraint_name] constraint type (column);

ALTER TABLE tablename
MODIFY column [CONSTRAINT constraint_name] NOT NULL;

ALTER TABLE tablename
DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint_name [CASCADE];

ALTER TABLE tablename

ALTER TABLE tablename
ENABLE CONSTRAINT constraint_name;

NOTE : DISABLE/ENABLE can be used in both CREATE TABLE/ALTER TABLE statements. CASCADE option causes any depended constraints also to be dropped or disabled.

DISABLEing a Primary Key or Unique Key constraint removes the unique index.

ENABLING creates the UNIQUE KEY or PRIMARY KEY indexes.If you enable a constraint, then all data in the table must fit the constraint. Enabling a primary key constraint disabled using CASCADE doesnot enable any foreign keys that are dependent upon the primary key.

Steps to take in case existing data violates a disabled Constraint:


Syntax :

Column Constraint Level :
column [CONSTRAINT constraint_name] REFERENCES tablename(column_name) [ON DELETE CASCADE|ON DELETE SET NULL]

Table Constraint Level :
column,... [CONSTRAINT constraint_name] FOREIGN KEY (column,...) REFERENCES tablename(column_name) [ON DELETE CASCADE|ON DELETE SET NULL]

Note : check constraints declared at table level can't reference other columns. All constraints can be defined at Table Level except NOT NULL. Composite unique/primary key/Foreign key contraints are created at table level declaration.

NOT NULL : can only be defined at column level. A NOT NULL constraint can only be defined if the table is empty or if the column has values for all the rows.

UNIQUE : Ensures uniqueness of column or column combination. Unless NOT NULL is also defined of the column, unique constraint allows any number of null values as NULL are not considered equal to anything. If the Unique constraint comprises more than one column eg. Unique(col1,c2) that group of column is called a Composite Unique Key. Can't have identical values in the non-null columns of a partially null UNIQUE key constraint.

PRIMARY KEY : Ensures uniqueness of column or column combination. No columns which are part of a composite primary key can contain a NULL. Only one Primary key is allowed per table.

Oracle enforces Unique & Primary key constraints by implicitly creating UNIQUE indexes on these columns/column combinations.

FOREIGN KEY : Referential integrity constraint. Designates a column or column combination as foreign key and establishes a relationship between a primary key or unique key in the same table or different table. A foreign key must match an existing value in the parent table or be NULL. Foreign keys are based on data values and are purely logical pointers.The rows in the parent table can't be deleted if it is referenced in the child table, unless ON DELETE CASCADE or ON DELETE SET NULL is specified. This default behaviour is called the restrict rule which disallows update or deletion of referenced data.

Note: A FOREIGN KEY can't be created on the child table until the parent table is created and the PRIMARY KEY is defined on that parent table.

CHECK constraints : Defines a condition that each row must satisfy.

The CHECK condition may be the same as SQL conditions except following:

CASCADE CONSTRAINTS : Used along with DROP COLUMN clause. It drops all referencial integrity constraints that refer to the primary & unique keys defined on the dropped columns. It also drops all multicolumn constraints defined on the dropped columns.

column1 primary key,columns...,
CONSTRAINT constraint_name1 FOREIGN KEY (column1) references test1,
CONSTRAINT constraint_name2 CHECK (column1>0, column3>0);

Alter Table drop column1 ; -> error as column1 is a parent key
ALTER TABLE DROP column3 ; -> error because column3 is referenced by multi-column constraint_name2

Solution :
ALTER TABLE DROP (column1) CASCADE CONSTRAINTS; -> drops constraints primary key, foreign key & constraint_name2

ALTER TABLE DROP (column1,column2,column3); -> this is valid as all the columns referenced by the constraints defined on the dropped columns are also dropped.

DateTime Data Types

TIMESTAMP [(fractional_second_precision)][WITH TIME ZONE | WITH LOCAL TIME ZONE]

WITH TIME ZONE : timestamp + time zone displacement between local time & UTC(GMT). Can't be used for index/primary keys/unique keys.

WITH LOCAL TIME ZONE : Data stored in database is normalised to the database time zone.
Oracles returns the data in the user's local session time zone.

INTERVAL YEAR [(year precisiom)] TO MONTH

INTERVAL '123-2' YEAR(3) TO MONTH : +123-02 (month value can be 0-11)
INTERVAL '123' YEAR(3) : +123-00
INTERVAL '300' MONTH(3) : +025-00

INVERVAL DAY[day precision] TO SECOND[fractional_second_precision]

INTERVAL '4 5:12:10.222' DAY TO SECOND(3) : +04 05:12:10.222
INTERVAL '7' DAY : +07 00:00:00
INTERVAL '180' DAY(3) : +180 00:00:00
INTERVAL '4 5:12' DAY TO MINUTE : +04 05:12:00
INTERVAL '4 5' DAY TO HOUR : +04 05:00:00
INTERVAL '5:12:10.1234567' HOUR TO SECOND(7) : +00 05:12:10.1234567

Data Dictionary Views :
All data dictionary tables are owned by SYS user.

Data dictionary views :


Default data dictionary views available in Oracle 9i :

Note : C: check P: Primary key, R : Referential Integrity, U : Unique key. Not NULL is a check constraint

A SQL statement to get a useful table constraints view :

SELECT ucc.table_name, ucc.column_name, uc.constraint_type, ucc.constraint_name,

uc.r_constraint_name,uc.delete_rule,uc.status,uc.search_condition, uc.index_name
FROM user_constraints uc
JOIN user_cons_columns ucc
ON (ucc.constraint_name=uc.constraint_name)
ORDER BY ucc.table_name;

Comments: Post a Comment

<< Home
Advertisements - The World's Online Marketplace

  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings