.png)
Constraints are used to specify rules or conditions for data in a table.This constraints can be applied at columns or attributes.This ensures the accuracy and reliability of the data in the table.
Constraints can be divided into the following two types:
Column level constraints: Limits only column data.
Table level constraints: Limits whole table data.
The following constraints are commonly used in SQL:
NOT NULL
Unique
Primary key
Foreign key
Check
Default
NOT NULL- It ensures that a column cannot have a NULL value. It enforces a column to contain a proper value.This constraint is that it cannot be defined at table level.
Example using NOT NULL constraint-
CREATE TABLE Student(Name varchar2(20) NOT NULL, Age int);Unique- It ensures that all values in a column are different.A Unique constant field or column will not have duplicate data.
Using UNIQUE constraint when creating a Table (Table Level)-Here we have a simple CREATE query to create a table, which will have a column s_id with unique values.
CREATE TABLE Student(s_id int NOT NULL UNIQUE, First Name varchar2(50), Age int);Using UNIQUE constraint after Table is created (Column Level)-
ALTER TABLE Student ADD UNIQUE(s_rollno);The above query specifies that s_rollno field of Student table will only have unique value.
Primary Key- A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
Using PRIMARY KEY constraint at Table Level-
CREATE table Student (s_id int PRIMARY KEY, Name varchar2(60) NOT NULL, Age int);The above command will creates a PRIMARY KEY on the s_id.
Using PRIMARY KEY constraint at Column Level-
ALTER table Student ADD PRIMARY KEY (s_id);Foreign Key- FOREIGN KEY is used to relate two tables.Uniquely identifies a row/record in another table.
Using FOREIGN KEY constraint at Table Level-
In this query, emp_id in table dept_Detail is made as foreign key, which is a reference of emp_id column in emp_Detail table.
Using FOREIGN KEY constraint at Column Level-
ALTER table dept_Detail ADD FOREIGN KEY (emp_id) REFERENCES emp_Detail(emp_id);Check- Ensures that all values in a column satisfies a specific condition.It performs check on the values, before storing them into the database.
Using CHECK constraint at Table Level-
CREATE table Account( Balance number(10,2) CHECK(Balance > 0));The above query will restrict the Balance number to be greater than zero.
Using CHECK constraint at Column Level-
ALTER table Account ADD CHECK(Balance number > 0);Default- Sets a default value for a column when no value is specified.
For example- The below query will create a table named Student and specify the default value for the field AGE as 18.
CREATE TABLE Student(NAME varchar2(10) NOT NULL,AGE int DEFAULT 18);Happy Coding!
Follow us on Instagram @programmersdoor
Join us on Telegram @programmersdoor
Please write comments if you find any bug in above code/algorithm, or find other ways to solve the same problem.
Follow Programmers Door for more.
Comments