What is a Many-To-Many Relationship?

In SQL, a many-to-many relationship occurs when multiple records in one table can be associated with multiple records in another table.

To represent this type of relationship, a third table—often referred to as a “junction table” or “associative entity”—is used. This junction table typically contains foreign keys that reference the primary keys of the two related tables, allowing for the connection between multiple records.

Continue reading

Explanation of ON DELETE NO ACTION in SQL Server

In SQL Server, ON DELETE NO ACTION is an option that we can apply to foreign key constraints to prevent the deletion of a row in the parent table if there are related rows in the child table.

Unlike ON DELETE CASCADE, which would delete the related rows in the child table, NO ACTION enforces that if a deletion would result in orphaned records in the child table, the deletion operation is prohibited, and an error is raised.

Continue reading

Fix Error 7999 “Could not find any index…” in SQL Server

If you’re getting SQL Server error 7999 that reads something like “Could not find any index named ‘IX_LastName’ for table ‘Employees’“, it appears that you’re trying to create an index with the DROP_EXISTING argument ON, but there is no existing index of that name.

When we set the DROP_EXISTING argument to ON, SQL Server tries to look for the index to drop before it recreates it with the new definition. If it doesn’t exist, then we get the error.

Continue reading

Compound Keys Explained

In SQL databases, a compound key is a type of primary key that consists of two or more columns combined to uniquely identify each row in a table. The key columns are used together as a single unit to ensure uniqueness.

Some within the SQL community define compound keys as composite primary keys comprising of foreign keys from other tables, so there doesn’t seem to be an agreed consensus on the precise definition.

With that in mind, let’s explore these definitions of compound keys.

Continue reading

Fix “Incorrect syntax near the keyword ‘Order'” in SQL Server (Error 156)

If you’re getting an error in SQL Server that reads “Incorrect syntax near the keyword ‘Order’“, it could be because you’re using the word Order (or another reserved keyword) in your SQL code.

The 156 error itself merely indicates a syntax error, so there could be a multitude of reasons you might be getting this error. But when it refers to the keyword 'Order', this could suggest that you’re trying to use the word Order as an identifier (such as a column name, table name, etc).

Continue reading

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

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

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

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