There are many different “key” types in SQL. We have the primary key, the foreign key, and even the unique key.
But have you heard about the candidate key?
Continue readingConcatenate Array Elements into a String in PostgreSQL
You may be aware that PostgreSQL has a couple of functions that allow us to concatenate strings. In particular, the concat() function allows us to concatenate multiple strings into one string, and the concat_ws() function allows us to do the same, but to also specify a separator for the concatenated strings.
But did you know that we have the ability to pass an array to these functions?
Continue readingFix Error 547 “The DELETE statement conflicted with the REFERENCE constraint…” in SQL Server
If you’re getting SQL Server error 547 that reads something like “The DELETE statement conflicted with the REFERENCE constraint “FK_Projects_DepartmentID”. The conflict occurred in database “test”, table “dbo.Projects”, column ‘DepartmentID’“, you’re probably trying to delete data from a parent table of a foreign key relationship.
Continue readingSQL Atomicity Explained: The Basics of Atomic Operations
One of the core principles that ensure data integrity in relational database management systems (RDBMSs) is atomicity. When dealing with SQL, understanding what “atomicity” and “atomic” means can help you design more reliable and robust databases.
Atomicity is a key concept that underpins how SQL transactions operate, ensuring that either all steps in a transaction are completed successfully, or none are. This article looks at the meaning of atomic in SQL, its importance, and how it fits into the broader context of database management.
Continue readingUnderstanding the ON DELETE SET DEFAULT Option in SQL Server
In SQL Server, foreign keys are essential for maintaining referential integrity between tables. When setting up a foreign key, we have various options for handling actions when the referenced data is deleted. One of these options is ON DELETE SET DEFAULT.
This feature can be particularly useful in scenarios where it’s important to maintain the relationship while ensuring that the foreign key column doesn’t end up with invalid or null values.
Continue readingFix Error 1785 “Introducing FOREIGN KEY constraint … may cause cycles or multiple cascade paths” in SQL Server
If you’re getting SQL Server error 1785 that reads something like “Introducing FOREIGN KEY constraint ‘FK__Name’ on table ‘Department’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints“, it’s probably because you’ve specified the CASCADE option when trying to create a foreign key with a circular reference, or one that has multiple cascade paths (perhaps your child table has multiple foreign keys to the same primary key on the parent table).
What is Domain Integrity?
Domain integrity is an important concept in relational database management that ensures the accuracy, validity, and consistency of data within a database. It refers to the enforcement of rules and constraints that ensure data entered into a database adheres to a predefined set of acceptable values. This helps maintain the quality of data and prevents the entry of erroneous, incomplete, or invalid data into the system.
Continue readingEnlarging an Array in PostgreSQL
In PostgreSQL it’s possible to make an array larger without providing any values. For example, we can take an array with three elements, and enlarge it so that it contains say, five elements, with the two extra elements being NULL.
We can also do it so that some of the extra elements are non-NULL and others are NULL.
Fix Error 1989 “Cannot enable foreign key constraint … as index … on referenced key is disabled” in SQL Server
If you’re getting SQL Server error 1989 that reads something like “Cannot enable foreign key constraint ‘FK_Jobs_Employees’ as index ‘PK__Employee__7AD04FF1A39ECAB1’ on referenced key is disabled“, it’s probably because you’re trying to enable a foreign key, when the index that it references is disabled.
For example, if you’ve disabled a primary key on the parent table, and then try to enable a foreign key that references that primary key, then you’ll get this error.
To fix this issue, enable the index for the primary key/referenced key before enabling the foreign key.
Continue readingCreate an Index in SQL Server
Indexes play an important role in SQL database performance. We can use them to speed up commonly run queries, so that users don’t have to sit and wait for their results to come in.
In SQL Server, indexes can be created in several different situations. For example, when we create a primary key or a UNIQUE constraint, an index is created behind the scenes for us. However, we can also create indexes explicitly using the CREATE INDEX statement.