Keys and Constraints in Relational Database

This post is part 2 of the article series ‘Database in 2021’, in Part 1, we saw basics such as What is Data? , What is Database? Types of Database? CRUD Operations in RDBMS ( PostgreSQL ). I hope you are familiar with all these before you start reading below.

Photo by Bradford Nicolas on Unsplash

What makes a Relational Database actually Relational?

Good Question! As the name suggests, there must be a way we can establish relations between multiple or the same tables or columns, so that we can save more data without making it complex to read.
example — For a franchise company such as MacDonalds or Dominos, they must be having some central data that is common to all stores, Regional Data that is common to some stores, and store data which is basically valid only for that store. In order to store all such data in databases and making sense out of it, a lot of connections between such tables, columns will be made.
In this blog post, we’ll try to make sense of all the cool features RDBMS provides to tackle such types of use cases.
🔑 KEYS in RDBMS —
Keys are nothing but attributes in the table. Keys denote a special crucial characteristic to a column. Different types of keys in RDBMS are
1. Super Key
2. Composite Key
3. Foreign Key
4. Candidate Key
5. Primary Key
6. Alternate Key
7. Surrogate Key
In order to keep the blog short and simple, we’ll only discuss the most widely used keys.
🔑 Primary Key — Primary Key is assigned to the column, which acts as a head column, the primary key will contain a unique ID (0,1,2,3,…) to make it easy for the user to access the row data. Every table will have one and only one Primary key column. When we use software like pgAdmin, it helps us to take care of creating primary key easily. By Default column with the name ‘id’ will be created and treated as Primary key, but if you are dead set on using your own column as primary key, you can also do that as following:

Creating a new table with a custom primary key

The Query Tool way for doing the same will be as following :

CREATE TABLE <tablename> 
(  <column1> <data_type> PRIMARY KEY,  
<column2> <data_type>,  
… );

in our case, it will be

create table store_table
( storeId INTEGER PRIMARY KEY,
storelocation TEXT,
storeSize INTEGER ) ;

All the columns except Primary Key are known as Alternate Keys
🔑 Foreign Keys — Foreign Keys are an important column that helps us to connect 2 tables based on their primary keys. Using pgAdmin we can establish a relationship between multiple tables as follows

Creating Foreign Keys

The Query Tool way to do the same is as follows :

ALTER TABLE <table1> add constraint <col_table1> foreign key(<col_table2>) REFERENCES <table2>(<col_table2>) ON UPDATE CASCADE ON DELETE CASCADE;

In our case, we’ll have to perform this in 2 steps, first is to add a column in the first table to hold the value of a key, and then adding a constraint to it.

-- adding 'amenity_store_id' column to 'store_table' 
ALTER TABLE store_table ADD COLUMN amenity_store_id integer 
-- adding 'foregin key' constraint to 'amenity_store_id' column
ALTER TABLE store_table add constraint amenity_store_id foreign key(amenity_store_id) REFERENCES store_amenities(amenity_id) ON UPDATE CASCADE ON DELETE CASCADE;

🚪 Constraints in RDBMS —
Constraints in a nutshell are rules that column must follow in order to work properly. In Simple words, constraints allow us to be very strict with the column and data it is supposed to hold, so that even someone tries to enter the wrong data, the database will not accept it. Some of the most widely used constraints are as follows :
🚪 Not NULL — Self-explanatory! Isn’t it!! By default, every column has a null value for every entry, which means you can skip filling up information for the specific column if you want. But if you want to stop that behaviour, you can then add Not NULL Constrain to that column. in pgAdmin, you can easily do that by clicking on Nou Null switch in column popup

Not Null switch in the column list

you can also do that in Query Tool as follow :

CREATE TABLE <tablename> 
(<column1> <data_type> NOT NULL,
<column2> <data_type>,
<column3> <data_type> NOT NULL,
    ... );

🚪 Unique — Again! Self-explanatory! Isn’t it!! By default you can enter any value in rows ( as long as data type matches ) but this might create problem in situations where you want each row to have distict value for the column. We can add this contraint my going in table properties using pgAdmin,

adding unique contraint on ‘storelocation’ column

we can also do that in Query Tool as follow :

CREATE TABLE <tablename> 
(<column1> <data_type> UNIQUE,
<column2> <data_type>,
<column3> <data_type> UNIQUE,
    ... );

If we try to insert same value twice, we’ll get following error

Error for duplicate value insertion

🚪 Primary Key & Foreign Key — These are also constriants on column, adding both of these are explained in Keys in RDBMS Section of this article

Primary key can be considered as UNIQUE + NOT NULL constraint

🚪 CHECK — Check Constraints gives us ability to check the value with certain condition before allowing it to be stored. E.g. — Store Size should be positive only ( > 0 ) . in pgAdmin we can do that as follows :

Creating Check constraint for storeSize column

we can also do that in Query Tool as follow :

CREATE TABLE <tablename> 
(<column1> <data_type> CHECK( <condition> ) ,
<column2> <data_type>,
<column3> <data_type> ,
    ... );

If we try to insert value which might not pass the condition , we’ll get following error

Error for not passing the check

About me

Hi, I’m Krishna Lodha, I’m a full-stack Web GIS developer. I’m really enthusiastic about the power of locations and what story it tells us. Read more about me at http://krishnaglodha.com, to get more information about this code or suggest a better way to do it. ping me on LinkedIn 💼 , Twitter 🐦 , and Youtube📺

Adios!