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
0 votes
1 answer
41 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 ...
Dirk Boer's user avatar
  • 365
4 votes
1 answer
209 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 ...
Andriy M's user avatar
  • 23k
0 votes
1 answer
11 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 ...
Ron Piggott's user avatar
0 votes
1 answer
89 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 [...
Jeff.Clark's user avatar
0 votes
1 answer
27 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....
Matthew Lenz's user avatar
0 votes
2 answers
17 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 ...
GreenhornCJ's user avatar
3 votes
1 answer
81 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 ...
riwu's user avatar
  • 133
1 vote
1 answer
251 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 ...
Brendan Maguire's user avatar
0 votes
2 answers
70 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 ...
Twisted Tea's user avatar
0 votes
1 answer
700 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, ...
U. Windl's user avatar
  • 125
0 votes
1 answer
122 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 ...
Camila326's user avatar
1 vote
1 answer
980 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 ...
Eventeos's user avatar
0 votes
0 answers
98 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 ...
gabtzi's user avatar
  • 149
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 ...
Kevin Huntly's user avatar
1 vote
2 answers
999 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 ...
Curtis Fenner's user avatar
0 votes
1 answer
56 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 ...
Ken Lyle's user avatar
0 votes
1 answer
245 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 '...
Neil Widdowson's user avatar
1 vote
1 answer
187 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 ...
Anvil's user avatar
  • 11
1 vote
1 answer
47 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) ...
Saeed Neamati's user avatar
0 votes
2 answers
859 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 ( ...
majid_shoorabi's user avatar
3 votes
1 answer
204 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 ...
Biller Builder's user avatar
0 votes
2 answers
357 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 ...
Muzib's user avatar
  • 103
0 votes
1 answer
337 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 ...
Alex's user avatar
  • 3
-1 votes
1 answer
1k 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 ...
Yannick Mussche's user avatar
4 votes
1 answer
618 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 ...
Don Draper's user avatar
0 votes
0 answers
102 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 ...
Tom Shelley's user avatar
0 votes
1 answer
112 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, ...
x1a4's user avatar
  • 111
9 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 ...
bigchief's user avatar
  • 225
0 votes
1 answer
63 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 ...
an_interesting_name's user avatar
1 vote
1 answer
3k 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 ...
Gunther Schadow's user avatar
1 vote
1 answer
130 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 ...
Jeroen Vermunt's user avatar
2 votes
2 answers
571 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 ...
ivan's user avatar
  • 552
2 votes
0 answers
606 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 ...
xoudini's user avatar
  • 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 ...
Lance's user avatar
  • 199
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-...
Vishal_Kotecha's user avatar
4 votes
1 answer
550 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 ...
jcalbert's user avatar
1 vote
0 answers
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 ...
Andrew Polukhin's user avatar
0 votes
1 answer
47 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 ...
Gili's user avatar
  • 1,019
1 vote
3 answers
756 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 [...
Catachan's user avatar
0 votes
0 answers
840 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 ...
Mark Lodato's user avatar
0 votes
2 answers
2k 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 ...
Adr4m's user avatar
  • 3
0 votes
1 answer
392 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 ...
Denis Borzenko's user avatar
0 votes
1 answer
797 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 ...
Archmede's user avatar
  • 165
2 votes
1 answer
153 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 ...
Roman Reimche's user avatar
2 votes
5 answers
1k 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 ...
O. Jones's user avatar
  • 563
0 votes
1 answer
612 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&...
Robert W's user avatar
  • 113
1 vote
2 answers
4k 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 ...
albttx's user avatar
  • 123
5 votes
2 answers
378 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? ...
SQL_Guy's user avatar
  • 665
2 votes
1 answer
8k views

postgresql Modifying existing unique index

I have this table on PostgreSQL: example_dev=# \d products Table "public.products" Column | Type | Collation | Nullable | ...
manuel.menendez's user avatar
2 votes
1 answer
2k 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 ...
Olleggerr's user avatar

1
2 3 4 5
7