An Insightful Look Into SQL Constraints and Triggers

SQL Constraints and Triggers are two of the most important concepts in database development. They each play a role in ensuring that the data in the database is consistent, accurate, and secure.

In the previous blog posts of this series, we introduced the concepts of DBMSs and the basics of the SQL query language. In this blog post, we’re going to take a look at constraints and triggers, and how they can be used to maximize the security and accuracy of your data.

Table of Contents

SQL Constraints

Let’s start by looking at SQL constraints. SQL constraints are conditions that are used to limit the data that can be stored in a table. For example, you might use a constraint to make sure that a value is within a certain range, or that it is of a certain data type.

Constraints are enforced by the database engine, which means that if a user tries to enter data that doesn’t meet the constraints, they will be given an error message.

There are a few different types of constraints that you can use in SQL. The most common ones are primary key constraints, foreign key constraints, and check constraints.

The primary key constraint ensures that each row in the table has a unique value for the designated column. This can be used to ensure that two rows don’t have the same value for the primary key column.

The foreign key constraint is used to ensure that a value in one table matches a value in another table. This is usually used to ensure that related data is consistent across multiple tables.

The check constraint is used to make sure that a value meets certain criteria, such as being within a certain range or being of a certain data type.

Example of Constraints

In our previous post, we used the student’s database to demonstrate our concepts, following is an example of constraints we could use to create those tables:

CREATE TABLE Student (
    ID INT NOT NULL, 
    Name VARCHAR(30) NOT NULL, 
    GPA INT NOT NULL,
   Photo BINARY
); 

Now, of course, we could use the PRIMARY KEY constraints and this already forces it to have a not null and unique value. But this is just an example.

SQL Triggers

Now that we’ve looked at constraints, let’s take a look at triggers. Triggers are SQL statements that are automatically executed when certain events occur in the database, such as an INSERT, UPDATE, or DELETE statement.

Triggers can be used to enforce data integrity, ensure data accuracy, or enforce security. For example, a trigger can be created to prevent the deletion of rows from a table.

Examples of Triggers

In the following example, a trigger is created to check if a record is being deleted from the table and if so, it will rollback the delete operation:

CREATE TRIGGER trg_prevent_delete
ON [TableName]
FOR DELETE
AS
BEGIN
    ROLLBACK TRANSACTION
    PRINT 'Delete not allowed'
END;

Triggers can also be used to update other tables when data is changed in a table. For example, a trigger can be created to update a column in another table whenever a record is inserted into the first table.

In the following example, a trigger is created to update the LastUpdated column in the second table whenever a record is inserted into the first table:

CREATE TRIGGER trg_insert_action 
ON [TableName]
FOR INSERT
AS
BEGIN
    UPDATE [SecondTableName] 
    SET LastUpdated = GETDATE()
    FROM Inserted
END;

Using SQL Triggers for Notifications

Another common use of triggers is to send notifications to users when data is changed in a table. For example, a trigger can be created to send an email to a user whenever a record is updated in a table. In the following example, a trigger is created to send an email to a user whenever a record is inserted into the first table:

CREATE TRIGGER trg_send_email
ON [TableName]
FOR INSERT
AS
BEGIN
    DECLARE @Email varchar(50)
    SELECT @Email = [Email] 
    FROM Inserted

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMailProfile',
    @recipients = @Email,
    @subject = 'Record Inserted',
    @body = 'A new record has been added to the table.'
END;

Note that the examples above are rather pseudo code, the actual syntax might be slightly different.

Wrapping up

Using constraints and triggers together can help to maximize the security and accuracy of your data. By using constraints, you can ensure that the data in the database is valid and consistent. And by using triggers, you can automate certain tasks, such as updating related tables or sending notifications.

In conclusion, SQL constraints and triggers are powerful tools for ensuring the security and accuracy of your data. By using both of these concepts together, you can ensure that your data is consistent, secure, and up-to-date.

Further Reading

In the first post of The Relational Database series, I recommended Database Systems The Complete Book as the best comparative book on RDBMS that I have ever read. However as of 2021, I have also read Learning SQL: Generate, Manipulate, and Retrieve Data, which is an outstanding resource for those who wish to gain a better understanding of the language of SQL.

It provides a comprehensive introduction to the language, covering everything from basic syntax to more advanced topics such as database design and optimization. The book is brilliantly written, with clear examples and explanations throughout.

Additionally, the authors have included helpful tips and resources for further study. I highly recommend Learning SQL: Generate, Manipulate, and Retrieve Data to anyone looking to gain a deeper understanding of SQL and its applications.