Skip to main content

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
7 votes
1 answer
12k views

When to use unique composite keys?

When creating a database structure, I tend to create unique composite keys for every group of data that needs to be unique. Beside them I usually use a primary key (usually INT AI id), unless a ...
0 votes
1 answer
285 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 '...
0 votes
1 answer
24 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 ...
1 vote
1 answer
41 views

Unique constraint for single structure that simulates relations between tables

Imagine I have several tables in SQL Server 2016: Cat Cat carrier Cat transit box Cat car Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can ...
0 votes
1 answer
409 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
2 answers
95 views

Performance issue - using try catch to return data to service & how to use partition table with unique index

I'm working on enviorment SQL azure. I've got a stored procedure that runs from several AKS simultaneously and insert a message. So it's a race for who writes the message first and the rest fails. The ...
1 vote
1 answer
1k 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 ...
2 votes
1 answer
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 ...
0 votes
1 answer
370 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 ...
0 votes
1 answer
52 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 ...
1 vote
1 answer
694 views

Does innodb guarantee unique data in unique indexes even with UNIQUE_CHECKS=0?

We have a suspicion that sometimes there could be non-unique data even with a UNIQUE index. Question is, can we rely on InnoDB guaranteeing that there won't ever be non-unique data, even in the ...
4 votes
1 answer
217 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 ...
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 ...
11 votes
3 answers
10k views

Does making a field unique make it indexed?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly ...
0 votes
1 answer
112 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 [...
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....
0 votes
2 answers
24 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 ...
3 votes
1 answer
83 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 ...
1 vote
1 answer
344 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 ...
0 votes
2 answers
79 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 ...
9 votes
0 answers
712 views

Inconsistent equality checking of Unicode text in Postgres 9.5?

Using PostgreSQL 9.5. It seems like Postgres is being inconsistent with how it compares strings similar to لى. A unique constraint is considering some strings equal that GROUP BY is considering ...
0 votes
1 answer
849 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, ...
0 votes
1 answer
166 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 ...
2 votes
2 answers
6k views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no ...
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-...
4 votes
3 answers
2k 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 | ...
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 ...
1 vote
2 answers
1k 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 ...
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 ...
0 votes
1 answer
57 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 ...
31 votes
2 answers
31k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
1 vote
1 answer
203 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 ...
14 votes
4 answers
11k 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", &...
3 votes
1 answer
213 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 ...
1 vote
1 answer
48 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) ...
0 votes
2 answers
1k 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 ( ...
1 vote
3 answers
1k 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 ...
26 votes
3 answers
122k views

What is the difference between a primary key and a superkey in a DBMS

What is the difference between a primary key and a superkey in a DBMS? Can a primary key and a superkey both have multiple columns? Is a primary key a subset of a superkey or vice versa?
0 votes
2 answers
417 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 ...
10 votes
1 answer
1k 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 ...
7 votes
3 answers
4k views

How can I enforce ensure uniqueness of an association with a time dimension?

I have a database table AB which tracks the association of A and B over time: create table AB columns ( ARef ..., -- FK of A BRef ..., -- FK of B Since as Timestamp, Until as Timestamp, ...
42 votes
5 answers
62k views

Why does a UNIQUE constraint allow only one NULL in SQL Server?

In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should ...
0 votes
1 answer
368 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 ...
4 votes
1 answer
677 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 ...
-1 votes
1 answer
2k 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 ...
33 votes
2 answers
17k views

Custom unique constraint, only enforced if one column has a specific value

Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is 'true', then I want the ...
0 votes
0 answers
105 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
123 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, ...
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 ...
0 votes
1 answer
68 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 ...

1
2 3 4 5
7