1,384 questions
2
votes
2
answers
222
views
MySQL: Soft Delete + Unique Constraint [duplicate]
I understand that soft deletion is useful for preserving data history and avoiding the need to manually handle child records.
However, soft deletion can cause issues with unique constraints.
For ...
- 324
1
vote
3
answers
235
views
Unique constraint to prevent overlapping ranges? To make sure a BETWEEN statement for two columns will always only return one row?
We have two columns, id_begins and id_ends. We use a BETWEEN statement to select the row where a specific id number will fit between the values in those two fields (including the begins and ends ...
- 9,514
3
votes
0
answers
86
views
Hibernate MappingException - Foreign key column count mismatch with referenced primary key
I am encountering an error when trying to save data using JPA and Hibernate. The error message is:
Caused by: org.hibernate.MappingException: Foreign key (FKi3uxs48nod3arim7hhpwx1anb:role_group [...
0
votes
1
answer
63
views
Duplicate Key violation unique constraint PostgreSQL History Tables
I have table_1 and table_1_history in PostgreSQL. I have setup a trigger for insert, update, delete on table_1. So any changes in table_1 will reflect to table_1_history and will create a new row. ...
- 65
1
vote
2
answers
130
views
Manage the order of updates when using a filtered unique index?
I have a filtered unique index defined as:
builder
.HasIndex(e => new { e.UserId, e.IsDefault })
.HasFilter($"[{nameof(MyEntity.IsDefault)}] = 1")
.IsUnique()
....
- 830
0
votes
0
answers
44
views
EF Core 9, inserting into a MySQL table with Composite Unique Constraint Best practice? [duplicate]
I'm working with a MySQL table that has a composite unique constraint on two columns (column1 and column2), like this:
CREATE TABLE example_table (
column1 INT,
column2 INT,
column3 ...
- 85
0
votes
1
answer
32
views
Odoo modue Event Registration duplicate key value violates unique constraint "utm_medium_unique_name"
Using the module Event (website_event_sale), there is an error occuring for some (few) of the customers in front office, when they click on the REGISTER-button that bring to the cart:
ERROR xxxxx-oerp-...
- 1,720
1
vote
1
answer
64
views
Does Hibernate support unique constraint inside @TableGenerator
I tried generating the values for my primary keys using @tableGenerator annotation. Since this annotation will create a table and generate the value from it, I want to place a unique constraint on the ...
- 89
0
votes
1
answer
182
views
pymysql.err.IntegrityError - 1062, "Duplicate entry - Unique constraint
Below is my update query :
update table_name
set Name = concat('zzzz', Name)
where ID = 100
The table has unique constraint on ID and Name -
UNIQUE KEY `UK_table_name` (`ID`,`Name`)
ID and Name are ...
2
votes
1
answer
56
views
Adding a unique constraint that includes a nullable value
I have the following entity model class:
public class RoutePermission
{
public Guid? UserId { get; set; }
public Guid? DistrictId { get; set; }
public Guid RouteId { get; set; }
public ...
- 138
1
vote
1
answer
102
views
Best practice for unique constraint: multiple columns VS generated column
I have several tables in which "whole" items are stored across multiple columns for each of their "parts," and I wish to ensure that the "whole" is unique. These tables ...
- 1,093
0
votes
1
answer
490
views
How can I avoid "ERROR: there is no unique constraint matching given keys for referenced table" on referencing combination primary key on Postgres?
I have the following table definition in the schema on pgAdmin4 I'm trying to execute:
CREATE TABLE IF NOT EXISTS Reporting
(
SEQ integer,
Product character varying(30) NOT NULL,
Version integer ...
- 25
3
votes
2
answers
1k
views
Why does PostgreSQL check a DEFERRABLE INITIALLY IMMEDIATE constraint after each statement?
I run the following SQL that never commits the transaction:
rollback;
begin;
create table testing_stuff (
id serial,
num integer NOT NULL unique deferrable initially immediate
);
insert into ...
- 14.7k
0
votes
2
answers
152
views
Type Error while populating a model in Django with UniqueConstraint
I'm trying to build up my first project in Django and I'm having some issues while creating an entry into my model Prevision (including sales forecasts), which has a uniqueConstraint to ensure that ...
- 303
0
votes
1
answer
121
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
...
- 61
0
votes
0
answers
35
views
How to add more than one user to the database (something to do with sparse and unique)
Here's my User Schema:
const userSchema = new mongoose.Schema({
username: {
type: String,
required: true,
unique: true,
trim: true,
lowercase: true,
...
- 13
1
vote
0
answers
27
views
How to prevent MySQL table from accepting multiple rows with NULL column value [duplicate]
How can I configure a MySQL table to prevent multiple rows having NULL in a given column when all other column values are equivalent?
I have a table with the following structure to store the favorite ...
- 141
1
vote
2
answers
172
views
Appying Unique Constraint on Text and Text[] column in pgsql
I am trying to avoid duplicate entries in the table based on type and names however the unique constraint not getting applied.
CREATE TABLE IF NOT EXISTS test
(
id integer NOT NULL DEFAULT ...
- 105
0
votes
0
answers
39
views
Django: Adding unique together errors to one of the involved fields
Note: I asked this question on the Django forum, but since I did not receive any answers there, I'm asking here, too.
I’m building an application with multi-tenancy, meaning that all data is ...
- 2,985
0
votes
1
answer
30
views
django constraint in multi-tenant application
I have a multi-tenant application where we use multi-currency.
The simplified models looks like:
class Currency(models.Model):
multi_tenant_company = models.ForeignKey(MultiTenantCompany, ...
- 2,981
1
vote
1
answer
964
views
How to create unique constraint that are deferrable in PostgreSQL with null values?
I am creating a table in PostgreSQL v14.9 with a unique constraint on two columns: A and B where B can be null. As PostgreSQL does not consider the absence of values as equal (NULL) it will allow rows ...
- 23
0
votes
2
answers
147
views
Unique index on 3 columns where NULL conflicts with all other values in one column
I have a table like this:
CREATE TABLE "users"
(
id serial PRIMARY KEY,
town text NOT NULL,
street text not null,
building text
);
I want to be able to store unique entries ...
- 399
0
votes
2
answers
336
views
Postgresql Primary Key Index Maintenance
I refresh my indexes in my Postgresql database with the "reindex index concurrently" command. However, I never renewed my primary key indexes because it might damage my primary key indexes ...
0
votes
0
answers
84
views
Unique conflict between deleted records and creation of new records [duplicate]
Well, I am facing uniqueness conflicts, because as I am using softDeletes(), the deleted records present in the database are conflicting with the new registered records, as the name column has the ...
1
vote
1
answer
554
views
Does unique key order matters if the WHERE statement have all columns?
Assume people in another planet have unique combination in gender,age,name
gender:0~1
age:0~100
name:unlimited possibilities
And my query will use these 3 columns, so always return one(or zero) person,...
- 49
1
vote
1
answer
54
views
How can i drop a unique constraint which do not have any id from the column in sql
create table frontend_employee(
employee_id int primary key,
employee_name varchar(50),
employee_role varchar(50),
employee_sarlary int default 25000,
employee_boss_id int unique,
foreign key(...
0
votes
0
answers
109
views
sqlite3.IntegrityError: UNIQUE constraint failed (Python)
I am using Python and Sqlite 3 for a program
I have created following two tables in my database
theCursor.execute("""CREATE TABLE IF NOT EXISTS
taxpayers(txp_id TEXT PRIMARY KEY, ...
0
votes
0
answers
33
views
Same records being added on unique constraints
I have added a unique together (badge_id, speech_id, badge_type) however I can still add data with same information, Attaching my schema as well
I can explain one thing.
unique constrain was applied ...
- 799
0
votes
1
answer
173
views
Find and update duplicate values held in a column with a unique constraint? - Msg 402 The data types are incompatible in the add operator
I need to create a SQL Server query to clean up an old database by ensuring all non-unique values in columns with a unique restraint are updated to be unique.
To do this I was thinking to find all ...
- 4,969
1
vote
0
answers
307
views
Postgres deadlock happened with multi-column unique constraint when upsert single record
Abstract
What is 'speculative token'?
Why deadlock is happened? I've tried upsert just single row with insert.. with on conflict.. do update syntax.
There was UNIQUE INDEX index_foo_date ON table_a ...
- 43
3
votes
2
answers
1k
views
Adding unique constraint for null column returns error
I am trying to add a unique constraint to an existing table in Postgres on a null column named personnel_id. Most of the records have null personnel_id. Here's how I do it:
ALTER TABLE "myschema&...
- 10.6k
0
votes
1
answer
963
views
How to make unique key constraint made with multiple fields case insensitive in prisma?
Can anyone tell me how to make this unique constraint case insensitive? I was looking something like
@@unique([name, size, color], options:{caseInsensitive: true})
I am using prisma clientVersion: 5....
- 11
1
vote
1
answer
804
views
Using where clause in constraint or check constraint [duplicate]
I have a requirement where unique constraint should be based on certain condition.
If the status column is "LIVE" it should not allow another live record for the same algo_id.
When I put ...
- 11
0
votes
1
answer
110
views
Unique Constraint depending on value of a field
I need to create a unique constraint for my subscription table for not allowing a user to have two active subscriptions at the same time. I created the migration and updated my entity class:
CREATE ...
- 609
0
votes
1
answer
690
views
Postgres merge silently ignore unique constraint violation
The following quote is from here: https://pganalyze.com/blog/5mins-postgres-15-merge-vs-insert-on-conflict
The comment that he essentially made is that the downside of MERGE's
handling of concurrency ...
- 166
2
votes
1
answer
376
views
Hibernate - unique constraint violation on update
Consider the situation: I have table with two columns ID and PRIORITY.
The PRIORITY column has unique constrain.
Example data for the table is:
| ID | PRIORITY |
| -------| ---------|
| 1001 | ...
- 21
0
votes
2
answers
45
views
Unique constraint on 2 columns on condition of value in third column in Oracle
I need the combination of the values of two columns (X and Y) to be unique, but only if a third column ('STATUS') has a certain value. If the third column has a different value than duplicates in the ...
- 2,189
0
votes
3
answers
665
views
Postgresql unique constraint mystery that does not seem to work with batch inserts
I have a table with a unique constraint over 5 columns, all nullable=false. These columns are varchar(30), varchar, 3 float8 columns. There is an autoincrement id column, but that is not included ...
- 3
0
votes
1
answer
339
views
MariaDB is not Using Hash Unique Key on InnoDB
I create a table with a text column with unique key:
create table a(id int auto_increment key,a text unique);
And when I run show create table a;, it says that the unique key is using hash:
CREATE ...
- 15
0
votes
1
answer
50
views
MySQL UNIQUE on a combination of columns
Consider the case where I have a table with players data and a table where I want to store matches in a tournament. Say each match has 3 players and what I want to achieve is uniqueness of the three ...
-1
votes
2
answers
341
views
postgres query multiple tables to check if a value already exists
I have two tables A and B each with unique constraints for appid and name columns to ensure name is unique for each appid.
However, now I also need to ensure that name value is unique across the both ...
- 4,161
2
votes
1
answer
369
views
jpa-buddy generates ddl schema with not all unique constraints
I have this entity:
@Getter
@Setter
@Entity
@Table(name = "publications")
public class Publication {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "...
0
votes
1
answer
458
views
Inserting data to a implicit many-to-many relationship in Prisma
I am building a website similar to LinkedIn/indeed and am facing difficulties when trying to update a job row when a user applies to a job. I am using Nest.js, Postgres and Prisma as the ORM.
Here are ...
3
votes
2
answers
183
views
How to set unique-constraint index in RavenDB 5.4?
Their documentation doesn't clarify this and ChatGPT always give me incorrect code (probably from legacy versions of RavenDB)
I have been using the code below to set the unique constraint, but the ...
- 663
1
vote
0
answers
147
views
Insert values in unique constraint with case sensitive in SQL Server
I have to insert values with case sensitive in a table where it has unique constraint.
it looks like below example.
create table test1(
name1 varchar(100) unique)
insert into test1 select 'a'
insert ...
0
votes
0
answers
51
views
Error creating table with foregin key constraint referencing multiple columns from same table: Postgresql
I am creating tables in following steps-
create table student(rollno varchar(8) primary key,
name varchar not null,
admitted_on date,
...
- 696
0
votes
1
answer
57
views
Django - Model - Is it possible to use dict key value in UniqueConstraint combined with other attributes
Short version of the question: Is it possible to use dict key value in UniqueConstraint. If not, advice on implementing the logic described;
*I use postgreSql
In django, I have a model which;
has a ...
- 487
0
votes
1
answer
77
views
Cannot set login id when using flask admin
I'm using flask admin to allow admins to perform CRUD operations. So when adding a new user the website should automatically generate and set the unique login id for that user. I'm trying to implement ...
1
vote
1
answer
841
views
One row table postgres
I am trying to create a table which can only hold one row. Following table works fine and you can't insert more than one row in this table. But when I try to update that row using ON CONFLICT, it ...
- 17.3k
2
votes
0
answers
1k
views
Catch multiple constraints with ON CONFLICT upsert pgsql
I use an INSERT ON CONFLICT UPDATE upsert to perform regular syncs from low to high environments. I defer all constraints before the upsert, however I recently had to add a partial unique index which ...
- 63