CONSTRAINT

<------CONSTRAINT ----->

WHICH CAN RESTRICT SOMETHING

~~~~~~~~~~~~ ABOUT DIFFERENT TYPES OF CONSTRAINTS~~~~~~~~~

NOT NULL

this ensures that NULL values are not permitted for the columns, since they serve as keys for operation on this table. Columns without the NOT NULL constraint allow NULL values. NOT NULL is one of several intigrity constraints thatmay be defined for table.

UNIQUE

this designates a column or combination of column as a unique key. No two rows in the table can have the same value for this key. Nulls are allowed if the UNIQUE key is used on a single column.

PRIMARY key

As with UNIQUE keys a primary key enforces uniqueness of the column or column combinations involved and a uique index is created to manage this. There may however be only one primary key in a table and this is known as definitive key ehrough which rows in the table are individually identifier. NULLS are not allowed in a primary key clumns. the long datatypes cannot be included in a primary key. a primary key can contain 16 columns at most. A UNIQUE index is creted on the column contained in a the primary key.

FOREIGHN KEY

foreign key provide referential intigrity rule either within a table or between tables. A foreign key is used in relation ship with either a primary or unique key elsewhere.

CHECK constraints

the check constraints explicitly defines a condition that each row must satisfy.

DEFINING CONSTRAINTS (ALSO EXAMPLE OF DEFINING PRIMARY KEY CONSTRAINT)

CREATE TABLE COUNTRY(ID NUMBER(4),NAME VARCHAR2(30), RANK NUMBER(4), CONTINENT VARCHAR2(30),CONSTRAINT ID_PK PRIMARY KEY(ID));

INSERT INTO COUNTRY VALUES(1,'INDIA',1,'ASIA');

VIEW CONSTRAINT FROM DATA DICTIONARY

SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME='ID_PK';

NOT NULL

CREATE TABLE STATE(ID NUMBER(4) CONSTRAINT ID_NNUL NOT NULL,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30));

INSERT INTO STATE VALUES( NULL,'WEST BENGAL',1,'INDIA');

UNIQUE

CREATE TABLE STATE(ID NUMBER(4) ,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30),CONSTRAINT ID_UNI UNIQUE(ID));

INSERT INTO STATE VALUES( NULL,'WEST BENGAL',1,'INDIA');

FOREIGN KEY

CREATE TABLE STATE(ID NUMBER(4) CONSTRAINT ID_NNUL NOT NULL,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30),CONSTRAINT ID_FK FOREIGN KEY(ID) REFERENCES COUNTRY(ID),CONSTRAINT ID_UNI UNIQUE(ID));

CREATE TABLE STATE(ID NUMBER(4) CONSTRAINT ID_NNUL NOT NULL,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30),CONSTRAINT ID_FK FOREIGN KEY(ID) REFERENCES COUNTRY(ID) ON DELETE CASCADE,CONSTRAINT ID_UNI UNIQUE(ID));

CREATE TABLE STATE(ID NUMBER(4) CONSTRAINT ID_NNUL NOT NULL,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30),CONSTRAINT ID_FK FOREIGN KEY(ID) REFERENCES COUNTRY(ID) ON UPDATE CASCADE,CONSTRAINT ID_UNI UNIQUE(ID));

INSERT INTO STATE VALUES( 1,'WEST BENGAL',12,'INDIA');

PROOF OF GIVING MULTIPLE CONSTRAINT TO ONE TABLE

SELECT CONSTRAINT_NAME,TABLE_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='STATE';

CHECK CONSTRAINT

CREATE TABLE CHK(NAME VARCHAR2(10),SAL NUMBER(7),CONSTRAINT SAL_CHK CHECK(SAL>2000));

INSERT INTO CHK VALUES('ARKA',100);

INSERT INTO CHK VALUES('ARKA',2904);

ADDING A CONSTRAINT

ALTER TABLE COUNTRY MODIFY(ID CONSTRAINT ID_PK NOT NULL);

DROPPING A CONSTRAINT

ALTER TABLE COUNTRY DROP CONSTRAINT ID_PK CASCADE;

DISABLE A CONSTRAINT

ALTER TABLE COUNTRY DISABLE CONSTRAINT ID_PK CASCADE;

ENABLE A CONSTRAINT

ALTER TABLE COUNTRY ENABLE CONSTRAINT ID_PK CASCADE;