If you work with MySQL databases, you may have encountered the SERIAL type in a table’s definition. And if you’ve come over from PostgreSQL, you might have a false expectation about how MySQL’s SERIAL works – unless you already know 😉
Below is a quick overview of MySQL’s SERIAL type, including an explanation of how it differs from PostgreSQL’s SERIAL type and similar functionality from other DBMSs.
Probably the most widely known key type in SQL is the primary key, which is chosen to uniquely identify each row in a table. Perhaps next is the foreign key, which is used to establish a relationship between tables.
But there are more key types than this, and the differences between them can be subtle, but important. Here we’ll look at nine of the various key types in SQL.
Continue readingProbably the most common way to join tables in SQL is with the ON clause. But that’s not the only way.
We can also join with the USING clause, which can be more concise, while providing the same or similar results. And there’s also the concept of a natural join, which is more concise again.
Let’s take a look at these three join options and compare them side by side.
Continue readingThe \di command can be used to list indexes in the current database when using psql (PostgreSQL’s command-line tool). This meta-command can display all indexes, or just indexes that match certain patterns. Here’s an in-depth explanation of how \di works, its options, and examples of its various uses.
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Whether you’re just starting your journey in data management or looking to refresh your skills, understanding the basic SQL statements is crucial.
This article will walk you through seven fundamental SQL statement examples that are pretty much standard across most major Relational Database Management Systems (RDBMSs).
Continue readingIt’s widely understood among SQL Server developers that foreign keys can prevent us from deleting a row from the parent table if there’s child table that references the key in that row.
But did you know that we can specify a different course of action in such cases?
For example, we could configure our foreign key to delete the child as well. Or we could set it to NULL, or to its default value.
These options are all available, thanks to the ON DELETE clause.
In SQL, a UNIQUE key is a column or set of columns that can uniquely identify a row in a table. These are also candidate keys. Only one candidate key can become the primary key for a table. All other candidate keys can then be referred to as UNIQUE keys, given they can uniquely identify a row in a table.
If you need to do some inserts and updates in MySQL, one option is to run separate INSERT and UPDATE statements. Another option is to use the INSERT ... ON DUPLICATE KEY UPDATE statement. Depending on the scenario, this can be a handy option.
The INCLUDE option in SQL Server allows us to include non-key columns in a nonclustered index. These columns are not part of the index key (which SQL Server uses to order and search the index), but they are stored with the index pages.
The INCLUDE option can significantly improve query performance when additional columns are needed by a query but are not part of the index key.
In MySQL, prepared statements are a feature that allows us to execute the same SQL query multiple times with different parameters. They can improve performance, make our code cleaner, and help defend against SQL injection attacks.
In this article, we’ll explore prepared statements in MySQL, with examples and practical tips on how to use them effectively. We’ll also cover the importance of deallocating statements to free up resources.
Continue reading