Understanding the ON UPDATE SET NULL Option for Foreign Keys in SQL Server

Posted on by Ian

In SQL Server (and relational databases in general), a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.

This relationship is a cornerstone of relational database design, ensuring referential integrity between the related tables. When designing databases, it’s crucial to understand how changes to primary keys in parent tables can affect the foreign keys in child tables.

One of the various options available in SQL Server for handling such changes is the ON UPDATE SET NULL action.

Continue reading →

Fix Error 156 “Incorrect syntax near the keyword ‘ON'” When Using DROP_EXISTING in SQL Server

Posted on by Ian

If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘ON’“, it appears that you’re trying to recreate an existing index, but you’re using the wrong syntax for the DROP_EXISTING argument.

The same error occurs when using the wrong syntax to set DROP_EXISTING to OFF; “Incorrect syntax near the keyword ‘OFF’“.

Either way, to fix this issue, be sure to use the right syntax.

Continue reading →

Understanding the RID Lookup in SQL Server: How It Affects Performance

Posted on by Ian

If you’ve spent any time working with heaps in SQL Server, you may have seen the RID lookup in the query plan. And if you haven’t, chances are it’s because you either didn’t look at the query plan, or the queries used full table scans.

In any case, it pays to understand what a RID lookup is and how it affects query performance. If we ever see RID lookup in the query plan, it could be a good opportunity to see if we can make changes that will improve performance.

Continue reading →

What is a Surrogate Key in SQL?

Posted on by Ian

In SQL and relational database design in general, an important concept is the identification of unique rows in a table. A surrogate key is one method for achieving this.

Understanding what surrogate keys are, and how they differ from natural keys, is crucial for database administrators, developers, and anyone involved in data modeling or management.

In this article we’ll explore what the surrogate key is, how it differs from the natural key, and examples of implementing it in SQL.

Continue reading →

Fix Error 4145 “An expression of non-boolean type specified in a context where a condition is expected” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 4145 that goes something like “An expression of non-boolean type specified in a context where a condition is expected…“, it’s probably because you’re using a boolean-like syntax in the wrong context.

This error occurs when a non-boolean expression is used in a context where a condition (i.e., something that evaluates to true or false) is expected. This often happens in IF statements, CASE expressions, or WHERE clauses when a value that isn’t a boolean is mistakenly used as a condition.

Even when we do use a boolean value, we can still get this error if we use it in the wrong context.

Continue reading →

4 Ways to Insert Composite Data in PostgreSQL

Posted on by Ian

When we have a column that’s defined as a composite type in PostgreSQL, we have some options when it comes to inserting data. For example, we can explicitly specify each individual field of the composite type, or we can use a row constructor to insert all fields at once.

Below are four different options for inserting composite values into a column in PostgreSQL.

Continue reading →

What is a Superkey Key in SQL?

Posted on by Ian

There are various key types in SQL, each with their own characteristics. Some of these can be enforced with a constraint of one kind or another. Others are not necessarily enforced with a constraint, but are more of an abstract notion, albeit, an important one. These key types consist of one or more columns with certain characteristics that qualify it as the particular key.

One such key type is the superkey.

Continue reading →

Understanding Self-Referencing Foreign Keys: A Beginner’s Tutorial

Posted on by Ian

In relational databases, a foreign key is typically a field that is linked to another table‘s primary key field in a relationship between two tables.

However, there’s also a type of foreign key we call the self-referencing foreign key. The self-referencing foreign key refers to a field within the same table, creating a relationship between rows in the same table.

Continue reading →

What is a Trigger in SQL?

Posted on by Ian

In SQL databases, triggers can play a significant role in automating responses to specific events within a database. Whether we’re dealing with data validation, logging changes, or enforcing business rules, SQL triggers can be a handy tool to ensure that certain actions are taken automatically when certain conditions are met.

This article covers what SQL triggers are, how they work, and provides an example to illustrate their use.

Continue reading →