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
1
vote
1answer
3k 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
142 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
69 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
2k 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
4k 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
61 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 ...
0
votes
1answer
1k 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
70 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
622 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
982 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
45 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....
6
votes
3answers
6k 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
241 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
96 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 ...
6
votes
2answers
418 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
861 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
39 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
37 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
602 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
464 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
54 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
51 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
51 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
428 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
30 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
382 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 ...
4
votes
2answers
4k 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
1k 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,...
2
votes
1answer
868 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 "...
3
votes
1answer
568 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
558 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
299 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
2k 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
33 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
356 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 ...
5
votes
1answer
1k 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 ...
6
votes
1answer
1k 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=...
4
votes
1answer
2k 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
3k 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(...
2
votes
2answers
703 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
1k 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
401 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
251 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 ...
4
votes
1answer
982 views

Unique combination of values

I want to create a database for my program. Most likely I will use SQLite, although others are an option. I will have columns a, b, c, d. a is unique and will be my primary key. b, c, d are allowed ...
0
votes
0answers
40 views

How to add unique key dependant on another composite unique key

I have products related to a custom pages, I want to display these products on the page in a specific order for this I have created a new table; CREATE TABLE `am_landing_page_products` ( `rel_id` ...
-4
votes
1answer
138 views

How do I ensure a relationship is commutative and unique? [closed]

In PostgreSQL, we can ensure the relations stored inside a table a commutative-ly (or bidirectional etc) unique. For instance, CREATE TABLE foo ( a int, b int, UNIQUE ( greatest(a,b), least(a,b)...
1
vote
1answer
31 views

Unique ID for Row of a Set in One Table

I know the title is a little weird but what I actually want to do is simple. In my Postgres db a user creates a profile and uploads an image. user_id: 1, photo_id: 1 Now a second user makes a ...
0
votes
0answers
2k views

Postgresql Restore - duplicate key value violates unique constraint

I have a PostgreSQL 9.2 server and want to restore this to PostgreSQL 9.6. Here is the table. create table mytable ( ID int, EID int, Name varchar(100), Address varchar(300), primary key (ID, EID) )...