“That query’s not sargable” says one developer to another.
“I know it’s not sargable… would you love it more if it had sargability?” replies the other developer.
“What does sargable mean?” you wonder in silence.
Continue readingAuthor: Ian
How to Truncate Tables with Foreign Key Relationships in PostgreSQL
When working with PostgreSQL databases, you may sometimes need to clear out all the data from tables that have foreign key relationships. This process, known as truncation, can be tricky when dealing with interconnected tables.
By default, if we try to truncate a table that is referenced by another table via a foreign key constraint, we’ll get an error that looks something like this: “ERROR: cannot truncate a table referenced in a foreign key constraint“.
You may have encountered this before finding this article. However, all is not lost. Below are two options for overcoming this issue.
Continue readingFix “The new name is already in use as a COLUMN name and would cause a duplicate that is not permitted” in SQL Server (Error 15335)
If you’re getting an error that reads something like “Error: The new name ‘c1’ is already in use as a COLUMN name and would cause a duplicate that is not permitted” in SQL Server, it appears that you’re trying to rename a column with a name that already exists in that table.
Basically, there’s already a column of that name in the table.
Continue readingA Quick Look at the DROP_EXISTING Option in SQL Server
In SQL Server, we can use the DROP_EXISTING option of the CREATE INDEX statement to modify an existing index. It allows us to drop and rebuild an index in one atomic operation. This can be particularly useful when we need to make changes to an index, such as altering its columns, changing its properties, or rebuilding it with different options like fill factor or sort order.
What is User-Defined Integrity?
Maintaining data integrity is critical for ensuring the accuracy, consistency, and reliability of data inside a relational database. Among the various strategies to enforce data integrity, user-defined integrity plays a significant role, especially in customized applications and systems where standard integrity constraints may not suffice.
This article explores the concept of user-defined integrity, its importance, how it is implemented, and scenarios where it can become essential.
Continue readingFix “Cannot alter column because it is ‘COMPUTED'” in SQL Server (Error 4928)
If you’re getting an error that reads something like “Cannot alter column ‘c2’ because it is ‘COMPUTED’” in SQL Server, it looks like you’re trying to alter a computed column.
We can’t alter computed columns.
Continue readingWhat is a One-To-One Relationship?
In relational databases, a one-to-one relationship is a relationship between two tables where each record in the first table corresponds to one and only one record in the second table, and vice versa.
This kind of relationship is less common compared to other relationship types, but it can be useful in certain scenarios. The one-to-many and many-to-many relationships are much more common than the one-to-one relationship in SQL.
Continue readingUnderstanding the NOT NULL Constraint in SQL
In SQL, constraints are essential for maintaining data integrity and ensuring the accuracy and reliability of the data stored within a database. One of the most commonly used constraints in SQL databases is the NOT NULL constraint.
In this article, I provide a quick overview of the NOT NULL constraint, its purpose, syntax, usage, and practical examples.
Fix “Cannot create index on view. The function yields nondeterministic results” in SQL Server
If you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. The function ‘sysdatetime’ yields nondeterministic results…” and so on, in SQL Server, it looks like you’re trying to create an index on a view that returns nondeterministic results.
Indexes can only be created on views that return deterministic results.
Continue readingUnderstanding the DEFAULT Constraint in SQL: A Complete Guide
The DEFAULT constraint is a nifty little feature in SQL databases that simplifies data management by assigning default values to columns in a table. This can help us maintain data integrity in our databases.
Let’s take a look at the DEFAULT constraint.