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.
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
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
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 ;
0 Comments
Please do not Enter any spam link in the comment box