Constraints and Validations and their Implementation
Published: 2024-04-15 05:40:55

When you open an application to the web many users have access to it. These users have the ability to add data that can cause errors, whether it be intentional or unintentional. To handle this we have constraints and validations. These are mechanisms used to ensure that data meets a certain criteria and avoid errors.

**Constraints** - rules defined at SQL level, they set limits or conditions that data must satisfy before it will be stored in the database. Some frequently used constraints are as follows:

1. **Primary Key Constraint**: Ensures that each row in a table has a unique identifier.

`CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);`

2. **Foreign Key Constraint**: Specifies that the key can only contain values that are in the referenced primary key.

`CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);`

3. **Unique Constraint**: Ensures that values in a column or a group of columns are unique across all rows in a table.

`CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);`

4. **Check Constraint**: Sets a limit the value range that can be placed in a column. Its usage varies whether it's being implemented on a table or a column

`CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);`

5. **Not Null Constraint**: Specifies that a column cannot contain null values.

`CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);`

When code does not satisfy the set constraints it usually results in an error being raised and the data will not be added to the database.


**Validations** - automatic rules or checks at the application level that ensure that the data entered meets a certain criteria. Common types of validations are as follows:

1. **Format Validation**: Enforces that data is in the correct format, commonly used to validate email, phone numbers, ect.

2. **Length Validation**: Checks the length of input data to ensure it falls within acceptable limits.

3. **Range Validation**: Verifies that numeric values are within a specified range.

4. **Presence Validation**: Checks that required fields are not empty or null.

5. **Uniqueness Validation**: Checks that a value is unique within a specific context, often used for setting emails or usernames.

Validations are typically implemented within the application code and prevent invalid data from being persisted to the database. An error message will display to alert the user if the validation is triggered.

What happens when you don't implement constraints or validations? Without these users can input anything they want throughout the application, such as putting letters where a number input is expected, or worse someone inputing SQL commands that are designed to compromise the application into the input field. Other problems include a poor user experience when not alert arises to inform them why an input may have been unsuccessful or loss of data completely without an error to tell you the data did not persist!

References:

https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Constraints/ConstraintTypes/ForeignKeyConstraints.htm#:~:text=A%20foreign%20key%20constraint%20specifies,existing%20table%20with%20ALTER%20TABLE%20.

https://www.w3schools.com/sql/sql_constraints.asp