Data Validation in SQL

When contributing data directly to an SQL database, when a mediating web server or interface doesn’t exist, as is the case at some smaller companies, validation checks become all the more necessary because mistakes are bound to happen. What if instead of adding the correct price of a t-shirt, the price submitted was -29.99? That would mean that if a customer wanted to buy that t-shirt, your company would have give the customer 29.99, and that wouldn’t be good! Instead, when a mistake happens a validation check could help throw the employee an error so the mistake can be corrected.

In this blogpost, I’ll discuss three kinds of validation checks and how to implement them during table creation or after!

NOT NULL constraint

If someone is adding data quickly, they might forget to add certain necessary pieces of information like price in a new table row. The NOT NULL constraint prevents this from occurring.

To implement this constraint when creating a table add ‘NOT NULL’ after column type.

CREATE TABLE products (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50),
   department VARCHAR(50),
   price INTEGER NOT NULL
   weight INTEGER
);

To add this constraint on a preexisting table, you would use the following command:

ALTER TABLE products
ALTER COLUMN price
SET NOT NULL;

However, this command would only work on a table without any null values in the desired column, so be sure to update your table prior to adding this check!

Using this format also allows you to add a default value in case an omission occurs:

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
price INTEGER DEFAULT 9999,
weight INTEGER
);

And on a pre-existing table:

ALTER TABLE products
ALTER COLUMN price
SET DEFAULT 9999;

RANGE check

In the example from the introduction, a typo might cause a critical error. The RANGE constraint can prevent values outside of a certain range from being added.

CREATE TABLE products (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50),
   department VARCHAR(50),
   price INTEGER CHECK (price > 0),
   weight INTEGER
);

To do this on a preexisting table, you would use the following command:

ALTER TABLE products
ADD CHECK (price > 0);

However, this command would only work on a table without any values below 0 in the desired column, so be sure to update your table prior to adding this check!

We can also use this format on multiple columns! In a job application table, we might not want an applicant’s previous employment to have an end date prior to the start date.

In this case we would add a check of the following format:

CREATE TABLE employment_history(
   id SERIAL PRIMARY KEY,
   company_name VARCHAR(50),
   role VARCHAR(50),
   start_date TIMESTAMP,
   end_date TIMESTAMP,
   CHECK (end_date> start_date)
);

To alter a pre-existing table:

ALTER TABLE employment_history
ADD CHECK (end_date> start_date);

UNIQUE check

In a scenario where you want to allow users to only have one row, a UNIQUE check can prevent multiple records from being created.

To implement this when creating a table, you would add UNIQUE after the column type.

CREATE TABLE employees (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50) NOT NULL UNIQUE,
   department VARCHAR(50)
);

To do this on a preexisting table, you would use the following command:

ALTER TABLE employees
ADD UNIQUE (name);

However, this command would only work on a table where the values in the desired column are already unique, so be sure to update your table prior to adding this check!

You might be wondering why there are parenthesis around the column name. Well, imagine if we just hired John Smith in Marketing, but we already have a John Smith in Engineering! This would mean that we would have to delete John Smith in Engineering in order to add John Smith in Marketing with our current configuration, which I’m sure would upset John Smith in Engineering.

However, the good news is the parentheses allow us to check for uniqueness across multiple columns, something that I’m sure both John Smiths will appreciate!

To do this in a pre-existing table, we would add the department column to our parenthetical statement:

ALTER TABLE employees
ALTER COLUMN name
ADD UNIQUE (name, department);

To accomplish this while creating a table, we would add this unique argument at the end of our CREATE TABLE.

CREATE TABLE employees (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   department VARCHAR(50),
   UNIQUE(name, department)
);

At a small company that cannot implement checks on the server level, SQL validation checks become all the more crucial. I would like to thank Stephen Grinder again for his SQL and PostgreSQL course on Udemy, which you should really check out if you’re learning SQL. Until next week!