SQL/MySQL Constraints

CodeWorld19
10 Apr, 2020
SQL/MySQL Constraints


Topic : SQL/MySQL Constraint


Objectives

in this session we have seen about constraint in SQL or MySQL. and explain each constraint with example.

AGENDA

What is Constraint ?
NOT NULL Constraint
DEFAULT Constraint
UNIQUE Constraint
PRIMARY KEY Constraint
FOREIGN KEY Constraint
CHECK Constraint
INDEX Constraint

What is Constraint ?

SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints are the rules enforced on data columns on table. these are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraint are applied to the whole table.

Following are commonly used constraints available in SQL:
  • NOT NULL Constraint : Ensures that a column cannot have a NULL value.
  • DEFAULT Constraint : Sets a default value for a column when no value is specified.
  • UNIQUE Constraint : Ensures that all values in a column are different.
  • PRIMARY Constraint : A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
  • FOREIGN KEY Constraint : Uniquely identifies a row/record in another table.
  • CHECK Constraint : Ensures that all values in a column satisfies a specific condition.
  • INDEX Constraint : Used to create and retrieve data from the database very quickly.

1. NOT NULL Constraint :

The NOT NULL constraint enforces a column to NOT accept NULL values. By default a column can hold NULL values. if you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that  column. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

Example : 
MariaDB [Batman]> CREATE TABLE EMP
    -> (
    -> EMP_Id int NOT NULL,
    -> LastName varchar(255) NOT NULL,
    -> FirstName varchar(255),
    -> Address varchar(255),
    -> City varchar(255)
    -> );
Query OK, 0 rows affected (0.514 sec)

2. DEFAULT Constraint

The DEFAULT Constraint provides a default value to a column when the INSERT INTO statement does not provide a specific values. The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified.

 Example : 
MariaDB [Batman]> CREATE TABLE EMP
    -> (
    -> EMP_Id int NOT NULL,
    -> LastName varchar(10),
    -> FirstName varchar(10),
    -> Address varchar(10),
    -> City varchar(20) DEFAULT 'PUNE'
    -> );
Query OK, 0 rows affected (0.308 sec)
you can also use function() to get date and time in DEFAULT constraint.

 Example 2 : 
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

3. UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE Constraint prevents two records from having identical values in a particular column.

Examples : 
MariaDB [Batman]> CREATE TABLE EMP
    ->     (
    ->     EMP_Id int NOT NULL UNIQUE,
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Address varchar(255),
    ->     City varchar(255)
    ->     );
Query OK, 0 rows affected (0.286 sec)

Example 2 : 
MariaDB [Batman]> CREATE TABLE EMP
    ->     (
    ->     EMP_Id int NOT NULL,
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Address varchar(255),
    ->     City varchar(255),
    ->     UNIQUE (EMP_Id)
    ->     );
Query OK, 0 rows affected (0.286 sec)

4. PRIMARY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key.

 Example : 
MariaDB [Batman]> CREATE TABLE EMP
    ->     (
    ->     EMP_Id int NOT NULL UNIQUE,
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Address varchar(255),
    ->     City varchar(255),
    ->     PRIMARY KEY (EMP_Id)
    ->     );
Query OK, 0 rows affected (0.286 sec)

5. FOREIGN KEY Constraint

A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key. A FOREIGN KEY in one table points to a PRIMARY KEY in another table. The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

 Example : 
MariaDB [Batman]> CREATE TABLE EMP
    ->     (
    ->     EMP_Id int NOT NULL UNIQUE,
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Address varchar(255),
    ->     City varchar(255),
    ->     PRIMARY KEY (EMP_Id)
    ->     );
Query OK, 0 rows affected (0.286 sec)
MariaDB [Batman]> desc EMP;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| EMP_Id    | int(11)      | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.029 sec)
MariaDB [Batman]> CREATE TABLE SAL
    ->     (
    ->     ID INT NOT NULL,
    ->     EMP_ID INT,
    ->     SAL INT NOT NULL,
    ->     PRIMARY KEY (ID)
    ->     );
Query OK, 0 rows affected (0.301 sec)
MariaDB [Batman]> ALTER TABLE SAL ADD FOREIGN KEY (EMP_ID) REFERENCES EMP(EMP_Id);
MariaDB [Batman]> desc SAL;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| ID     | int(11) | NO   | PRI | NULL    |       |
| EMP_ID | int(11) | YES  | MUL | NULL    |       |
| SAL    | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.002 sec)
in the above example thir are two tables EMP and SAL the FOREIGN KEY is in SAL table the EMP_ID is references to EMP table EMP_Id.

6. CHECK Constraint

Ensures that all values in a column satisfies a specific condition. The CHECK constraint is used to limit the value range that can be placed in a column. The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered the table. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

 Example 1 : 
MariaDB [Batman]> desc SAL;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| ID     | int(11) | NO   | PRI | NULL    |       |
| EMP_ID | int(11) | YES  | MUL | NULL    |       |
| SAL    | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [Batman]> ALTER TABLE SAL
    -> MODIFY SAL INT NOT NULL CHECK (SAL >=5000);
Query OK, 0 rows affected (0.028 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Batman]> DESC SAL;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| ID     | int(11) | NO   | PRI | NULL    |       |
| EMP_ID | int(11) | YES  | MUL | NULL    |       |
| SAL    | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [Batman]>

Example 2 : 
MariaDB [Batman]> CREATE TABLE EMP
    ->     (
    ->     EMP_Id int NOT NULL CHECK (EMP_Id > 0),
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Address varchar(255),
    ->     City varchar(255),
    ->     UNIQUE (EMP_Id)
    ->     );
Query OK, 0 rows affected (0.286 sec)

7. INDEX Constraint

The INDEX is used to create and retrieve data from the database very quickly. Index can be created by using single or group of columns in a table. The users cannot see the indexes, they are just used to speed up searches/queries. Proper indexes are good for performance in large databases, but you need to be careful while creating an index. A Selection of fields depends on what you are using in your SQL queries.

Syntax : 
CREATE INDEX index_name
ON table_name ( column1, column2, column3.....);

Example : 
MariaDB [Batman]> DESC SAL;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| ID     | int(11) | NO   | PRI | NULL    |       |
| EMP_ID | int(11) | YES  | MUL | NULL    |       |
| SAL    | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.001 sec)
MariaDB [Batman]> CREATE INDEX IND_SAL
    -> ON SAL (SAL);
Query OK, 0 rows affected (0.301 sec)
Records: 0  Duplicates: 0  Warnings: 0

CodeWorld19

CodeWorld19

Hey, I am Parthe Omkar

Next Post Previous Post
No Comment
Add Comment
comment url