When a foreign key is defined in a table, it ensures that the values in the column(s) correspond to values in a primary key or unique key in another table. While PostgreSQL automatically creates an index for primary keys (because these need to be fast for lookups and enforcing uniqueness), it does not automatically create an index for foreign keys.
Continue readingHow to Drop a Prepared Statement in MySQL
MySQL provides us with the ability to create multiple prepared statements and run them as many times as we like, while changing the parameter values with each run.
While prepared statements are only available to the session that created them, they are stored in the server. So it’s quite possible that a server could have a build up of lots of prepared statements hanging around once different users have created them.
Continue readingIdentify a Table’s Primary Key’s Index in SQL Server
There are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key’s index.
For example, we may want to identify the index’s name if we need to disable the primary key for some reason (we disable primary keys by disabling their index).
The query below can help if you need to find the name of a primary key’s index for a given table.
Continue readingFix “Syntax ‘*’ is not allowed in schema-bound objects” in SQL Server (Error 1054)
If you’re getting an error that reads “Syntax ‘*’ is not allowed in schema-bound objects” in SQL Server, it appears that you’re using the asterisk wildcard (*) to select all columns when trying to define a schema-bound object (like a schema-bound view).
As the error message states, this is not allowed.
To fix this issue, explicitly name each column.
Continue readingFix “Not unique table/alias” in MySQL (Error 1066)
If you’re getting an error that reads something like “ERROR 1066 (42000): Not unique table/alias: ‘d’” in MySQL, it could be that you’re trying to assign a duplicate alias to a table. Or it could be that you’re doing a self-join without assigning table aliases.
Table names and aliases must be unique when doing queries in MySQL.
The error can also happen if you use HANDLER to open a table, but then try to open it again before closing it.
To fix this issue, be sure to use unique table aliases in your query. And if you’re using HANDLER, either close the table or continue working with it (without trying to open it again).
Column Constraints vs Table Constraints in SQL: What’s the Difference?
In relational database management systems (RDBMSs), constraints are nifty tools that we can use to ensure the integrity, accuracy, and reliability of the data stored in our database.
Constraints can enforce rules at the column and table levels, guiding how data can be inserted, updated, or deleted. Whether you’re defining the uniqueness of a value, establishing relationships between tables, or ensuring that critical fields are never left blank, constraints play an important role in the design of relational databases.
Continue readingUnderstanding the SERIAL Type in MySQL
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.
5 Ways to List Indexes in PostgreSQL
There are several ways that we can list all indexes in PostgreSQL. These include utilizing various system catalog views and psql commands. Below are five options for returning a list of indexes in PostgreSQL, along with examples of how to filter the results by index name, table name, and other criteria.
Return All Indexes on a Table in SQL Server
Sometimes when working with SQL Server, we need to get a quick list of indexes on a given table.
Here are three queries that return all indexes on a given table in SQL Server.
Continue readingHow to Disable a Constraint in SQL Server
Disabling constraints in SQL Server can be useful for various operations, such as bulk data loading or certain maintenance tasks. But not all constraints are created equal. The method we use to disable a constraint, and whether that’s even possible, depends on the type of constraint.
In this article, we’ll explore how to disable different types of constraints and consider the potential impacts.
Continue reading