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.
307
questions
1
vote
1
answer
59
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
0
votes
0
answers
24
views
how can I add a unique restriction on a column with json data for a specific key
I have this database table
create table db_etennisnlive.bss_custom_option_template
(
template_id int unsigned auto_increment comment 'Template ID'
primary key,
title ...
- 149
1
vote
1
answer
18
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
144
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
38
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
50
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
57
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
0
votes
0
answers
123
views
Aggregating unique values across 2 columns into one array (sql)
Imagine one table called exam in Postgres DB with 2 nullable columns called grade1 and grade2. Is it possible to aggregate all unique grades across both columns into one array without using UNION or ...
- 1
1
vote
1
answer
27
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,327
0
votes
2
answers
215
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
103
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 ...
- 176
0
votes
2
answers
148
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
182
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
556
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
287
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 ...
- 197
0
votes
0
answers
45
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
81
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
8
votes
3
answers
1k
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 ...
- 205
0
votes
1
answer
50
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 ...
0
votes
1
answer
1k
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 ...
- 425
1
vote
1
answer
97
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
462
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
349
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
414
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
...
- 187
0
votes
1
answer
834
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
375
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
0
votes
0
answers
916
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 ...
- 101
0
votes
1
answer
44
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 ...
- 999
1
vote
3
answers
634
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 [...
- 37
0
votes
0
answers
501
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
0
votes
2
answers
1k
views
How to enforce only one NULL value per unique combination of some other columns?
here's the situation, I have a table A with like such
CREATE TABLE A
(
id UUID NOT NULL PRIMARY KEY,
B_id bigint NOT NULL,
C_id bigint NOT NULL,
some_value ...
0
votes
1
answer
248
views
SQLite: excluded int primary key null value replaced with next auto value
I'm trying to create generic upsert query builder with specific behavior. Table has two unique constraints: primary key itId and column slug:
create table it (
itId integer primary key -- an alias ...
0
votes
1
answer
452
views
Unique constraint replaces existing foreign key
I have two tables, parent and child, the child table has a FK on the parent table using the parent_id column. When adding this FK I can see that the child table has both the FK and an index to ...
- 165
2
votes
1
answer
66
views
Selecting gives one row, updating affects 2
When I select using WHERE clause that uses the same columns as a UNIQUE constraint "mytable_uniq_detail_date" (detail_id and date), I get a single row, but when I upsert a row data that (as ...
2
votes
5
answers
833
views
Is a unique index required for a sequential (autoincrementing) id?
If a sequential AUTO_INCREMENT column is not also the primary key of a MySQL table, does it have to have its own UNIQUE index to ensure long-term data integrity?
I'm working on reindexing a particular ...
- 531
0
votes
1
answer
421
views
How can I update the value of a column for two rows that have a unique constraint in SQLite?
I have the following table
CREATE TABLE IF NOT EXISTS node (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
type TEXT CHECK(type IN ("DIRECTORY", "EXECUTABLE", "DOCUMENT&...
- 113
1
vote
2
answers
2k
views
PostgreSQL: INSERT in a sequence and increment all values above the INSERT (make "room" for the INSERT) [duplicate]
I have a table that requires storing my item in a specific position, and the user can "move" the items' positions to "make room" for the new item.
Here is what the table looks ...
- 123
5
votes
2
answers
255
views
Index with IGNORE_DUP_KEY = ON filters out non-duplicate records
Below is a script which can be used to re-produce the issue I am facing.
Basically the question is this: why is it that the record with (ID1 = 6, ID2 = 7) is not populated into the final #tt2 table? ...
- 635
1
vote
1
answer
4k
views
postgresql Modifying existing unique index
I have this table on PostgreSQL:
example_dev=# \d products
Table "public.products"
Column | Type | Collation | Nullable | ...
2
votes
1
answer
1k
views
PostgreSQL relative update breaks unique constraint
I have unique index on column for UPSERT. When I am trying to update column using v = v + 1 expression my unique index breaks.
SQL
CREATE TABLE test(v bigint, data jsonb DEFAULT '{}'::jsonb);
INSERT ...
- 23
0
votes
2
answers
687
views
UPSERT unless the date is the same
There's a table
CREATE TABLE IF NOT EXISTS table00 (
userid int8 PRIMARY KEY,
save date NOT NULL,
value int4
);
where I want to upsert values IFF the date is not the same ...
- 123
13
votes
4
answers
7k
views
Why doesn't my UNIQUE constraint trigger?
I have this UNIQUE constraint:
ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE
("col1", "col2", "col3", "col4", "col5", "col6", &...
- 277
1
vote
0
answers
54
views
Add Unique constraint to existing table with large amount of data
I have an existing table with following schema
create table event_logs
(
event_id integer not null
constraint event_logs_event_id_fkey
references event,
user_id ...
- 111
1
vote
3
answers
404
views
Constraint to prevent immediate loop in hierarchy
I have a companies table:
CREATE TABLE companies (
id bigserial,
name varchar(255) NOT NULL
PRIMARY KEY (id)
)
I want companies to have parents and childs. A company might have many parent and ...
- 13
6
votes
3
answers
747
views
Enforce uniqueness across a one-to-many-to-many bridged relationship when constraining to one-to-many
We have defined a series of configurations, where, driven by a RESTful API, end-users can build up new revisions. Some of the components of the configuration can have more than one value; a revision ...
- 175
2
votes
2
answers
2k
views
Why does MySQL Workbench let me add unique indexes to primary keys?
I'm using MySQL Workbench 8.0.12
Reproduce:
Create a table
Create the first column id, select it as a primary key (PK)
Under indexes, PRIMARY appears
Back under columns, also select id as unique (UQ)
...
- 121
0
votes
3
answers
53
views
How to set constraint sql 2 colume, Data is not available elsewhere and must be used only
How to set constraint sql 2 colume, Data is not available elsewhere and must be used only.
Example is OK
NAME
REV
ID
PI
A
0
1
PA001
A
1
1
PA001
Example is Error
NAME
REV
ID
PI
A
0
1
PA001
A
1
1
...
0
votes
2
answers
80
views
Unique relationship attribute for every pair of entities
I have entities "Student" and "Course" and they are connected with relationship many-to-many called "Exam". The relationship "Exam" has an atribute "Grade&...
- 3
0
votes
1
answer
151
views
How to index "almost" unique columns?
I have a table that saves the needed information to send notifications to users. now the UID columns in this table is almost unique because mostly each user subscribes with one device.
but it is ...
- 149
0
votes
1
answer
678
views
Postgresql: Disallow duplicate value to be inserted if already in any of multiple columns?
Suppose I have 3 columns:
Critical, Medium, Low
And I have data UUIDs to be inserted from another tables. And I want to disallow insertion of same UUID in any of the column if it is already present ...
- 219