SQL | Constraints

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:

1. Column level constraints: Limits only column data.

2. Table level constraints: Limits whole table data.

The following constraints are commonly used in SQL:

1. NOT NULL

2. Unique

3. Primary key

4. Foreign key

5. Check

6. 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.

#blog #interview #placement #learn #computer #science