SQL Constraints
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 can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly
--------------------------
SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
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.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
-------------------
SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
-------------------
Setting environment for using XAMPP for Windows.
venkat@VENKAT-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.37-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> show tables
-> ;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| ram |
| ram1 |
| ram2 |
+-------------------+
3 rows in set (0.07 sec)
MariaDB [vlrinst]> create table r1(
-> id int,
-> name varchar(30),
-> age int,
-> check(age>=18)
-> );
Query OK, 0 rows affected (0.30 sec)
MariaDB [vlrinst]> desc r1
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.12 sec)
MariaDB [vlrinst]> insert into r1 values(22,"ram",30);
Query OK, 1 row affected (0.07 sec)
MariaDB [vlrinst]> insert into r1 values(23,"anj",16);
Query OK, 1 row affected (0.08 sec)
MariaDB [vlrinst]> select * from r1;
+------+------+------+
| id | name | age |
+------+------+------+
| 22 | ram | 30 |
| 23 | anj | 16 |
+------+------+------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> CREATE TABLE Persons (
-> ID int NOT NULL,
-> LastName varchar(255) NOT NULL,
-> FirstName varchar(255),
-> Age int,
-> CHECK (Age>=18)
-> );
Query OK, 0 rows affected (0.18 sec)
MariaDB [vlrinst]> insert into persons values(1,"chan","ram",3);
Query OK, 1 row affected (0.09 sec)
MariaDB [vlrinst]> drop table persons;
Query OK, 0 rows affected (0.23 sec)
MariaDB [vlrinst]> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| r1 |
| ram |
| ram1 |
| ram2 |
+-------------------+
4 rows in set (0.00 sec)
MariaDB [vlrinst]> CREATE TABLE Persons (
-> ID int NOT NULL,
-> LastName varchar(255) NOT NULL,
-> FirstName varchar(255),
-> Age int,
-> UNIQUE (ID)
-> );
Query OK, 0 rows affected (0.24 sec)
MariaDB [vlrinst]> insert into persons values(1,"chan","ram",3);
Query OK, 1 row affected (0.06 sec)
MariaDB [vlrinst]> insert into persons values(1,"chan","ram",3);
ERROR 1062 (23000): Duplicate entry '1' for key 'ID'
MariaDB [vlrinst]> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| persons |
| r1 |
| ram |
| ram1 |
| ram2 |
+-------------------+
5 rows in set (0.00 sec)
MariaDB [vlrinst]> drop table ram;
Query OK, 0 rows affected (0.16 sec)
MariaDB [vlrinst]> drop table ram1;
Query OK, 0 rows affected (0.21 sec)
MariaDB [vlrinst]> drop table ram2;
Query OK, 0 rows affected (0.16 sec)
MariaDB [vlrinst]> drop table r1;
Query OK, 0 rows affected (0.14 sec)
MariaDB [vlrinst]> drop table persons;;
Query OK, 0 rows affected (0.14 sec)
ERROR: No query specified
MariaDB [vlrinst]> show tables;
Empty set (0.00 sec)
MariaDB [vlrinst]> create table r19
-> ;
ERROR 1113 (42000): A table must have at least 1 column
MariaDB [vlrinst]> create table r1(
-> id int,
-> age int,
-> check(age>=18)
-> );
Query OK, 0 rows affected (0.23 sec)
MariaDB [vlrinst]> create table r2(
-> id int,
-> name varchar(20),
-> uinque(name)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near '(na
me)
)' at line 4
MariaDB [vlrinst]> create table r3(
-> id int,
-> name varchar(25),
-> unique(name)
-> );
Query OK, 0 rows affected (0.25 sec)
MariaDB [vlrinst]> desc r3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(25) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh");
Query OK, 1 row affected (0.06 sec)
MariaDB [vlrinst]> insert into r3(id) values(2);
Query OK, 1 row affected (0.07 sec)
MariaDB [vlrinst]> select * from r3;
+------+--------+
| id | name |
+------+--------+
| 1 | ramesh |
| 2 | NULL |
+------+--------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> insert into r3(id) values(3);
Query OK, 1 row affected (0.06 sec)
MariaDB [vlrinst]> select * from r3;
+------+--------+
| id | name |
+------+--------+
| 1 | ramesh |
| 2 | NULL |
| 3 | NULL |
+------+--------+
3 rows in set (0.00 sec)
MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh");
ERROR 1062 (23000): Duplicate entry 'ramesh' for key 'name'
MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh");
ERROR 1062 (23000): Duplicate entry 'ramesh' for key 'name'
MariaDB [vlrinst]> insert into r3(id) values(1);
Query OK, 1 row affected (0.04 sec)
MariaDB [vlrinst]> select * from r3;
+------+--------+
| id | name |
+------+--------+
| 1 | ramesh |
| 2 | NULL |
| 3 | NULL |
| 1 | NULL |
+------+--------+
4 rows in set (0.00 sec)
MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh");
ERROR 1062 (23000): Duplicate entry 'ramesh' for key 'name'
MariaDB [vlrinst]> insert into r3(id,name) values(1,"ramesh3");
Query OK, 1 row affected (0.06 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.