Oracle Constraints

Constraints are used to prevent the invalid data entry into tables. We have the following constraints.
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

NOT NULL: This does not allow null values. But, this allows duplicate values.
Example:

CREATE TABLE table_name(empno number, ename NOT NULL);

UNIQUE: This constraint does not allow duplicate values. But, this will accept null values. This constraint can be created at column level and table level. Whenever we create unique constraints, Oracle server automatically creates b tree indexes on the columns.
Example:

CREATE TABLE ORA1(ACCT_NUMBER NUMBER UNIQUE, A_NAME VARCHAR2(30));
CREATE TABLE ORA2(ACCT_NUMBER NUMBER, A_NAME VARCHAR2(30),
UNIQUE(ACCT_NUMBER,A_NAME));

PRIMARY KEY: Primary key uniquely identifies a record in tables. Primary key does not accept duplicate and null values. There can be only one primary key in a table.Whenever we create primary key, Oracle server automatically creates b tree indexes on the columns. If primary key created in more than one column is called composite primary key.

Example:

CREATE TABLE ORA3(ACCT_NUMBER primary key, A_NAME varchar2(30));
CREATE TABLE ORA4(ACCT_NUMBER NUMBER, A_NAME VARCHAR2(30),
PRIMARY KEY(ACCT_NUMBER,A_NAME));

FOREIGN KEY: If we want to establish relationship between two tables, then we must establish referential integrity constraint FOREIGN KEY. One table foreign must belongs to anther table primary key. These two columns datatype must be same. Foreign key accepts duplicate and null values.

Example:

CREATE TABLE ORA3(ACCT_NUMBER primary key, A_NAME varchar2(30));
CREATE TABLE FRA1(ACCT_NUMBER NUMBER, references(ACCT_NUMBER));
CREATE TABLE ORA4(ACCT_NUMBER NUMBER, A_NAME VARCHAR2(30),
PRIMARY KEY(ACCT_NUMBER,A_NAME));
CREATE TABLE FRA2(ACCT_NUMBER NUMBER, A_NAME VARCHAR2(30), FOREGIN KEY(ACCT_NUMBER, A_NAME) references ORA4);

CHECK: This constraint is used to define logical conditions.

Example:

CREATE TABLE ORA5(ACCT_NUMBER NUMBER, A_NAME VARCHAR2(30),SAL NUMBER(10) CHECK(SAL>5000));

DEFAULT: If we want to define default values for a column using default clause.

Example:

CREATE TABLE ORA6(ACCT_NUMBER NUMBER, A_NAME VARCHAR2(30), SAL NUMBER(10) DEFAULT 10000);

Adding or dropping constraints on existing table:
ALTER TABLE ORA7 ADD PRIMARY KEY(ACCT_NUMBER);
ALTER TABLE ORA7 ADD A_NAME VARCHAR2(30) UNIQUE;

Dropping constraints:
ALTER TABLE ORA7 DROP PRIMARY KEY;

Powered by k2schools