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
2answers
35 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&...
0
votes
1answer
22 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
1answer
16 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 ...
0
votes
1answer
21 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 ...
1
vote
1answer
34 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
1answer
17 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 ...
1
vote
3answers
61 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].[...
1
vote
2answers
24 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
1answer
64 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
3answers
93 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
1answer
59 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(...
0
votes
1answer
69 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 ...
0
votes
0answers
39 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 ...
1
vote
1answer
46 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
1answer
38 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
0answers
46 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
1answer
36 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
2answers
81 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
0answers
26 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(...
1
vote
2answers
73 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 ...
0
votes
0answers
59 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 ...
0
votes
0answers
22 views

Primary Key and Unique Constraints in Partitioned Table

I am currently creating a project that applies to a table that contains about 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second will be inserted into the ...
1
vote
2answers
192 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 + ...
0
votes
1answer
39 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 ...
1
vote
1answer
896 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 ...
1
vote
2answers
47 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, ...
0
votes
0answers
11 views

Use generated IDs or meaningful fields in mapping table? (user, permission, user permission)

Given the below tables, what is best practice and why for defining mapping table? Should mapping table consist of user.id, permission.id, or say user.email + permission.permission? Are there any ...
1
vote
2answers
45 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 ...
9
votes
1answer
311 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 ...
1
vote
0answers
58 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 ...
0
votes
0answers
1k 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
1answer
157 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 ...
0
votes
2answers
25 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 ...
1
vote
2answers
36 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....
1
vote
3answers
71 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?
0
votes
1answer
801 views

Trigger to prevent insertion of duplicate data

I have a table named dbo.Inventory in SQL Server 2016 like this: ProdCode UPCCode 1 11111 2 11112 3 11113 When adding a new row, I need to write a ...
2
votes
1answer
788 views

Compound unique constraint with NULL values

With the following table structure CREATE TABLE favorite_comments( id SERIAL PRIMARY KEY NOT NULL, opinion_id BIGINT REFERENCES opinions(id), review_id BIGINT REFERENCES reviews(id), ...
5
votes
1answer
118 views

Does it make sense to put a unique constraint on a nullable soft-delete flag?

So we currently have the following: MYTABLE COLUMN: ID (INTEGER Primary key, auto-incrementer) COLUMN: START (DATE) COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY) COLUMN: DELETED (INTEGER) CHECK:...
0
votes
0answers
57 views

Complex key implementation on nullable fields using unique indexes

Currently I have a table as follows: ID Name Category Group 1 Abc D1111 55501 2 Abd D1234 55502 3 Abc D1112 55545 4 Abc D1113 55545 ...
0
votes
2answers
63 views

Why is Primary Key not present at the end of Unique Key in MySQL?

I have a table with following definition. CREATE TABLE `test` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, `e` int(11) DEFAULT NULL, ...
5
votes
2answers
1k views

When should the IGNORE_DUP_KEY option be used on an index?

Someone said it's preferable to craft your queries to avoid duplicate key exceptions, but I'm not convinced that's more performant that just setting IGNORE_DUP_KEY = ON for the index. My goal is to ...
1
vote
2answers
468 views

Preventing duplication on a Postgres table with 2 columns [duplicate]

I am currently implementing a simple database schema (in PostgreSQL 11.1 (Debian)), with two int columns (say col1, col2). My goal is to prevent duplicates appearing in any of the two columns. If (1,...
0
votes
1answer
219 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 ...
1
vote
1answer
2k views

SQL Server - MERGE duplicate SOURCE rows validation is only evaluated on UPDATE and DELETE clauses

I want SQL Server to throw an error at me if my SOURCE table contains duplicates as per the ON clause in every case, including INSERT, but it only does for the UPDATE and DELETE cases. Why doesn't it? ...
1
vote
3answers
84 views

Pairing Row Values in Any Order

I'm trying to find % of unique pairs that exist in two columns. For example Where 1,6 and 6,1 / 2,3 and 3,2 are unique pairs. So the % of matched pairs is 33%
-4
votes
1answer
22 views

How to list not nullable unique column list in postgres DB? [closed]

I want the list of unique columns with not null constraint
2
votes
2answers
65 views

How to have a “kinda” unique column

How can I have a MySQL database number column that only allows one 1, but infinite 0's? Some type of constraint or something. Elaboration To clarify what I mean and why I want this: Imagine you ...
14
votes
4answers
1k views

Index Uniqueness Overhead

I've been having an ongoing debate with various developers in my office on the cost of an index, and whether or not uniqueness is beneficial or costly (probably both). The crux of the issue is our ...
1
vote
2answers
1k views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
0
votes
1answer
3k views

“Cannot drop index 'unique_token_data': needed in a foreign key constraint” in MySQL without a foreign key

When I try to remove a unique index, MySQL Ver 5.7.26-0ubuntu0.19.04.1 says that it is needed in a foreign key constraint: mysql> ALTER TABLE project_erc20token DROP INDEX unique_token_data; ERROR ...

1
2 3 4 5 6