While the primary key is almost certainly the most discussed key in SQL, there are other types of keys that we shouldn’t forget. One such key is the alternate key.
In this article, we’ll look at the concept of the alternate key in SQL.
Continue readingThere 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 readingOne 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 readingDomain 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 readingWhen working with relational databases, data accuracy and consistency are paramount. When we talk about maintaining the integrity of the data within a relational database, several types of integrity come into play. One of the most fundamental is entity integrity.
This concept is crucial for ensuring that each record within a database table is uniquely identifiable, which in turn supports the reliability of the data. In this article, we will delve into what entity integrity is, why it matters, and how it is implemented in practice. We’ll also look at an example to clarify the concept further.
Continue readingIn SQL (and computer science in general), a Boolean expression is a logical statement that evaluates to either true or false. Some RDBMSs provide a boolean data type that can store values that are either true or false (or unknown). PostgreSQL is one such RDBMS.
In PostgreSQL, the Boolean type is called boolean and it uses 1 byte.
When looking at ways to fix slow running SQL queries, one of the first things we might do is look at what indexes are available to support the query. If none are available, then we might look at creating one. And if there is an index, perhaps it doesn’t cater for the query as well as it could.
Indexes are often created on a single column, but this isn’t the only way to create an index. We can also create indexes on multiple columns. These are typically known as composite indexes.
Continue readingWhen writing SQL queries, we sometimes find ourselves including a subquery in the query in order to get the results we want. Sometimes the subquery we include could be taken out of that query and run by itself to return its own independent result set. But not the correlated subquery.
A correlated subquery relies on the outer query for its data. We can’t simply move it outside of the outer query and expect it to work.
Let’s take a look the correlated subquery in SQL and observe it in action with a few examples.
Continue readingIn SQL, indexing can be a great way to improve query performance across our database. We can create indexes to support the most commonly used queries, and these indexes can help our queries run much faster.
But it’s not always a matter of just “adding an index” and leaving it at that. If we really want the best bang for our buck, we usually need to look at how best to implement the index. There are many decisions that go into designing the best index for the job. And one such decision might be, should this be a filtered index?
But what exactly is a filtered index, and how can it improve our database efficiency?
Let’s find out.
Continue readingA UNIQUE constraint is a rule we apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows. This means that no two rows in the table can have the same value(s) in the column(s) where we’ve applied the UNIQUE constraint. It’s an essential tool for maintaining data integrity and preventing duplicate entries in our database.