Now that we have a gentle introduction to databases and SQL, it’s time to start with Constraints and Queries. 

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.

So, constraints:

Reasons to use constraints

The following constraints are commonly used in SQL:

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Before we can create a new entity/table let’s modify our database design like so

As you can see we've updated the users table with a nre constraint for name. This will allow us to do fast queries over the name parameter/column, example: when we need to do some search by the name. 

Second, we’ve created a new table called Product with all the columns and constraints specification. So, the tables will contain data as follows:

Let’s see how to apply constraints to a current table (User) and to a new table (Product). Let’s start with the users table. 

➜  ~ sudo su - postgres

[sudo] password for daniel: 

postgres@daniel-MS-7A15:~$ psql

psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)

Type "help" for help.

postgres=# \list

postgres=# \list marvel_tshirts

postgres=# \c marvel_tshirts

psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)

You are now connected to database "marvel_tshirts" as user "postgres".

marvel_tshirts=# SELECT * FROM users;

 id |  name  | age 

----+--------+-----

  1 | Justin |  22

  2 | Ana    |  21

  4 | Viktor |  39

(3 rows)

marvel_tshirts=# CREATE INDEX idx_name

marvel_tshirts-# ON Users (name);

CREATE INDEX

marvel_tshirts=# \d+ users

                                   Table "public.users"

 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 

--------+---------+-----------+----------+---------+----------+--------------+-------------

 id     | integer |           |          |         | plain    |              | 

 name   | text    |           |          |         | extended |              | 

 age    | integer |           |          |         | plain    |              | 

Indexes:

    "idx_name" btree (name)

As you can see we have now the index under the column name. Remember that an Index will help us to do queries faster. Later we’ll be seeing queries.

Now let’s add other 3 things to users table and to the “id” column

marvel_tshirts=# ALTER TABLE Users

marvel_tshirts-# ALTER COLUMN id SET not null;

ALTER TABLE

marvel_tshirts=# \d users

               Table "public.users"

 Column |  Type   | Collation | Nullable | Default 

--------+---------+-----------+----------+---------

 id     | integer |           | not null | 

 name   | text    |           |          | 

 age    | integer |           |          | 

Indexes:

    "idx_name" btree (name)

marvel_tshirts=# ALTER TABLE Users

marvel_tshirts-# ADD PRIMARY KEY (id)

marvel_tshirts-# ;

ALTER TABLE

marvel_tshirts=# \d users

               Table "public.users"

 Column |  Type   | Collation | Nullable | Default 

--------+---------+-----------+----------+---------

 id     | integer |           | not null | 

 name   | text    |           |          | 

 age    | integer |           |          | 

Indexes:

    "users_pkey" PRIMARY KEY, btree (id)

    "idx_name" btree (name)

marvel_tshirts=# ALTER TABLE Users

marvel_tshirts-# ADD UNIQUE (id);

ALTER TABLE

marvel_tshirts=# \d users

               Table "public.users"

 Column |  Type   | Collation | Nullable | Default 

--------+---------+-----------+----------+---------

 id     | integer |           | not null | 

 name   | text    |           |          | 

 age    | integer |           |          | 

Indexes:

    "users_pkey" PRIMARY KEY, btree (id)

    "idx_name" btree (name)

    "users_id_key" UNIQUE CONSTRAINT, btree (id)

Awesome, in the last description table print, we have all the constraints necessary to our id column in users. Now let’s create a new table called products according to our designed schema

marvel_tshirts=# CREATE TABLE Products (

marvel_tshirts(# id int NOT NULL UNIQUE,

marvel_tshirts(# name TEXT NOT NULL,

marvel_tshirts(# in_stock BOOLEAN NOT NULL DEFAULT 'false',

marvel_tshirts(# price FLOAT NOT NULL DEFAULT '0');

CREATE TABLE

marvel_tshirts=# \d products

                          Table "public.products"

  Column  |       Type       | Collation | Nullable |        Default        

----------+------------------+-----------+----------+-----------------------

 id       | integer          |           | not null | 

 name     | text             |           | not null | 

 in_stock | boolean          |           | not null | false

 price    | double precision |           | not null | '0'::double precision

Indexes:

    "products_id_key" UNIQUE CONSTRAINT, btree (id)

Now let’s add the Index constraint to the columns according the the database design

marvel_tshirts=# CREATE INDEX idx_id

marvel_tshirts-# ON products (id);

marvel_tshirts=# \d products

                          Table "public.products"

  Column  |       Type       | Collation | Nullable |        Default        

----------+------------------+-----------+----------+-----------------------

 id       | integer          |           | not null | 

 name     | text             |           | not null | 

 in_stock | boolean          |           | not null | false

 price    | double precision |           | not null | '0'::double precision

Indexes:

    "idx_id" btree (id)

    "products_id_key" UNIQUE CONSTRAINT, btree (id)

With all of these we can start doing some operations to check if everything is working right

marvel_tshirts=# INSERT INTO users (id, name, age)

marvel_tshirts-# VALUES (4, 'Rose', 35);

ERROR:  duplicate key value violates unique constraint "users_pkey"

DETAIL:  Key (id)=(4) already exists.

Now we have a nice error, because ID = 4 already exists, and we are constrained to be unique. So this is working. Let’s try with Null

marvel_tshirts=# INSERT INTO users (name, age)

marvel_tshirts-# VALUES ('Rose', 35);

ERROR:  null value in column "id" violates not-null constraint

DETAIL:  Failing row contains (null, Rose, 35).

Awesome, we also have the error related to Null Values constraint in place. Now let’s check how products behave

marvel_tshirts=# INSERT INTO products (name)

marvel_tshirts-# VALUES ('Captain America');

ERROR:  null value in column "id" violates not-null constraint

DETAIL:  Failing row contains (null, Captain America, f, 0).

marvel_tshirts=# INSERT INTO products (id, name)

VALUES (1, 'Captain America');

INSERT 0 1

marvel_tshirts=# SELECT * FROM products;

 id |      name       | in_stock | price 

----+-----------------+----------+-------

  1 | Captain America | f        |     0

(1 row)

Here what we have is:

As you can see the constraints are very important in the SQL world, and we have to do our best to figure out what will be the behavior of the database and then try to design it before writing a single line of code. However this is sometimes not possible for different reasons, so the best we can do is to tru to design it on our own and then change the structure of the constraints. This is a common task under database systems. 

In real case scenarios, we’ll have different layouts of constraints, and there are 3 main ones. 

The constraints we’ve just studied are database constraints, they are built to keep the consistency and integrity of the data, and it helps us to have more clear data which is good for the projects. However we’re limited by the kind of constraints we can do at this level. For instance if we want to validate if a datetime column is under a given timeframe it will be hard to do this at a database level, so probably it's better to use a model validation. 

Later in Rails, we’ll be seeing what a Model is, for now we just need to understand that we have limits in the database constraints. Another example is if we want to check if an email is valid or not and if it already exists in the database or if it is unique. In thi case we can do two things, validate from the form in the UI of the webapp (e-commerce) and check from the model if the record already exist or from the database level to have it as a unique

So, this means that we have multiple ways to keep the consistency of the database and we have to keep in mind the 3 layers when we’re designing our databases. 

Now, let’s do an exercise with the concepts we’ve just learned. Remember to try this on your own before to see our solution

1- Create a table called friends with three columns:

2- Under your current code, add Jane Doe to friends whose birthday is May 30th, 1990.

3- Let's make sure Jane has been added to the database:

SELECT * 

FROM friends;

Check two things: Has the friends table been created? Has Jane Doe been added to it?

4- Let's continue, let's add two of your friends to the table. Insert an id,name, and birthday for each of them.

5- Jane just got married! Her last name is now "Smith". Update her record in friends.

6- Add a new column called email. * We didn't see this operation on the blog post, but you can find a solution on the Internet. 

7- Update everyone's email in your table. Jane's email is [email protected]. 

8- Wait, Jane Smith is not a real person. Remove her from friends.

Great job! Let's look at the result one last time:

SELECT * 

FROM friends;

Our anwer

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

marvel_tshirts=# CREATE TABLE friends (

marvel_tshirts(# id INTEGER,

marvel_tshirts(#   name TEXT,

marvel_tshirts(#   birthday DATE

marvel_tshirts(# );

marvel_tshirts=# INSERT INTO friends (id, name, birthday) VALUES (1, 'Jane Doe', '1990-05-30');

marvel_tshirts=# SELECT * 

marvel_tshirts-# FROM friends;

 id |   name   |  birthday  

----+----------+------------

  1 | Jane Doe | 1990-05-30

marvel_tshirts=# INSERT INTO friends (id, name, birthday) VALUES (2, 'Emiliano', '2006-09-26');

marvel_tshirts=# INSERT INTO friends (id, name, birthday) VALUES (3, 'Maria', '1986-09-02');

marvel_tshirts=# UPDATE friends                                                 

SET name = 'Jane Smith'

WHERE id = 1;

marvel_tshirts=# ALTER TABLE friends

ADD COLUMN email TEXT;

marvel_tshirts=# UPDATE friends

SET email = 'emilianitogmail.com'

WHERE id=2;

marvel_tshirts=# UPDATE friends

SET email = '[email protected]'

WHERE id=3

marvel_tshirts=# UPDATE friends

SET email = '[email protected]'

WHERE id=1;

UPDATE 4

marvel_tshirts=# SELECT * 

FROM friends;

 id |    name    |  birthday  |        email        

----+------------+------------+---------------------

  1 | Jane Smith | 1990-05-30 | [email protected]

  2 | Emiliano   | 2006-09-26 | emilianitogmail.com

  3 | Maria      | 1986-09-02 | [email protected]

(3 rows)

marvel_tshirts=# DELETE FROM friends

marvel_tshirts-# WHERE id = 1;

DELETE 4

marvel_tshirts=# SELECT * 

FROM friends;

 id |   name   |  birthday  |        email        

----+----------+------------+---------------------

  2 | Emiliano | 2006-09-26 | emilianitogmail.com

  3 | Maria    | 1986-09-02 | [email protected]

(2 rows)

I hope you learned a lot with this blog post

Thanks for reading

Daniel Morales