primary key

                                            PRIMARY KEYS

In Oracle, a primary key is a single field or combination of fields that uniquely defines a record.

NOTE

The fields that are part of the primary key can’t contain a null value.

A table can have only one primary key.

In Oracle, a primary key cannot contain more than 32 columns.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement. 

Defining in CREATE TABLE

syntax:

CREATE TABLE table_name

( 

column1 datatype null/not null, 

column2 datatype null/not null,  ... 

CONSTRAINT constraint_name PRIMARY KEY (column1,... column_n)

);

Example

CREATE TABLE student

(

  student_id numeric(10) not null,

  student_name varchar2(50) not null,

  CONSTRAINT student_pk PRIMARY KEY (student_id)

);

Defining in ALTER TABLE STATEMENT

Syntax

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column1,..column_n);

Example

ALTER TABLE student

ADD CONSTRAINT student_pk PRIMARY KEY (student_id);

Creating a primary key with more than one field

ALTER TABLE student

ADD CONSTRAINT student_pk PRIMARY KEY (student_id, student_name);

DROP PRIMARY KEY

Syntax

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Example

ALTER TABLE student

 DROP CONSTRAINT student_pk;

DISABLE PRIMARY KEY

Syntax

ALTER TABLE table_name

DISABLE CONSTRAINT constraint_name;

Example

ALTER TABLE student

 DISABLE CONSTRAINT student_pk;

ENABLE PRIMARY KEY

Syntax

ALTER TABLE table_name

ENABLE CONSTRAINT constraint_name;

Example

ALTER TABLE student

ENABLE CONSTRAINT student_pk;

oracle data types

Data Type	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
char(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Where size is the number of characters to store. Variable-length string.
long	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length strings. (backward compatible)
raw	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Variable-length binary strings
long raw	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length binary strings. (backward compatible)

NUMERIC DATATYPES

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
number(p,s)	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Where p is the precision and s is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float
dec(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer
int
smallint
real
double precision

DATE/TIME DATATYPES

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example: timestamp(4) with local time zone
interval year (year precision) to month	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	Time period stored in years and months. For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example: interval day(2) to second(6)