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.
283
questions
0
votes
2
answers
27
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
0
answers
25
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
26
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 ...
- 151
2
votes
1
answer
43
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
237
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 ...
- 489
0
votes
1
answer
30
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&...
- 103
1
vote
2
answers
294
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 ...
- 121
5
votes
2
answers
128
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? ...
- 575
0
votes
1
answer
49
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
381
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
81
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 ...
- 113
9
votes
3
answers
3k
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", &...
- 227
1
vote
0
answers
27
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
122
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
5
votes
3
answers
205
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 ...
- 165
2
votes
2
answers
677
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
52
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
47
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
50
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 ...
- 129
0
votes
1
answer
85
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 ...
- 209
0
votes
1
answer
25
views
The possibility to define a composite unique constraint for specific cases
I have two tables: user and address.
User table:
id
email
name
1
henk@example.com
Henk
2
john@example.com
John
Address table:
id
user_id
street
is_default
1
1
Super cool street
1
2
1
Beautiful ...
- 422
1
vote
1
answer
73
views
How can I define a one to many constraint between columns of the same table?
I'm creating an IDs table in PostgreSQL to represent the relationship between multiple foreign IDs - call them a, b and c.
I happen to know that the foreign IDs have one-to-many relationships between ...
- 113
0
votes
1
answer
32
views
Should I drop a btree index on a column after adding a unique constraint?
On one of my existing tables I have a btree index. I am now adding a unique constraint to this column. I know behind the scenes it will add a UNIQUE btree index to this column as well.
In this ...
- 153
1
vote
3
answers
85
views
SQL Server - Unique Constraint Syntax
Is there a functional difference between the tables built in the following two ways?
CREATE TABLE [dbo].[test_uq](
[ID] [int] NOT NULL,
[f1] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[...
- 119
1
vote
2
answers
240
views
How do I simultaneously update unique keys in PostgreSQL?
CREATE TABLE widget (
id serial PRIMARY KEY,
name text NOT NULL,
ordinal int NOT NULL UNIQUE
);
I have the data
id | name | ordinal
----+------+---------
1 | A | 1
2 | B | ...
- 640
1
vote
1
answer
631
views
Problem with a foreign key referencing a primary key which is also part of an unique key (MariaDB)
I created a database using MariaDb. I have the following author table (with id as primary key), which is extended by two other tables, author_personal and author_corporate, representing two different ...
- 113
-1
votes
3
answers
473
views
Advantages / disadvantages of simple vs compound primary keys
I am trying to model for a database. For one part of the problem I have an entity Area, which contains Departments, and each department is assigned specific functions. I have the following.
I have ...
- 13
1
vote
1
answer
87
views
INSERT with ON CONFLICT does not insert rows as expected
We have two tables:
CREATE TABLE listing_master (
id SERIAL PRIMARY KEY,
feed_id character varying(20) NOT NULL,
feed_listing_id character varying(20) NOT NULL,
updated_time timestamp(...
1
vote
1
answer
929
views
Unexpected unique constraint violation during update
In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records ...
- 123
0
votes
0
answers
50
views
Implementing Unique Index by ignoring similar rows in PostgreSQL
I am currently working on creating an append-only PostgreSQL table where I want to insert a row every time a business object is updated or deleted.
One issue I have is to replicate the guarantees ...
- 101
1
vote
1
answer
84
views
How to enforce a column value on duplicate values on postgresql using constraint?
I have the following table:
mytable
---------
id Serial PK
action Integer
model VARCHAR
model_id integer
active BOOL
But due to my logic there cannot be duplicate values having the same values for ...
1
vote
1
answer
112
views
How to avoid errors in server log when conflict fails within a transaction before retry?
We have a table such as
create table pages (
id uuid primary key not null,
parent_id uuid not null references pages(id),
shortname text not null,
shorturl text not null,
constraint ...
0
votes
0
answers
138
views
Trigger to enforce unique constraint on text column (Geometry) to resolve INSERT conflicts
I have a simple table with a geometry column like so:
CREATE TABLE geomtable
(
gid serial,
geom geometry,
trip_count integer
);
I want a unique constraint on the geometry column, such that ...
- 111
1
vote
1
answer
47
views
How can I enforce that my composite key columns do not equal one another in MySQL?
I have a pivot table in which there is a composite key made up of two columns that reference the same table. The columns are requester_id and addressee_id and both of them refer to the id field on a ...
- 167
2
votes
2
answers
122
views
SQL Server Unique Constraint on two columns with an exception
Hi all and thanks for your advice.
Expense(SupplierID(Foreign Key), DocumentID(vchar))
I understand how to add a simple unique constraint on two columns. However, if DocumentID = 'NA', I would like ...
1
vote
0
answers
44
views
Apply function in UNIQUE constraint in MySQL 5.7
I would like to add a multi column unique constraint to my MySQL 5.7 database table like this:
ALTER TABLE report_summary
ADD UNIQUE (
tenant_id,
app_id,
date_format(FROM_UNIXTIME(...
- 477
1
vote
2
answers
337
views
Prevent duplicates in MySql without using unique constraint
I have a rules table consist of a column called priority, that must be gapless, incremental and unique.
A user must be able to re-order priority of the rules. For example, if there are 10 rules for a ...
- 165
0
votes
0
answers
102
views
Space concerns for unique constraint on row for time-series db (PostgreSQL / TimescaleDB)
I was wondering if there is a more space-efficient way of doing unique row constraints on time-series DBs. Adding a normal unique key for the entire row will result in an index the same size or ...
- 123
1
vote
2
answers
583
views
Hierarchical data using closure table, how to ensure that a column is unique within the parent node?
I store hierarchical data in a Node-table, combined with a closure table (NodeClosure). Each node has a column "name" that should be unique among the sibling nodes (link to parent-Node + ...
- 111
0
votes
1
answer
202
views
UNIQUE constraint on a specific pairing [duplicate]
I am trying to work out a special UNIQUE constraint: there are many columns in a Users table but only these two are relevant for the question:
id bigint,
active boolean,
...
I want to allow multiple ...
- 103
1
vote
1
answer
1k
views
Does a primary key contain exactly one Null value...?
My work
A primary key is unique and only one contains for a table.
There are many unique keys that can contain in a table.
In SQL Server, the primary key is not Nullable. But, a unique key may ...
- 29
1
vote
2
answers
56
views
3 additional varchar(32) keys and also updating them -- too bad for performance?
I have a table in mind which basicaly would look like this:
table1
--------
id bigserial autoincrement primary key,
token1 varchar(32) unique not null,
token2 varchar(32) unique not null,
...
- 13
1
vote
2
answers
107
views
Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries
I have a 'Users' table with columns user_email, user_company_id and user_status. The user_status column is an enum with values '1' or '0' which represents the users being either active or inactive. Is ...
- 13
10
votes
1
answer
720
views
Best way to model the relationship of unique pairs
I have two tables; one for storing thing and one for storing the relationship between two thing objects.
dbfiddle example
Assume:
AB == BA. Storing both would be redundant
A != B. The ...
- 201
1
vote
0
answers
59
views
Best way to handle this type of constraint
Guidance please. We have a small configuration table where we need to ensure only 1 record has bit checked. In the past, we have done check constraint with function that checks count, which we are ...
- 11
0
votes
0
answers
2k
views
Error: duplicate key value violates unique constraint, but the column does not have a unique constraint? postgreSQL
By the way, I'm trying to automate the below using python, but I've isolated the SQL code and tried it by itself on pgAdmin III, and it still doesn't work.
So I have a table called tableName, and I ...
2
votes
1
answer
306
views
MariaDB handling composite unique indexes that are foreign keys at the same time [closed]
I have a possible issue with how MariaDB (and probably MySQL) handles composite unique indexes that are also foreign keys. Not sure if that is by design and if it is, why does it work like that.
I ...
- 123
0
votes
2
answers
38
views
Count violated unique constraint caused by INSERT statement
I would like to add rows from table2 into table1 and see how many conflicts of the type "duplicate key value violates unique constraint" result from this in PostgreSQL. pkey is a primary key, so there ...
- 268
1
vote
2
answers
72
views
Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement?
My problem
I am running SQL Server 2017 and would like to investigate an error with a MERGE statement like the following
MERGE INTO table
USING
(SELECT ? AS Search_Col) AS SRC
ON table....
- 635
1
vote
3
answers
74
views
When possible, are clustered indexes that are declared as unique more performant than clustered indexes that aren't declared as unique?
When applicable, will using the UNIQUE constraint for a clustered index help improve performance in any way under any scenario?
- 24.2k