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
0answers
52 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
49 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, ...
3
votes
2answers
189 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
26 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
24 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
93 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
39 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
19 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
50 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
votes
2answers
58 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
265 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
vote
0answers
50 views

UNIQUE constraint encompassing all the columns of a table - is it bad practice?

Is it considered bad practice to have a UNIQUE constraint applying to all columns on a table, essentially enforcing unique rows? My use case is to store tokens from textual message from various ...
-1
votes
1answer
125 views

Alternatives to MySQL Partitioning, so we can have a Unique Index

I have a DBA vs Developer Issue here, and am wondering if there are any alternatives to partitioning that I can employ here. I have a table as follows: CREATE TABLE `tracking` ( `Id` int(10) ...
2
votes
1answer
46 views

Unique sets of IDs from table

I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified): CREATE ...
2
votes
1answer
75 views

What is the strict difference between key and constraint in SQL Server?

In SQL Server, the 'keys' folder includes primary, foreign and unique keys, while the 'constraints' folder, for example, check and default. But unique can also be considered a constraint, so why it is ...
0
votes
1answer
398 views

What is a good unique identifier for customer and payment details for this tourism company?

Given a tourism company, Air T&T, has customers. When a customer checks in to an AirTnT facility (i.e. house-share), AirTnT records the customer's first and last names, address, date of birth, ...
0
votes
1answer
38 views

Making a field greater than the limit unique

Firstly, just a quick note. I have searched for an answer but I can only people asking why rather than how to overcome. I am currently designing a set of database tables for a project I am working on....
5
votes
3answers
2k views

How to generate globally unique ids for different tables of the same database?

In a production system on SQL Server all ids (mostly PKs) in all tables are generated automatically and I am informed that they are unique globally. I mean no 2 ids are the same in the database, even ...
0
votes
1answer
94 views

Indexing VARCHAR column?

Should I add INDEX to my VARCHAR column? I need to select rows with a string, not with ID, will it speed my queries? Example: SELECT * FROM `cities` WHERE `slug` = 'new-york';
2
votes
1answer
47 views

Make a DBMS-enforced multi-column “UNIQUE” constraint, with a “match all” wildcard

Well I wonder if I can do the following on database level (instead of server level). We are using PostgreSQL as the SQL flavour. I have multiple columns. Now I wish to make sure each entry into the ...
5
votes
2answers
173 views

Does the concept of candidate key exist only in theory?

I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate ...
0
votes
1answer
220 views

How to update a sequence column using single update statement

I have a table, lets call it foo which consist a column priority and has a unique constraint on it. lets assume below is the current state of foo table :- mysql> select * from foo; +-----+--------...
0
votes
1answer
34 views

How to insert data in table with constraint on column? - in parallel queries

I have an Oracle database with a table with a unique field in it: create table t(n number, CONSTRAINT n_unq UNIQUE (n)); Then from a session-1 someone is trying to insert some data: insert into t(n)...
0
votes
2answers
21 views

Postgres - Only allow insert if TWO columns match with a separate table

I have three tables: a users table, a books table, and a chapters table. Each book has an id called identifier_id (for good business reasons that I cannot work around). This id is guaranteed to be ...
0
votes
1answer
262 views

Get result from select exists, execute insert query based on the response of it

I have to insert unique value for a row but has to put check on 3 columns collectively ((unique value(col1 && col2 && col3) == true) I found a solution here it is query = select ...
1
vote
1answer
198 views

SSIS Data Flow Task Violates Unique Constraint But Linked Server Insert Succeeds

Ultimately, what I am trying to do is create an SSIS package in the QA environment for another team which truncates the destination tables and inserts from the source tables. However, on one table, ...
0
votes
1answer
33 views

efficient primary key that does not expose business internals

I am using auto_increment primary key to store each task user performs. Users will be given the key to perform queries about their tasks. Keys are short, efficient, but are auto incremented in a ...
-1
votes
1answer
42 views

Why would someone choose to increment a unique key with a value higher than 1? [duplicate]

Why would someone choose to increment a unique key with a value higher than 1? For example instead of unique index going from (1, 2, 3, 4, 5) It goes(8, 15, 22, 29, 36), starting at 8 and then each ...
3
votes
1answer
49 views

Ensuring Uniqueness Across Columns

I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution! Consider the following table: ID | TypeA | RefA | TypeB | RefB -- + ----- + ---- + ----- + ---- ...
5
votes
1answer
200 views

Minimizing/avoiding data repetition and choosing between compound keys and unique indexes

I am absolutely new to database development and i'm trying to build a question bank basically. The questions have to be categorized based on Class, Subject and Topic. Since each Class can have many ...
2
votes
1answer
27 views

Database Entity Design for Enumeration Table

I have an enumeration of ~10 values - they are Char such as A through K. These are all unique. They are used for two purposes - an application is forced to select acceptable values and also there is a ...
1
vote
0answers
130 views

Mysql Deadlock in Stored Procedure,composite unique key

I have a table with composite unique key(uniqueness on combination of 3 columns.) Table structure: CREATE TABLE `userreview` ( `cid` bigint(12) unsigned NOT NULL, `conid` bigint(12) unsigned NOT ...
3
votes
2answers
2k views

“Create Unique Index” on big table taking too long time [closed]

I am trying to restore GHTorrent's database dumps (CSV files containing GitHub metadata). The commits table has more than 891 Million rows and the project_commits has more than 5.4 Billion rows. Since ...
0
votes
1answer
227 views

Create a unique constraint on a column based on distinct value from another column in the same table

I want to make a column unique, but it has to be unique based off a distinct value from another column in the table. Consider the following table: CREATE TABLE locations( locationId INT IDENTITY(1,...
1
vote
1answer
358 views

Foreign key subsumed by unique constraint in MariaDB/MySQL

I'm using a "migrations" approach to upgrading/downgrading table structure (Laravel). Essentially tables are added and altered as the application grows, but each "upgrade" script has an appropriate "...
2
votes
1answer
218 views

Remove extra email characters to enforce uniqueness

Gmail ignores . inside an email address e.g abc@gmail.com and a.b.c@gmail.com are same Gmail also considers same account if account has any number after + symbol. e.g xyz@gmail and xyz+1@gmail.com are ...
3
votes
1answer
286 views

Increment value for unique key column

I have a table: CREATE TABLE tbl ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, rank BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (id), UNIQUE KEY rank (rank) ) With some values: (2,...
11
votes
1answer
205 views

N'Șc' considered duplicate key of N'C' using Latin1_General_CI_AS collation

I have a table with a unique key that includes an NVARCHAR(50) column (correct or not, but is there). So, when trying to insert Șc or C (doesn't matter the order of the insert) it breaks on the 2nd ...
1
vote
1answer
659 views

Creating the same unique ID in multiple tables

I have two tables of data; one table is the Employee Master table containing a list of unique employee names, and the other table contains transactional data with multiple records for each employee. ...
0
votes
1answer
32 views

When all you indexes have included all the other columns, do you still have the penalty for a wide clustered index?

So I hear sometimes that I should not use a clustered index on a wide column, mainly because it is being referenced in all other indexes. When all my other indexes on this table include all other ...
3
votes
3answers
296 views

create a visible uniquifier column for a nonunique clustered index

I have a table of events, clustered index is the creation date/time. Normally there should never be two events happening in the same millisecond, but freak occurences happen, so the column is not ...
3
votes
1answer
686 views

Is there any way to have Unique Constraint and NOT have Unique Index?

Looking into the Microsoft documentation, it says when a unique constraint is created, a unique index is created automatically. Is there a trick/workaround to have a unique constraint and NOT have a ...
5
votes
1answer
823 views

Allow only unique combination of values for a composite primary key

I created the following table called books: CREATE TABLE IF NOT EXISTS `books` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` TEXT NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=...
3
votes
1answer
627 views

SELECT COUNT(*) returning incorrect results

I am seeing some very strange behavior on PostgreSQL 9.6.3 on RDS Aurora. I am getting duplicate results from certain queries: => select count(id) from foos where id = 'deadbeef'; count ------- ...
4
votes
2answers
1k views

SQL Server: specifying a unique optional column

SQL Server has a quirk with optional unique columns: you cannot have more than one null in a table. For example: create table employees ( id int identity(1,1) primary key, givenname nvarchar(...
1
vote
2answers
205 views

Is it benefit to create a index key if unique key already exists on the same columns?

The table schema for example: CREATE TABLE `foos` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `bar1` bigint(20) unsigned NOT NULL, `bar2` bigint(20) unsigned NOT NULL, PRIMARY KEY (`...
1
vote
2answers
753 views

Unique constraint insert prevention

I've created a unique constraint to my table containing employee account details in an attempt to stop duplicated data inserts. ALTER TABLE emp ADD CONSTRAINT uq_ UNIQUE (USERNAME, EMP_NAME); ...
2
votes
0answers
122 views

Inserting two NULLs values into an UNIQUE column in SQLite

I use: sqlite-jdbc-3.21.0.jar Error received: SQLite_CONSTRAINT_UNIQUE] A UNIQUE constraint failed: companies.ncf I ask a few things to the user, there is two fields that are optional to complete ...
7
votes
3answers
211 views

Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

Scientific names are well accepted unique identifiers of species. However, scientific names have special characters such as spaces and dots. Can it be a good unique identifier for databasing or should ...