|
INTEGRITY CONSTRAINTS
1. CHECK CONSTRAINT
When the Table values must be in certain range or that satisfy certain condition.
Syntax ::
[CONSTRSINT <NAME>] CHECK (<CONDITION>)
Example ::
CREATE TABLE CUSTOMER (
CUST_ID NUMBER(3) CHECK(CUST_ID BETWEEN 100 AND 999),
CUST_NAME VARCHAR2(20) CHECK(CUST_NAME=UPPER(CUST_NAME)),
CUST_ADDRESS VARCHAR2(30) ,
CUST_CITY VARCHAR(20) ,
CUST_STATE VARCHAR(20) ,
CUST_PIN NUMBER(7) ,
CUST_COUNTRY VARCHAR(10) );
|
2. UNIQUE CONSTRAINT
A UNIQUE CONSTRAINT is used to assign the unique value to a defined attribute.
Syntax ::
[<CONSTRAINT NAME>] UNIQUE
Example ::
CREATE TABLE CUSTOMER (
CUST_ID NUMBER(3) ,
CUST_NAME VARCHAR2(20) ,
CUST_PROJECT VARCHAR2(30) UNIQUE,
CUST_CITY VARCHAR(20) ,
CUST_STATE VARCHAR(20) ,
CUST_PIN NUMBER(7) ,
CUST_COUNTRY VARCHAR(10) );
|
3. NOT NULL CONSTRAINT
NOT NULL CONSTRAINT is used when the value for the attributes must define.
Syntax ::
[<CONSTRAINT NAME>] NOT NULL
Example ::
CREATE TABLE CUSTOMER (
CUST_ID NUMBER(3) NOT NULL,
CUST_NAME VARCHAR2(20) NOT NULL,
CUST_PROJECT VARCHAR2(30) NOT NULL,
CUST_CITY VARCHAR(20) ,
CUST_STATE VARCHAR(20) ,
CUST_PIN NUMBER(7) ,
CUST_COUNTRY VARCHAR(10) );
|
4. PRIMARY CONSTRAINT
A PRIMARY KEY CONSTRAINT enables a unique identification of each tuple/record in a Table.
Syntax ::
[<CONSTRAINT NAME>] PRIMARY KEY
Example ::
CREATE TABLE CUSTOMER (
CUST_ID NUMBER(3) CUSTID_PK PRIMARY KEY(CUST_ID),
CUST_NAME VARCHAR2(20) ,
CUST_PROJECT VARCHAR2(30) ,
CUST_CITY VARCHAR(20) ,
CUST_STATE VARCHAR(20) ,
CUST_PIN NUMBER(7) ,
CUST_COUNTRY VARCHAR(10) );
|
5. FOREIGN CONSTRAINT
FOREIGN KEY CONSTRAINT is used to specify interdependencies between relations.
Syntax ::
[<CONSTRAINT NAME>] [FOREIGN KEY(<COLUMN(S)>)]
REFERNCES <TABLE>[(<COLUMN(S)>)]
Example ::
CREATE TABLE CUSTOMER (
CUST_ID NUMBER(3) ,
CUST_NAME VARCHAR2(20) ,
CUST_ADDRESS VARCHAR2(30) ,
CUST_CITY VARCHAR(20) ,
CUST_STATE VARCHAR(20) ,
CUST_PIN NUMBER(7) ,
CUST_COUNTRY VARCHAR(10)
CONSTRAINT CUSTID_PK PRIMARYKEY (CUST_ID)
CONSTRAINT DEPTID_FK FOREIGNKEY (DEPT_ID)
REFERNCES DEPARTMENT(DEPTID) );
|
|
|
