If you’ve got a table that has a UNIQUE constraint, there may come a time where you need to remove it. Whether it’s a permanent thing or temporary, you can easily delete the constraint using the following method.
What is a Synonym in SQL Server?
In SQL Server, a synonym is a database object that provides an alternative name for another database object, such as a table, view, stored procedure, or function. Synonyms act as aliases, allowing us to reference objects using different names without changing the underlying object itself.
The underlying object that the synonym references is typically referred to the base object. Either way, the synonym allows us to reference the base object without actually referring to its name (we refer to the synonym’s name instead).
Continue readingFix Error 4512 “Cannot schema bind view” in SQL Server Due to “two-part format” Issue
If you’re getting an error that reads something like “Cannot schema bind view ‘vEmployees’ because name ‘Employees’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself” it could be that you’re trying to create a schema bound view, but you’re not using a two-part format for names (such as the table names within the view).
Continue readingUnderstanding Filtered Indexes in SQL
In 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 reading2 Ways of Creating a Composite Type in PostgreSQL
In PostgreSQL, a composite type is a data type that represents the structure of a row or record. It’s basically a list of field names and their data types.
We can use composite types in many of the same ways we can use simple types, such as in table columns.
Below are two ways of creating composite types in PostgreSQL.
Continue readingUsing SQL INSERT with a Subquery
Ever wanted to populate a table with data from another table? That’s where the INSERT statement with a subquery comes in handy. Using this method, we can insert the full contents of another table, or we can insert just a select number of rows or columns based on certain criteria.
Below are four examples of using the INSERT statement with a subquery, with each one slightly more complex than the previous.
Fix Error 1505 “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” in SQL Server
If you’re getting an error that reads something like “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” it’s probably because you’re trying to create a UNIQUE constraint on a column that already contains duplicate values.
What is a UNIQUE Constraint?
A 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.
List of Array Functions in PostgreSQL
Below is an alphabetical list of functions that we can use when working with arrays in PostgreSQL.
Continue readingHow to Create an Array Column in PostgreSQL
PostgreSQL allows us to create arrays and store them in a database column. When we do this, we can use various array related tools to retrieve data from such arrays, as well as manipulate the data within them.
We do need to define the column as an array column though. If we don’t do this, we will likely run into trouble when we want to retrieve data from the array. For example, we can’t just store an array as the text type and then expect to be able to use subscripts to refer to its individual elements.
Continue reading