Questions tagged [unique-constraint]
DDL UNIQUE constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. The data contained in the column or columns involved is, therefore, useful to uniquely identify a row in the relevant table.
331 questions
3 votes
1 answer
105 views
MERGE causes unique constraint violation when source contains key multiple times
Given two tables counter and cnt_source defined like this: create temporary table counter (key bigint, count bigint); create unique index counter_key_uniq on counter (key); create temporary table ...
- 31
0 votes
1 answer
145 views
How to use sp_rename for an index with a target name that is the same in multiple tables
I am exploring the sp_rename procedure in MSSQL based on input provided in an answer to another question. I have some 50 tables that has been created with a UID column having a random unique ...
1 vote
2 answers
132 views
When can a unique index allow duplicate values in MySQL?
I have an intersecton table to record many-to-many relationships among three tables. Each combination must be unique, so I have a unique index on the three fields. However, using the PHP application I ...
- 121
3 votes
2 answers
576 views
Is there any benefit to declaring an index that contains a primary key as unique?
Suppose that I have a table with many columns that I don't care about, but two that I do: Primary and Secondary. There is a clustered primary key on Primary. CREATE TABLE [dbo].[...
- 1,322
0 votes
2 answers
79 views
Enforcing Data Uniqueness On Certain Period on MariaDB
In postgresql, as shown on this question, in PostgreSQL, you can create a rule that allows certain row to be unique during certain time range/period (for example, you can insert different prices on a ...
1 vote
1 answer
154 views
Should unique filtered indexes include their filtering column?
Context Suppose that you have a unique filtered index on a table. Said index exists only to enforce a constraint. A classic example of such an index would be one making sure that a non-nullable bit ...
- 1,322
2 votes
3 answers
1k views
Are unique filtered indexes considered an antipattern for enforcing constraints?
I have a table, such as the following. Let's called it Fools. FoolID FoolValue IsActiveFoolValue 1 Ultra Foolish 0 1 Super Foolish 1 2 Quite Foolish 1 A business rule is that for each FoolID, we can ...
- 1,322
1 vote
1 answer
89 views
Unique constraint for single structure that simulates relations between tables
Imagine I have several tables in SQL Server 2016: Cat Cat carrier Cat transit box Cat car Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can ...
- 11