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
133
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
2
answers
137
views
Enforcing a single "special" foreign row with several regular rows [duplicate]
I have a database table of documents, and a database table of document versions. There is a one to many relationship between document and document version. I'm looking for advice on how to model ...
- 143
0
votes
1
answer
154
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 ...
4
votes
2
answers
635
views
How can I create a partitioned table with the addition of a unique index in SQL Server?
I am creating a partitioned table called TestArticles, specifying several filegroups according to the year of their publication (publishDate). This code (excluding the commented parts) executes ...
- 43
1
vote
2
answers
144
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
1
answer
213
views
Why delete and insert in a CTE works despite UNIQUE constraint?
Given a table with a unique constraint on a column
CREATE TABLE t
(
id int GENERATED ALWAYS AS IDENTITY,
name text NOT NULL UNIQUE
);
Let's create a single record
INSERT INTO t (name) ...
- 31
3
votes
2
answers
581
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,362
0
votes
1
answer
115
views
How to Reorder Primary Key Constraint on a Large TimescaleDB Table Without Downtime?
I am trying to reorder the primary key constraint on sampleTable to improve query performance.
Here are the details:
Database: PostgreSQL (12.18), TimescaleDB (1.7.5)
Table Name: sampleTable
DDL:
...
- 13
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
152
views
Why do I get a unique constraint violation error when updating a logical replica?
I have two docker containers with PostgreSQL. I want to replicate data from existing tables in a database called wiki. I have tried using pglogical and the native logical replication feature. However, ...
- 111
1
vote
1
answer
156
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,362
0
votes
1
answer
145
views
UNIQUE CONSTRAINT on two columns, but duplicates appeared: how to track down and fix? [duplicate]
We have table for users:
CREATE UNIQUE INDEX uk_users_login_socnet ON public.users USING btree (login, socnet)
We had maintenance on our server, during which it was backed up and replicated to ...
- 129
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,362
1
vote
1
answer
439
views
How to attach partition with primary key
I'm struggling to attach a partition to a table with a primary key.
I have a partitioned table Transactions:
create table "Transactions"
(
id bigserial ...
1
vote
1
answer
92
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