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.
326
questions
3
votes
2
answers
387
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].[...
- 661
0
votes
1
answer
26
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
37
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
24
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
90
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 ...
- 661
0
votes
1
answer
52
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
593
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 ...
- 661
1
vote
1
answer
67
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
51
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
0
votes
1
answer
53
views
How to Create a Case-Insensitive Unique Compound Index in MongoDB?
How to Create a Case-Insensitive Unique Compound Index in MongoDB?
Question Body: I'm trying to create a compound unique text index in MongoDB with the following fields:
tenant_id: 1 seller_id: 1 ...
0
votes
2
answers
109
views
Performance issue - using try catch to return data to service & how to use partition table with unique index
I'm working on enviorment SQL azure.
I've got a stored procedure that runs from several AKS simultaneously
and insert a message.
So it's a race for who writes the message first and the rest fails.
The ...
- 47
0
votes
1
answer
130
views
Why is Ignore Duplicate Keys not allowed on a Unique Key?
If I understand this answer correctly the difference between an Index with Is Unqiue on and a Unique Key is mainly cosmetic.
In the MSSQL interface when I switch to a Unique Key the Ignore Duplicate ...
- 385
4
votes
1
answer
260
views
Unique index on computed column when contributing columns are already unique
I have a table like this:
CREATE TABLE
dbo.DiscountBarcodeList
(
ID int NOT NULL IDENTITY
CONSTRAINT PK_DiscountBarcodeList
PRIMARY KEY CLUSTERED
, Discount int NOT NULL
CONSTRAINT ...
- 23.1k
0
votes
1
answer
16
views
PostgreSQL Tables Without Unique Constrtaint On Sequence
I use PostgreSQL. Is there a query to get a list of all tables that do not have the unique constraint on the sequence column? If it helps forming a query I use "reference" for my sequence ...
- 187
0
votes
1
answer
181
views
Unique Index on NULLable field isn't creating a Unique Constraint so I can make it a FK on another table?
I'm in MS SQL 2016. I'm a bit confused. I have a field that I have put a unique index on. The DocID is not a primary key, but it will be unique (and is NULLable):
CREATE UNIQUE INDEX [...
- 607
0
votes
1
answer
28
views
Existing tables adding composite foreign key constraints
I've got the above set of tables shown in the ER Diagram above.
members have many users.
members have many approval_groups.
approval_groups have many approval_group_users.
approval_group_users....
- 179
0
votes
2
answers
32
views
MariaDB- Can i create a unique key requirement if the dates are the same but allow it if they are different?
I am trying to create an unique index on barcode and date. Is it possible to have make barcode unique unless date is different than inserted row's (date) being inserted?
I created a unique index on ...
3
votes
1
answer
87
views
Enforce unique constraint without denormalization
Option 1
I have a database schema that looks something like this:
https://dbfiddle.uk/JKCTjadD
A Task belongs to a Service (like in ECS)
A Task can be reassigned to another Service.
TaskConfig tracks ...
- 133
1
vote
1
answer
513
views
Postgres - Is it possible to have a `UNIQUE INDEX` with `NULLS NOT DISTINCT`?
Given this table definition with a partial index:
BEGIN;
CREATE TABLE user_note (
user_id VARCHAR NOT NULL,
item_id VARCHAR,
note VARCHAR NOT NULL,
archived_at TIMESTAMP
);
CREATE UNIQUE ...
- 113
0
votes
2
answers
123
views
Is a Composite UNIQUE Constraint On an Already UNIQUE Column Redundant?
I'd just like to clarify something pretty simple regarding single and composite UNIQUE constraints that I can't find a clear answer to.
Assume I have the following table:
CREATE TABLE person (
id ...
- 101
0
votes
1
answer
969
views
Error "UNIQUE constraint failed" with composite primary key
With littele SQL(ite) practice, I tried to create a table with a composite primary key:
CREATE TABLE NAMES (
TYPE INTEGER NOT NULL,
SEQ INTEGER NOT NULL,
NAME VARCHAR(20),
PRIMARY KEY (TYPE, ...
- 125
0
votes
1
answer
225
views
Does a long string with "unique" constaint take a lot of space?
I've created a table "global_settings" for storing different settings of a web applications -- the ones, that I want to be able to edit on html page:
global_settings:
id primary key,
key ...
- 45
1
vote
1
answer
2k
views
Drop unique index of a unique constraint
I'm currently working on a postgresql 15 database.
I've created a data table named "measures" with following schema
CREATE TABLE measures (
timestamp TIMESTAMPTZ NOT NULL,
value double ...
- 31
1
vote
1
answer
20
views
Key constraints/indexes/etc... confusion on my part
I have two tables, each carry a column labelled "CN" which is a GUID value generated by my application. The first table holds user data, the second table holds logon data (in this case, salt ...
- 33
1
vote
2
answers
2k
views
How can I insert into a Postgres table with a unique constraint without waiting on concurrent transactions?
I have a table like this in a Postgres database:
CREATE TABLE tabwithunique (
id SERIAL PRIMARY KEY,
key BIGINT NOT NULL UNIQUE,
value BIGINT NOT NULL
);
I want to design a statement that ...
- 121
0
votes
1
answer
60
views
Is UPSERT feature complete?
Here, a lot of energy seems spent on Coalesce - I think it is to avoid the case where a null value in the update record could overwrite a non-null value.
Say we have a table of businesses and some ...
- 33
0
votes
1
answer
359
views
Trying to drop UNIQUE but fails due to dependencies, but can't find any
I have a table with a UNIQUE KEY which I need to remove, however I get the following error when I try to:
SQLSTATE[HY000]: General error: 1553
Cannot drop index '...
1
vote
1
answer
246
views
Why can't three non-unique indexes be used to enforce a unique constraint?
I have a large PostgreSQL table of 3 columns, I created a separate btree index on every single column. Then, I created a multicolumn unique constraint on the table on all the 3 columns, expecting the ...
- 11
1
vote
1
answer
51
views
How to solve this problem for hierarchical data structure?
I have this table called Hierarchies. This is its actual definition:
CREATE TABLE `Hierarchies` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`Guid` uuid NOT NULL DEFAULT uuid(),
`Key` varchar(400) ...
- 1,485
0
votes
2
answers
1k
views
Add unique conditional constraint to exsiting table
I want to add a unique conditional constraint to an existing table in oracle but I get an error.
ALTER TABLE T_EVENTATTENDEE ADD CONSTRAINT EVENT_ATTENDEE_UNIQUE UNIQUE (CASE WHEN C_DELETED = 0 THEN ( ...
3
votes
1
answer
236
views
How to "merge" rows along with their foreign many-to-many relations without violating unique constraints?
Fiddle: https://dbfiddle.uk/-JLFuIrN
Table
CREATE TABLE files (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text
);
CREATE TABLE folders (
id bigint GENERATED ALWAYS AS IDENTITY ...
- 276
0
votes
2
answers
478
views
How to define constraint having two columns with boolean column to be true only once
Let the table to be considered is this:
create table users(
email varchar(50) not null,
is_verified boolean default false,
);
Now, how can I define the exclusion constraint here so that if the ...
- 103
0
votes
1
answer
403
views
PostgreSQL duplicate non-null values despite unique constraint
I have a database with a unique constraint in a field that is apparently being ignored. That table also includes two rows with the same value for that field, but somehow querying by that value only ...
- 3
-1
votes
1
answer
2k
views
PostgreSQL claims my query has violated unique key constraint on id [closed]
I'm struggling with an error, and I think it has to do with PostgreSQL. I'm trying to insert values in a database created 3-4years ago. The java program use Hibernate and lombok.
The original program ...
- 117
4
votes
1
answer
717
views
UPSERT based on unique combination of (INTEGER, NULL), (NULL, INTEGER)
My table, for simplicity, looks like this:
id | foreign_key_1 | foreign_key_2 | value
As clear from the names, the fields foreign_key_1 and foreign_key_2 refer to PKs in two other tables.
The tricky ...
- 209
0
votes
0
answers
115
views
Check Existence Before Creating - Select, Insert, on conflict return
I've the following code that creates mapbox vector tiles and inserts them for storage. In the final code each xyz tile will be unique. Before the below code I would like to check the storage table to ...
0
votes
1
answer
156
views
Upserts while enforcing uniqueness over a table column, plus a set of joined records
I'm on Postgres 12.8.
I have a table of data points and tags attached to them:
CREATE TABLE data_points (
id INT GENERATED BY DEFAULT AS IDENTITY,
date DATE NOT NULL,
value INT NOT NULL,
...
- 111
10
votes
3
answers
2k
views
Is there a performance penalty when there are two unique indexes on a table with all of the colums in one index are also in the other index?
In my database I have a table with two indexes. There is one unique index #1 on columns a,b which I need for data integrity reasons. I have another index #2 with columns c,a,b that is used for ...
- 235
0
votes
1
answer
72
views
How to filter a table remove duplicated keys and keep unique value for each key in one table?
The input data and output example as the picture.
I tried to use pandas to filter the data after I try query:
select * from tablename;
is there any better way like only using a SQL query to do this ...
1
vote
1
answer
4k
views
Unique constraint on partitioned table
PostgreSQL has a limitation on unique constraints on partitioned tables:
Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation ...
- 493
1
vote
1
answer
169
views
How to deal with duplicate customers?
This is probably common knowledge for DBAs, but in my database I am struggling with a simple registration of customers.
Customers can sign up using a form (we use typeform), meaning we create customer ...
- 113
2
votes
2
answers
621
views
Allow multiple rows to have same X value only if also same Y value?
Given a (postgres) table,
CREATE TABLE links (
id bigint NOT NULL,
url text NOT NULL,
owner_id bigint NOT NULL
);
I'd like to allow multiple rows to have the same url if and only if they ...
- 552
2
votes
0
answers
697
views
PostgreSQL unique constraint not being enforced
It seems like one particular unique constraint on one particular table is currently not being enforced on every insert. I'm stumped as to how these constraint violations keep happening, as I'm unable ...
- 121
1
vote
1
answer
1k
views
How to have uniqueness constraints for structures across multiple tables?
Say I have a schema system something like this:
create table objects {
uuid id;
string type;
}
create table object_properties {
uuid id;
uuid source_id; // the object which has this property
...
- 211
0
votes
1
answer
1k
views
MySQL Duplicate entry for key unique key
I have a table with a unique key which is a combination of 4 columns. Column A,B,C and D.
The values in the columns are like this:
Column A
Column B
Column C
Column D
111000
150100_XYZ_XY02
150100-...
- 101
4
votes
1
answer
577
views
In a SQL database, how can I add a unique constraint that depends on a value in a foreign key linked table?
As an example, I have 2 tables:
devices
-----------------
id (Primary Key)
logical_address
physical_location (Foreign Key (physical_locations.id))
physical_locations
------------------
id (Primary ...
- 43
3
votes
1
answer
2k
views
Postgres constraints: EXCLUDE (name WITH =) vs partial unique index
I am refactoring my database constraint for such a table:
CREATE TABLE products (
name text NOT NULL,
status text NOT NULL
);
A former engineer working on the project had such a constraint:
ALTER ...
- 131
0
votes
1
answer
49
views
How to constrain row values in one table to match row values in another table?
Given:
CREATE TABLE operation
(
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY;
);
CREATE TABLE listing
(
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
operation_id BIGINT REFERENCES ...
- 1,029
1
vote
3
answers
777
views
How Does SQL (In my case SQL Server) Evaluate a Unique Constraint On Multiple Columns?
Given the following table
CREATE TABLE example (
id int NOT NULL IDENTITY(1,1),
strA NVARCHAR(10) NULL,
strB NVARCHAR(10) NULL,
CONSTRAINT [pk_id] PRIMARY KEY (id)
CONSTRAINT [...
- 137
0
votes
0
answers
911
views
PostgreSQL: Concurrent inserts on table with two unique constraints with ON CONFLICT still conflict
I have the following schema:
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE foo (bar JSONB UNIQUE NOT NULL);
CREATE UNIQUE INDEX unique_hash ON foo (digest(bar::text, 'sha256'));
Note that ...
- 101