SQL Queries Constraints Syntax

SQL Constraints:

The following constraints are commonly used in SQL queries:

 NOT NULL - Ensures that a column cannot have a NULL value.

 UNIQUE - Ensures that all values in a column are different.

 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.

 FOREIGN KEY - Uniquely identifies a row/record in another table.

 CHECK - Ensures that all values in a column satisfy a specific condition.

 DEFAULT - Sets a default value for a column when no value is specified.


NOT NULL Constraint

 By default, a column can hold NULL values. The NOT NULL constraint enforces a column
to NOT be given NULL values.

 Once NOT NULL constraint is applied to a column, you cannot pass a null value to that
column.


 NOT NULL constraint cannot be defined at table level.

SYNTAX

CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE(SIZE) NOT NULL, COLUMN2 DATATYPE(SIZE),

COLUMN3 DATATYPE(SIZE));

EXAMPLE:

CREATE TABLE EMPLOYEE (EID NUMBER(5) NOT NULL,

ENAME VARCHAR(20) NOT NULL, DEPT VARCHAR(20),

SALARY NUMBER(10));

UNIQUE Constraint

•                   The UNIQUE constraint ensures that all values in a column are different.

•                   It will not allow any duplicate value.

•                   But it permits multiple entries of NULL values into that column.

SYNTAX of UNIQUE CONSTRAINT Defined at Column Level: CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE(SIZE) UNIQUE , COLUMN2 DATATYPE (SIZE), COLUMN3 DATATYPE(SIZE));	SYNTAX of UNIQUE CONSTRAINT Defined at Table Level: CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE (SIZE) , COLUMN2 DATATYPE(SIZE), COLUMN3 DATATYPE(SIZE), UNIQUE (COLUMN1, COLUMN2));
EXAMPLE:	EXAMPLE:
CREATE TABLE EMPLOYEE (EID NUMBER(5) UNIQUE, ENAME VARCHAR(20) , DEPT VARCHAR(20), SALARY NUMBER(10));	CREATE TABLE EMPLOYEE (EID NUMBER(5) , ENAME VARCHAR(20) , DEPT VARCHAR(20), SALARY NUMBER(10), UNIQUE (EID,ENAME));

PRIMARY KEY Constraint

SYNTAX of PRIMARY KEY CONSTRAINT Defined at Column Level:	SYNTAX of PRIMARY KEY CONSTRAINT Defined at Table Level:
CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE (SIZE) PRIMARY KEY , COLUMN2 DATATYPE (SIZE), COLUMN3 DATATYPE(SIZE));	CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE(SIZE) , COLUMN2 DATATYPE(SIZE), COLUMN3 DATATYPE(SIZE), PRIMARY KEY (COLUMN1, COLUMN2));

EXAMPLE:	EXAMPLE:
CREATE TABLE EMPLOYEE	CREATE TABLE EMPLOYEE
(EID NUMBER(5) PRIMARY KEY,	(EID NUMBER(5) ,
ENAME VARCHAR(20) ,	ENAME VARCHAR(20) ,
DEPT VARCHAR(20),	DEPT VARCHAR(20),
SALARY NUMBER(10));	SALARY NUMBER(10),
	PRIMARY KEY (ENAME, DEPT));

FOREIGN KEY Constraint

SYNTAX of FOREIGN KEY CONSTRAINT Defined at Column Level: CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE , COLUMN2 DATATYPE REFERENCES MASTER_TABLE_NAME(COLUMN_NAME), COLUMN3 DATATYPE);

EXAMPLE: CREATE TABLE EMPLOYEE (EID NUMBER(5) PRIMARY KEY, ENAME VARCHAR(20) , DEPT VARCHAR(20) REFERENCES DEPARTMENT(DEPT), SALARY NUMBER(10));

SYNTAX of FOREIGN KEY CONSTRAINT Defined at Table Level: CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE , COLUMN2 DATATYPE, COLUMN3 DATATYPE, FOREIGN KEY (COLUMN1, COLUMN2) REFERENCES MASTER_TABLE_NAME(COLUMN1, COLUMN2) );

EXAMPLE: CREATE TABLE EMPLOYEE (EID NUMBER(5) PRIMARY KEY, ENAME VARCHAR(20) , DEPT VARCHAR(20), SALARY NUMBER(10) FOREIGN KEY (DEPT) REFERENCES DEPARTMENT(DEPT));

CHECK Constraint

SYNTAX of CHECK CONSTRAINT Defined at Column Level: CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE (SIZE) , COLUMN2 DATATYPE (SIZE) CHECK (LOGICAL EXPRESSION), COLUMN3 DATATYPE(SIZE));	SYNTAX of CHECK CONSTRAINT Defined at Table Level: CREATE TABLE TABLE_NAME ( COLUMN1 DATATYPE(SIZE) , COLUMN2 DATATYPE(SIZE), COLUMN3 DATATYPE(SIZE), CHECK (LOGICAL EXPRESSION));
EXAMPLE:	EXAMPLE:
CREATE TABLE EMPLOYEE	CREATE TABLE EMPLOYEE
(EID NUMBER(5),	(EID NUMBER(5) ,
ENAME VARCHAR(20) ,	ENAME VARCHAR(20) ,
DEPT VARCHAR(20),	DEPT VARCHAR(20),
SALARY NUMBER(10) CHECK(SALARY>0)	SALARY NUMBER(10),
);	CHECK(ENAME= INITCAP(ENAME)),
	);

DEFAULT Constraint

SYNTAX

COLUMNNAME DATATYPE(SIZE) DEFAULT (VALUE) EXAMPLE:

CREATE TABLE EMPLOYEE (EID NUMBER(5),

ENAME VARCHAR(20) , DEPT VARCHAR(20),

SALARY NUMBER(10) DEFAULT(0)) ;

Defining integrity constraints using ALTER Table command

Syntax:

ALTER TABLE TABLE_NAME ADD COLUMN NAME DATATYPE CONSTRAINT TYPE; ALTER TABLE TABLE_NAME MODIFY COLUMN NAME DATATYPE CONSTRAINT TYPE;

EXAMPLE:

•                   ALTER TABLE EMPLOYEE MODIFY SALARY NUMBER(10) NOT NULL;

•                   ALTER TABLE EMPLOYEE ADD AGE NUMBER(2) NOT NULL;

•                   ALTER TABLE EMPLOYEE MODIFY EID NUMBER(10) UNIQUE;

•                   ALTER TABLE EMPLOYEE ADD PRIMARY KEY(EID);

•                     ALTER TABLE EMPLOYEE ADD FOREIGN KEY(DEPT) REFERENCES DEPARTMENT (DEPT);

•                        ALTER TABLE EMPLOYEE ADD CHECK(DEPT LIKE 'D____ ');

Displaying Constraint types of a table

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMPLOYEE'

Providing user defined name to a constraint

•        CREATE TABLE EMPLOYEE (EID NUMBER(5) , ENAME VARCHAR(20) , DEPT VARCHAR(20), SALARY NUMBER(10), CONSTRAINT UC1 UNIQUE (EID,ENAME));

                   ALTER TABLE EMPLOYEE MODIFY CONSTRAINT

  

CREATE TABLE EMPLOYEE (EID NUMBER(5) , ENAME VARCHAR(20) , DEPT VARCHAR(20), SALARY NUMBER(10), CONSTRAINT PKCEID PRIMARY KEY (EID));

•                   CREATE TABLE EMPLOYEE (EID NUMBER(5) PRIMARY KEY, ENAME VARCHAR(20) , DEPT VARCHAR(20), SALARY NUMBER(10), CONSTRAINT FKCDEPT FOREIGN KEY (DEPT) REFERENCES DEPARTMENT(DEPT));

Dropping integrity constraints using ALTER Table command

Syntax

ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINTNAME;

Example:

•                   ALTER TABLE EMPLOYEE DROP CONSTRAINT PKCEID ;

•                   ALTER TABLE EMPLOYEE DROP PRIMARY KEY ;