CONSTRAINTS:
SQL constraints are essential for maintaining data integrity and consistency in a database, ensuring that data is accurate, reliable, and secure. By implementing constraints such as primary key, foreign key, and UNIQUE, you can prevent data anomalies and errors. Additionally, these constraints improve the overall performance of your database.. Learn how to use foreign key SQL query constraints effectively to enforce business rules, validate data, and optimize database design
– Constraints are used to restricted unwanted data (i.e invalid data) into a table.
– All databases support the following six types of constraints. These include…
1) UNIQUE
2) NOT NULL
3) CHECK
4) PRIMARY KEY
5) FOREIGN KEY
6) DEFAULT
– Constraints can be defined on a table at two levels.
i) Column level: In this level we will define a constraint on each column wise.
syntax:
CREATE TABLE <TN>(<COLUMN NAME1> <DATATYPE>[SIZE] <CONSTRAINT TYPE>,
<COLUMN NAME2> <DATATYPE>[SIZE] <CONSTRAINT TYPE>,…..);
ii) table level: At the table level, constraints are defined after all columns, i.e., at the end of the table.
syntax:
CREATE TABLE <TN>(<COLUMN NAME1> <DATATYPE>[SIZE] ,
<COLUMN NAME2> <DATATYPE>[SIZE] ,…..,<constraint name>(column name1,column name2,…..));
1) UNIQUE: To restricted duplicate values but allowed nulls into a column.
EX:
Column level:
SQL> CREATE TABLE TEST1(SNO NUMBER(2) UNIQUE,NAME VARCHAR2(10) UNIQUE);
Testing:
SQL> INSERT INTO TEST1 VALUES(1,’A’);——————–ALLOWED
SQL> INSERT INTO TEST1 VALUES(1,’A’);——————–NOT ALLOWED
SQL> INSERT INTO TEST1 VALUES(NULL,NULL);——-ALLOWED
SQL> INSERT INTO TEST1 VALUES(2,’B’);——————-ALLOWED
Table level:
SQL> CREATE TABLE TEST2(SNO NUMBER(2),NAME VARCHAR2(10),UNIQUE(SNO,NAME));
Testing:
SQL> INSERT INTO TEST2 VALUES(1,’A’);———–ALLOWED
SQL> INSERT INTO TEST2 VALUES(1,’A’);———-NOT ALLOWED
SQL> INSERT INTO TEST2 VALUES(1,’B’);———-ALLOWED
SQL> INSERT INTO TEST2 VALUES(NULL,NULL);——–ALLOWED
2) NOT NULL:
-> To restricted nulls but allowed duplicate values into a column.
-> It cannot defined at table level.
EX:
column level:
SQL> CREATE TABLE TEST3(ID NUMBER(2) NOT NULL,SAL NUMBER(8,2) NOT NULL);
Testing :
SQL> INSERT INTO TEST3 VALUES(1,24000); ——–ALLOWED
SQL> INSERT INTO TEST3 VALUES(1,24000); ——-ALLOWED
SQL> INSERT INTO TEST3 VALUES(NULL,NULL);—–NOT ALLOWED
3) CHECK:
– to check the values with user defined condition before accepting into a column.
EX: column level:
SQL> CREATE TABLE TEST4
(
STID NUMBER(2) UNIQUE NOT NULL,
SNAME VARCHAR2(10) NOT NULL,
SFEE NUMBER(6,2) NOT NULL CHECK(SFEE>=5000),
AGE NUMBER(3)NOT NULL CHECK(AGE BETWEEN 18 AND 30),
LOC VARCHAR2(10)NOT NULL CHECK(LOC IN(‘HYD’,’UP’,’MP’))
);
Testing:
SQL> INSERT INTO TEST4 VALUES(1,’SMITH’,4999,17,’HYDERABAD’);——–NOT ALLOWED
SQL> INSERT INTO TEST4 VALUES(1,’SMITH’,5000,18,’HYD’);——-ALLOWED
Table level:
SQL> CREATE TABLE TEST5(ENAME VARCHAR2(10),
SAL NUMBER(8,2),CHECK(ENAME=LOWER(ENAME) AND SAL>=15000));
Testing :
SQL> INSERT INTO TEST5 VALUES(‘ALLEN’,15000);——NOT ALLOWED
SQL> INSERT INTO TEST5 VALUES(‘allen’,15000);——ALLOWED
4) PRIMARY KEY: Primary Key (PK) is a column or set of columns in a table that uniquely identifies each row in the table. It is a fundamental concept in relational databases and is used to enforce data integrity and consistency.
->it is a combination UNIQUE and NOT NULL constraint.
->By using PRIMARY KEY we can restrict duplicate and null values.
->A table is having only one primary key constraint.
Ex:
Column level:
SQL> CREATE TABLE TEST6(EID NUMBER(4) PRIMARY KEY, ENAME VARCHAR2(10));
Testing :
SQL> INSERT INTO TEST6 VALUES(1,’SMITH’);—————ALLOWED
SQL> INSERT INTO TEST6 VALUES(1,’ALLEN’);————–NOT ALLOWED
SQL> INSERT INTO TEST6 VALUES(NULL,’ALLEN’);——-NOT ALLOWED
SQL> INSERT INTO TEST6 VALUES(2,’ALLEN’);————-ALLOWED
COMPOSITE PRIMARY KEY (table level):
->When we apply a primary key on the combination of columns are called as composite primary key.
->In composite primary key, individual columns are accepting duplicate values but the combination columns are not accepting duplicate values.
Ex: SQL> CREATE TABLE BRANCH(BCODE NUMBER(4),
BNAME VARCHAR2(10),BLOC VARCHAR2(10),
PRIMARY KEY(BCODE,BNAME));
Testing :
SQL> INSERT INTO BRANCH VALUES(1021,’SBI’,’AMEERPET’);———–ALLOWED
SQL> INSERT INTO BRANCH VALUES(1021,’SBI’,’MADHAPUR’);———NOT ALLOWED
SQL> INSERT INTO BRANCH VALUES(1022,’SBI’,’MADHAPUR’);——–ALLOWED
FOREIGN KEY / REFERENCES KEY: This constraint is used to establish relationship between tables for taking an identity from one table to another table.
Basic Rules:
- There should be a common column in both tables.
- those columns datatypes must match.
- one table should have a primary key and another table should have foreign key but both are should be common column from both tables.
- a primary key table is called as “parent table” and a foreign key table is called as “child table”.
- a foreign key column is accepting the values which must be in primary key column only.
- by default a foreign key is accepting duplicate and nulls.
<common column of child table> <datatype>[size] references <parent table name>(common column of parent table)
EX:
SQL> CREATE TABLE DEPT1(DNO NUMBER(2) PRIMARY KEY,DNAME VARCHAR2(10));—-> PARENT TABLE
SQL> INSERT INTO DEPT1 VALUES(1,’ORACLE’);
SQL> INSERT INTO DEPT1 VALUES(2,’SAP’);
SQL> CREATE TABLE EMP1(EID NUMBER(4),ENAME VARCHAR2(10),
2 DNO NUMBER(3) REFERENCES DEPT1(DNO)); ——————–> CHILD TABLE
SQL> INSERT INTO EMP1 VALUES(1021,’SMITH’,1);
SQL> INSERT INTO EMP1 VALUES(1022,’JONES’,1);
SQL> INSERT INTO EMP1 VALUES(1023,’ADAMS’,2);
SQL> INSERT INTO EMP1 VALUES(1024,’JAMES’,NULL);
Updating a reference column values of a child table with reference values of parent table:
Ex:
SQL> UPDATE EMP1 SET DNO=1 WHERE DNO IS NULL; ——allowed SQL> UPDATE EMP1 SET DNO=2 WHERE DNO IS NULL; ——allowed
NOTE:
Once we establish a relationship between the tables there are two rules are come into picture.
Rule-1(Insertion):
– we cannot insert values into a reference column of child table which are not found
in primary key column of parent table.
EX:
SQL> INSERT INTO EMP1 VALUES(1021,’SMITH’,3);
ERROR at line 1:
ORA-02291: integrity constraint (MYDB11AM.SYS_C009020) violated – parent key not found.
Rule-2:
We cannot delete a row from the parent table which will contains child rows in child table without addressing to the child.
EX: SQL> DELETE FROM DEPT1 WHERE DNO=1;
ERROR at line 1:
ORA-02292: integrity constraint (MYDB11AM.SYS_C009020) violated – child record found.
How to address to a child rows:
i) on delete cascade
ii) on delete set null
i) On delete cascade:
When a row is deleted from a parent table, corresponding child rows are automatically deleted from the child table.
EX: SQL> CREATE TABLE DEPT2(DNO NUMBER(2) PRIMARY KEY,DNAME VARCHAR2(10));—-> PARENT TABLE
SQL> INSERT INTO DEPT2 VALUES(1,’ORACLE’);
SQL> INSERT INTO DEPT2 VALUES(2,’SAP’);
SQL> CREATE TABLE EMP2(EID NUMBER(4),ENAME VARCHAR2(10),
DNO NUMBER(3) REFERENCES DEPT2(DNO) ON DELETE CASCADE); ———> CHILD TABLE
SQL> INSERT INTO EMP2 VALUES(1021,’SMITH’,1);
SQL> INSERT INTO EMP2 VALUES(1022,’JONES’,2);
Testing : SQL> DELETE FROM DEPT2 WHERE DNO=2;———allowed.
ii) On delete set null:
When we delete a row from the parent table, the corresponding child rows of the reference column values are automatically set to NULL in the child table.
EX: sql create table primary key
SQL> CREATE TABLE DEPT3(DNO NUMBER(2) PRIMARY KEY,DNAME VARCHAR2(10));—-> PARENT TABLE
SQL> INSERT INTO DEPT3 VALUES(1,’ORACLE’);
SQL> INSERT INTO DEPT3 VALUES(2,’SAP’);
SQL> CREATE TABLE EMP3(EID NUMBER(4),ENAME VARCHAR2(10),
DNO NUMBER(3) REFERENCES DEPT3(DNO) ON DELETE SET NULL); ——> CHILD TABLE
SQL> INSERT INTO EMP3 VALUES(1021,’SMITH’,1);
SQL> INSERT INTO EMP3 VALUES(1022,’JONES’,2);
Testing: SQL> DELETE FROM DEPT2 WHERE DNO=1;———allowed
HOW TO APPLY CONSTRAINT (primary key and foreign key)ON EXISTING TABLE:
syntax: ALTER TABLE <TN> ADD CONSTRAINT <CONSTRAINT KEY NAME> <CONSTRAINT TYPE>(column name);
i) Apply Primary key:
EX: SQL> CREATE TABLE PARENT(EID NUMBER(4),ENAME VARCHAR2(10),SAL NUMBER(8,2));
SQL> ALTER TABLE PARENT ADD CONSTRAINT PK_EID PRIMARY KEY(EID);
NOTE:
If we want to check sql check constraint details of a specific table then oracle will provide a pre-defined table (or) datadictionary is known as “user_cons_columns”.
EX:
SQL> DESC USER_CONS_COLUMNS;
SQL> SELECT CONSTRAINT_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME=’PARENT’;
CONSTRAINT_NAME COLUMN_NAME
PK_EID EID
How to view all data dictionaries (pre-defined tables) in oracle:
syntax: SELECT * FROM DICT; (Dictionary – Main table)
EX: SQL> SELECT * FROM DICT;
ii) Apply Unique constraint:
SQL> ALTER TABLE PARENT ADD CONSTRAINT UQ_ENAME UNIQUE(ENAME);
iii) Apply Check constraint:
SQL> ALTER TABLE PARENT ADD CONSTRAINT CHK_SAL CHECK(SAL>10000);
NOTE: If we want to view constraint name and check constraint conditional value then use a datadictionary is “user_constraints”.
EX: SQL> DESC USER_CONSTRAINTS;
SQL> SELECT CONSTRAINT_NAME,SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME=’PARENT’;
CONSTRAINT_NAME SEARCH_CONDITION
CHK_SAL SAL>10000
iv) Apply Foreign key:
syntax: ALTER TABLE <TN> ADD CONSTRAINT <CONSTRAINT KEY NAME>
FOREIGN KEY(common column from child table) REFERENCES
<PARENT TABLE NAME>(common column from parent table) ON DELETE CASCADE / ON DELETE SET NULL;
EX: SQL> CREATE TABLE CHILD(DNAME VARCHAR2(10),EID NUMBER(4));
Table created.
SQL> ALTER TABLE CHILD ADD CONSTRAINT FK_EID
FOREIGN KEY(EID) REFERENCES PARENT(EID)
ON DELETE CASCADE;
v) Apply “NOT NULL”:
syntax: ALTER TABLE <TN> MODIFY <COLUMN NAME> <CONSTRAINT> <CONSTRAINT KEY NAME> NOT NULL;
EX: SQL> ALTER TABLE CHILD MODIFY DNAME CONSTRAINT NN_DNAME NOT NULL;
How to remove a constraint from an existing table:
syntax: ALTER TABLE <TN> DROP CONSTRAINT <CONSTRAINT KEY NAME>;
i) to remove Primary key:
case-1: With relationship:
SQL> ALTER TABLE PARENT DROP CONSTRAINT PK_EID CASCADE;
case-2: Without relationship:
SQL> ALTER TABLE PARENT DROP CONSTRAINT PK_EID;
ii) to remove unique, check, not null:
SQL> ALTER TABLE PARENT DROP CONSTRAINT UQ_ENAME;
SQL> ALTER TABLE PARENT DROP CONSTRAINT CHK_SAL;
SQL> ALTER TABLE CHILD DROP CONSTRAINT NN_DNAME;
DEFAULT constraint: To assign a user defined default value to a column in the table.
Ex: SQL> CREATE TABLE TEST7(NAME VARCHAR2(10),LOC VARCHAR2(10) DEFAULT ‘HYD’);
Testing :
SQL> INSERT INTO TEST7(NAME,LOC)VALUES(‘A’,’PUNE’);—–allowed
SQL> INSERT INTO TEST7(NAME)VALUES(‘B’);——–allowed
How to add a default value to a column in the table:
syntax: ALTER TABLE <TN> MODIFY <COLUMN NAME> DEFAULT <value/expression>;
EX: SQL> CREATE TABLE TEST8(SNAME VARCHAR2(10),SFEE NUMBER(6,2));
SQL> ALTER TABLE TEST8 MODIFY SFEE DEFAULT 5000;
Testing: SQL> INSERT INTO TEST8(SNAME)VALUES(‘SMITH’);
NOTE: To view column name and also default value then use a datadictionary is “user_tab_columns”.
EX:
SQL> DESC USER_TAB_COLUMNS;
SQL> SELECT COLUMN_NAME,DATA_DEFAULT FROM USER_TAB_COLUMNS
2 WHERE TABLE_NAME=’TEST8′;
COLUMN_NAME DATA_DEFAULT
SFEE 5000
How to remove a default value from a column:
syntax: ALTER TABLE <TN> MODIFY <COLUMN NAME> DEFAULT NULL;
EX: SQL> ALTER TABLE TEST8 MODIFY SFEE DEFAULT NULL;
Testing: SQL> INSERT INTO TEST8(SNAME)VALUES(‘WARD’);
If you want to learn about SQL Operators then please visit our previous article click here.
If you want to learn about SQL Joins then please visit our previous article click here.
If you want to learn about SQL Functions then please visit our previous article Click here.
For online practice please click here.
