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.

Filter by
Sorted by
Tagged with
5 votes
3 answers
476 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 ...
  • 151
0 votes
1 answer
17 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
16 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 ...
1 vote
1 answer
38 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 ...
1 vote
2 answers
55 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 ...
  • 222
2 votes
0 answers
72 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
95 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 ...
  • 155
0 votes
1 answer
247 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-...
4 votes
1 answer
121 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 ...
0 votes
0 answers
101 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 ...
0 votes
1 answer
39 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 ...
  • 949
1 vote
3 answers
404 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 [...
0 votes
0 answers
175 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 ...
0 votes
2 answers
299 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
49 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
101 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
47 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
411 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 ...
  • 499
0 votes
1 answer
166 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
920 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
161 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
567 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
583 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 ...
0 votes
2 answers
346 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 ...
10 votes
3 answers
4k 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", &...
1 vote
0 answers
32 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
205 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
310 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 ...
2 votes
2 answers
944 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
57 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
70 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 ...
0 votes
1 answer
232 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
26 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 ...
  • 442
1 vote
1 answer
119 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 ...
0 votes
1 answer
75 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
109 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
436 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 | ...
1 vote
1 answer
1k 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 ...
-1 votes
3 answers
601 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 ...
1 vote
1 answer
109 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
2k 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
55 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
200 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
142 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
175 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 ...
1 vote
1 answer
55 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 ...
2 votes
2 answers
145 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
52 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(...
  • 527
1 vote
2 answers
565 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 ...

1
2 3 4 5 6