SQL/MySQL Constraints - Tutorial Boy -->
Home
DBMS

SQL/MySQL Constraints


Objectives

 In this session, we have seen constraints in SQL or MySQL. and explain each constraint with an example.

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 the 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 constraints 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 satisfy a specific condition.
  • INDEX Constraint: Used to create and retrieve data from the database very quickly.

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 [CyberDevil]> 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)

DEFAULT Constraint

  • The DEFAULT Constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. 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 [CyberDevil]> 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 the DEFAULT constraint.

Example 2 :

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

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 [CyberDevil]> 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)

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 [CyberDevil]> 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)

FOREIGN KEY Constraint

  • A foreign key is a key used to link two tables together. This is sometimes also called 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 [CyberDevil]> 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 [CyberDevil]> 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 [CyberDevil]> 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 [CyberDevil]> ALTER TABLE SAL ADD FOREIGN KEY (EMP_ID) REFERENCES EMP(EMP_Id);
MariaDB [CyberDevil]> 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, there are two tables EMP and SAL the FOREIGN KEY is in the SAL table the EMP_ID is referencing to EMP table EMP_Id.

CHECK Constraint

Ensures that all values in a column satisfy 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 [CyberDevil]> 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 [CyberDevil]> 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 [CyberDevil]> 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)

Example 2 :

MariaDB [CyberDevil]> 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)


INDEX Constraint

The INDEX is used to create and retrieve data from the database very quickly. The index can be created by using a 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 [CyberDevil]> 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 [CyberDevil]> CREATE INDEX IND_SAL
    -> ON SAL (SAL);
Query OK, 0 rows affected (0.301 sec)
Records: 0  Duplicates: 0  Warnings: 0



Post a Comment