Hello. In this tutorial, we will learn different constraints in SQL and will use the postgresql database running on Docker.
1. Introduction
SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –
- Parsing – Process to check the SQL query syntax
- Binding – Process to check the SQL query semantics
- Optimization – Process to generate the SQL query execution plan
1.1 Usage of SQL
Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –
- Execute queries against the database
- Retrieve data from the database
- Insert new records into the database
- Update existing records into the database
- Created stored procedures, functions, and materialized views in the database
- Create users and grant permissions
- Set permissions on tables, stored procedures, functions, and materialized views
1.2 Constraints in SQL
There are different constraints in sql and are as followed –
- NOT NULL – The not null constraint in sql ensures that a column cannot have a null value. It is applied as a column constraint
- UNIQUE – The unique constraint in sql ensures that a value entered into a column is unique. It is applied as a column constraint or group of column constraints
- CHECK – The check constraint in sql allows a user to define a condition on the column. The value to be inserted in the column has to satisfy the condition before it can be inserted
- DEFAULT – The default constraint in sql sets a default value for a column if the value of that column is missed during the insertion
- PRIMARY KEY (PK) – It is a column constraint and ensures that the PK column value must be unique for each row in a table. It is different from the UNIQUE constraint as 1 table can contain only 1 PK while there can be more than 1 UNIQUE constraint. A PK can be represented for 1 or a combination of more columns. A PK column cannot contain a null value
- FOREIGN KEY (FK) – A FK is a combination of the column with values based on PK from other tables. It is also called a Referential integrity column where the value of the FK column matches with the PK column value of another table
2. Constraints in SQL
Let us dive into some practice implementation on the postgresql database.
2.1 Pre-requirement – Postgres Setup
Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.
Docker commands
-- command to run postgres on docker -- -- remember to change the password -- docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the postgresql database server would be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool for connecting to the server.
2.2. Creating a database
To practice this tutorial I will be using the learning database. You can below command to create a database.
Create database sql query
-- create database create database learning
2.3 Understanding NOT NULL and PRIMARY KEY (PK) constraints
We will create a table named – vehicle with a not null and primary key constraint. We will insert a sample record to it and view it.
Sql query
-- example1: not null and primary key (pk) constraints
-- create a table with a not null and primary key constraint
create table vehicle(
id serial not null,
name varchar(200) not null,
year_of_manufacturing numeric(4, 0),
primary key(id));
-- the below insert query will get inserted in the vehicle
insert into vehicle (name, year_of_manufacturing) values ('maruti', 1983);
-- the below select query will print the record(s) present in the table
select * from vehicle;
If everything goes well the below result will be shown.
Now we will add another record to the above table but will keep the name column value as null. The record will throw an error while the sql insert operation is getting performed.
Invalid sql query
-- the below insert query will not get inserted into the vehicle table as the name is null insert into vehicle (name, year_of_manufacturing) values (null, 1997); -- below error is thrown by the invalid query -- error: sql error [23502]: error: null value in column "name" of relation "vehicle" violates not-null constraint
2.4 Understanding UNIQUE, DEFAULT, and PRIMARY KEY (PK) SQL Constraints
We will create a table named – order_details with a not null, default, and primary key constraints. We will insert a sample record to it and view it.
Sql query
-- example2: unique, default, and primary key (pk) constraints -- create a table with not null, primary key, and default create table order_details( id serial not null primary key, order_id integer not null, order_date timestamp with time zone default current_timestamp, quantity integer, comments varchar(100), constraint order_id_unique unique (order_id)); -- the below insert query will get inserted in the order_details table but order_date will be auto-filled because of the default constraint insert into order_details (order_id, quantity, comments) values (100, 1, 'order1'); -- the below insert query will get inserted in the order_details table but with the user-specified date -- here the default specified for the order_date will not invoked as user date is specified insert into order_details (order_id, order_date, quantity, comments) values(101, '2021-09-02 10:00:16.198 +0530', 5, 'order2') -- the below select query will print record(s) present in the table select * from order_details;
If everything goes well the below result will be shown.
Now we will add another record to the above table but use the order_id of the already inserted record. The record will throw an error while the sql insert operation is getting performed.
Invalid sql query
-- the below insert query will throw an error because we are trying to duplicate the order_id insert into order_details (order_id, quantity, comments) values (100, 10, 'order3'); -- below error is thrown by the invalid query -- error: sql error [23505]: error: duplicate key value violates unique constraint "order_id_unique"
2.5 Understanding CHECK constraint
We will create a table named – salary with a check constraint. We will insert a sample record to it and view it.
Sql query
-- example3: check constraint -- create a table with a check constraint create table salary( id serial not null primary key, emp_id int not null, salary numeric check(salary > 1000)); -- the below insert query will get inserted in the salary table insert into salary (emp_id, salary) values (1, 5000);
If everything goes well the below result will be shown.
Now we will add another record to the above table but keep the salary less than 1000. The record will throw an error while the sql insert operation is getting performed.
Invalid sql query
-- the below insert query will get failed because the salary < 1000 insert into salary (emp_id, salary) values (1, 850); -- below error is thrown by the invalid query -- error: sql error [23514]: error: new row for relation "salary" violates check constraint "salary_salary_check"
2.6 Understanding FOREIGN KEY (FK) constraint
We will create 2 tables named – customer and contact with an FK constraint. We will insert a sample record into the tables and view them.
Sql query
-- example4: foreign key constraint
create table customer(
id serial not null primary key,
name varchar(200));
create table contact(
id serial not null primary key,
customer_id int,
phone varchar(100),
constraint fk_customer foreign key(id) references customer(id));
-- record gets inserted to the customer
insert into customer (name) values ('james');
-- record gets inserted as the customer_id 1 is present in the customer table
insert into contact (customer_id, phone) values (1, '1234567890');
select * from customer;
select * from contact;
If everything goes well the below result will be shown.
Now we will add another record to the contact table but the customer_id will be one not present in the customer table. The record will throw an error while the sql insert operation is getting performed.
Invalid sql query
-- record will not get inserted as the customer_id 2 is not present in the customer table insert into contact (customer_id, phone) values (2, '0987654321'); -- below error is thrown by the invalid query -- error: sql error [23503]: error: insert or update on table "contact" violates foreign key constraint "fk_customer"
3. Summary
In this tutorial, we learned the brief of sql, different CONSTRAINTS, and their practical implementation via different examples. You can download the sql scripts used in this tutorial from the Downloads section.
4. Download the Constraints in SQL scripts
This was a tutorial to understand different CONSTRAINTS in sql.
You can download the full source code of this example here: Constraints in SQL
