Each table has some rules that allow the type of data to be inserted into the table, those rules are called as Constrains. Constrains make sure the integrity of the table and make sure all inserted rows fulfil the requirements. Some SQL Constraints are
- NOT NULL
- DEFAULT
- UNIQUE
- PRIMARY Key
- FOREIGN Key
- CHECK
- INDEX
NOT NULL Constraint
A row in a table can hold a value or can be empty. the NULL keyword marks the absence of a value. The NOT NULL constraint ensures that the column always has some values.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT NOT NULL, apple_name VARCHAR(20), available CHAR(1), available_date DATE );
INSERT INTO apple (apple_name, available, available_date ) VALUES ("Macintosh", 'Y', '2022-01-1');if the table was already created, the NOT NULL Constraint can be added later by ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE );
ALTER TABLE apple MODIFY apple_id int NOT NULL;
INSERT INTO apple (apple_name, available, available_date ) VALUES ("Macintosh", 'Y', '2022-01-1');The column must have values before altering for Constrains
if the NOT NULL constraint is applied on a column which already has some null values, then SQL will give an error as shown below
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE );
INSERT INTO apple (apple_name, available, available_date ) VALUES ("Macintosh", 'Y', '2022-01-1');
ALTER TABLE apple MODIFY apple_id int NOT NULL;DEFAULT
The Default constraint provides a default value when the insert query doesn't provide any value for a specified column.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) DEFAULT 'N', available_date DATE );
INSERT INTO apple (apple_id, apple_name, available_date ) VALUES (1, "Macintosh", '2022-01-1');if the table was already created, the DEFAULT constraint can be added later by ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE );
INSERT INTO apple (apple_id, apple_name, available_date ) VALUES (1, "Macintosh", '2022-01-1');
ALTER TABLE apple MODIFY available CHAR(1) DEFAULT 'N';
INSERT INTO apple (apple_id, apple_name, available_date ) VALUES (2, "Fuji", '2022-02-2');If the table has a DEFAULT constraint, it can be removed later using ALTER command.
UNIQUE
The unique constraint makes sure that all the values of the column must be unique, it doesn't allow 2 different records to have the same value for the column.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , available_date DATE, CONSTRAINT uqniue_apple_name UNIQUE (apple_name));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Macintosh", 'N', '2022-02-2');The UNIQUE constraint can be applied to multiple columns of a single table.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT NOT NULL UNIQUE, apple_name VARCHAR(20) NOT NULL UNIQUE, available CHAR(1) , available_date DATE );
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (6,"PinkLady", 'N', '2022-06-6');if the table was created without the UNIQUE constraint, it can be added later using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , available_date DATE );
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Macintosh", 'N', '2022-02-2');
update apple set apple_name='Fuji' where apple_id=2;
ALTER TABLE apple MODIFY apple_name VARCHAR(20) NOT NULL UNIQUE;
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Macintosh", 'Y', '2022-03-3');multiple columns can be combined to have a UNIQUE value for all columns combined.
UNIQUE constraints on multiple columns can have duplicate values in individual columns, but the combined column value should be unique.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , available_date DATE );
ALTER TABLE apple ADD CONSTRAINT UniqueConstraint UNIQUE(apple_id, apple_name);
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Macintosh", 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Fuji", 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'N', '2022-04-4');The UNIQUE constraints can be dropped using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , available_date DATE, CONSTRAINT uqniue_apple_name UNIQUE (apple_name));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
ALTER TABLE apple DROP INDEX uqniue_apple_name;
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2, "Macintosh", 'N', '2022-02-2');PRIMARY Key
The primary key is the column value using which the row can be referred. The primary key must always be unique within a table and must not be null.
Primary Key is combination of UNIQUE, NOT NULL and INDEX
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) , available_date DATE, PRIMARY KEY (apple_id));
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Fuji", 'Y', '2022-02-2');if the table is created without a PRIMARY Key, it can be added later using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
ALTER TABLE apple ADD PRIMARY KEY (apple_id);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Fuji", 'Y', '2022-02-2');PRIMARY key can be added to multiple columns
PRIMARY constraints on multiple columns can have duplicate values in individual columns, but the combined column value should be unique.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id , apple_name));
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Fuji", 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Macintosh", 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'N', '2022-04-4');if the table is created without PRIMARY key constraint for multiple columns, it can be added later using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
ALTER TABLE apple ADD CONSTRAINT pk_id_name PRIMARY KEY (apple_id, apple_name);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Fuji", 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Macintosh", 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'N', '2022-04-4');The PRIMARY key constraint can be dropped using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_ID));
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Fuji", 'Y', '2022-02-2');
ALTER TABLE apple DROP PRIMARY KEY;
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Fuji", 'Y', '2022-02-2');FOREIGN Key
Data is distributed among multiple tables, so to manage the relationship among tables, the column of the first table must be connected to the column of the second table. A FOREIGN key also known as a REFERENCE Key is used to line different tables together. A PRIMARY key can be a FOREIGN key in another table.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_ID));
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'N', '2022-02-2');
CREATE TABLE vendor (vendor_id BIGINT, vendor_name VARCHAR(20), active CHAR(1), PRIMARY KEY (vendor_id), apple_id INT REFERENCES apple (apple_id));
SHOW CREATE TABLE vendor;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'vendor';
INSERT INTO vendor (vendor_id, vendor_name, active, apple_id) VALUES (1, "Macintosh_Vendor", 'Y', 1);
INSERT INTO vendor (vendor_id, vendor_name, active, apple_id) VALUES (2, "Fuji_Vendor", 'Y', 2);
SELECT * FROM apple a, vendor v WHERE a.apple_id=v.vendor_id;if the table is created without a FOREIGN key reference, it can be added later using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_ID));
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'N', '2022-02-2');
CREATE TABLE vendor (vendor_id BIGINT, vendor_name VARCHAR(20), active CHAR(1), PRIMARY KEY (vendor_id), apple_id BIGINT);
SHOW CREATE TABLE vendor;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'vendor';
INSERT INTO vendor (vendor_id, vendor_name, active, apple_id) VALUES (1, "Macintosh_Vendor", 'Y', 1);
INSERT INTO vendor (vendor_id, vendor_name, active, apple_id) VALUES (2, "Fuji_Vendor", 'Y', 2);
ALTER TABLE vendor ADD CONSTRAINT foreign_key_apple_id FOREIGN KEY (apple_id) REFERENCES apple (apple_id);
SELECT * FROM apple a, vendor v WHERE a.apple_id=v.vendor_id;FOREIGN Key constraint can be dropped using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_ID));
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'N', '2022-02-2');
CREATE TABLE vendor (vendor_id BIGINT, vendor_name VARCHAR(20), active CHAR(1), PRIMARY KEY (vendor_id), apple_id BIGINT, FOREIGN KEY (apple_id) REFERENCES apple (apple_id));
SHOW CREATE TABLE vendor;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'vendor';
INSERT INTO vendor (vendor_id, vendor_name, active, apple_id) VALUES (1, "Macintosh_Vendor", 'Y', 1);
INSERT INTO vendor (vendor_id, vendor_name, active, apple_id) VALUES (2, "Fuji_Vendor", 'Y', 2);
ALTER TABLE vendor DROP FOREIGN KEY vendor_ibfk_1;CHECK
CHECK constraint allows a condition to validate the record before inserting a row into the table. If the condition evaluates to false, the constraint will be violated and the record will not be inserted into the table.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1) NOT NULL CHECK (available = 'Y'), available_date DATE, PRIMARY KEY (apple_ID));
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'N', '2022-02-2');if the table was created without CHECK constraint, then it can be added later using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_ID));
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'N', '2022-01-1');
UPDATE apple SET available = 'Y' WHERE apple_id=1;
ALTER TABLE apple ADD CONSTRAINT available_check CHECK (available = 'Y');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'N', '2022-02-2');
INDEX
INDEX is created in the database to search and retrieve records. PRIMARY Key of the table is by default INDEXED. Indexes improve the performance of database execution but too many indexes will slow down as well.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
EXPLAIN SELECT * FROM apple where apple_id = 1 ;
CREATE INDEX apple_id_index ON apple (apple_id);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
EXPLAIN SELECT * FROM apple where apple_id = 1 ;INDEX can be created on multiple columns combined
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
EXPLAIN SELECT * FROM apple where apple_id = 1 and apple_name = 'Macintosh';
CREATE INDEX apple_id_apple_name_index ON apple (apple_id, apple_name);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
EXPLAIN SELECT * FROM apple where apple_id = 1 and apple_name = 'Macintosh';INDEX can be dropped using ALTER command
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE);
CREATE INDEX apple_id_index ON apple (apple_id);
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
EXPLAIN SELECT * FROM apple where apple_id = 1;
ALTER TABLE apple DROP INDEX apple_id_index;
SHOW CREATE TABLE apple;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'apple';
EXPLAIN SELECT * FROM apple where apple_id = 1;
