Constraints

                                    CONSTRAINTS

Constraints is a rule which enforce entry of only valid data in table. Constraints prevent the deletion of a table if there are dependencies.

Types of Constraints:-

  ➥ PRIMARY KEY= (unique + not null)

  ➥ FOREIGN KEY

  ➥ NOT NULL

  ➥ UNIQUE

  ➥ CHECK

➧PRIMARY KEY:-

Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL. 

➧FOREIGN KEY:-

Specifies that the values in the column must correspond to values in a referenced primary key or unique key column.

➧NOT NULL:-

Specifies that this column cannot hold NULL values.

➧UNIQUE:-

Specifies that values in the column must be unique. Column can contain NULL value.

➧CHECK:-

Specifies Domain for Column values.

A CONSTRAINT can be one of the following:

* A column-level constraint

* A table-level constraint

➮Primary Key:

SQL> CREATE TABLE EMP(ID NUMBER(3) PRIMARY KEY,NAME VARCHAR2(10) NOT NULL,

  2  EMAIL VARCHAR2(10) UNIQUE,SALARY NUMBER(5) CHECK(SALARY>0));

Table created.

SQL> INSERT INTO EMP VALUES(1,'RAM','MAIL1',2000);

1 row created.

SQL> INSERT INTO EMP VALUES(2,'RAM','MAIL2',1000);

1 row created.

SQL> SELECT * FROM EMP;

        ID NAME       EMAIL          SALARY                                     

---------- ---------- ---------- ----------                                     

         1 RAM        MAIL1            2000                                     

         2 RAM        MAIL2            1000                 

➮Not Null/Unique/Check:-                  

SQL> CREATE TABLE EMP2(ID NUMBER(3) CONSTRAINT EMP2_ID_PK PRIMARY KEY,
  2  NAME VARCHAR2(10) CONSTRAINT EMP2_NAM_NN NOT NULL,
  3  EMAIL VARCHAR2(10) CONSTRAINT EMP2_MAIL_UNQ UNIQUE,
  4  SALARY NUMBER(5) CONSTRAINT EMP2_SAL_CHK CHECK(SALARY>0));

Table created.

➮Foreign key:-

CREATE TABLE Orders (OrderID int NOT NULL,OrderNumber int NOT NULL,PersonID int,PRIMARY KEY (OrderID),FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

➦Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.

➦The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

➦The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

➦The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

➦The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

➥Modification of Constraints:-

➧ALTER TABLE TABLE_NAME ADD PRIMARY KEY(COLUMN NAME);

➧ALTER TABLE TABLE_NAME DROP PRIMARY KEY;

➧ALTER TABLE Orders ADD FOREIGN KEY (COLUMN NAME) REFERENCES TABLE_NAME(COLUMN NAME);

➧ALTER TABLE TABLE_NAME DROP FOREIGN KEY;

➥TO VIEW CONSTRAINTS IN TABLE:-

SELECT * FROM USER_CONSTRAINTS WHERE TABLE NAME='STUDENT';

NOTE:-"Table Name Should Be In CAPS LOCK"