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
6
votes
3answers
80 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
33 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
44 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
49 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
52 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
43 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
48 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
371 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
35 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
71 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
45 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
127 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
77 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
32 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
19 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
118 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
151 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
31 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
40 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
177 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
26 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
97 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
1k 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
149 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
314 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
206 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
242 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
191 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
523 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
268 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
660 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
718 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
488 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
144 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
621 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
101 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
207 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
634 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
38 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
104 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
27 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
751 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) )...
5
votes
3answers
2k views

Lookup performance of Numeric vs String

I'm working on a project that is using the instagram key format. TL;DR 64-bit integer ids. They'll be used for lookups, and we also like them for sorting and batching since they will naturally sort ...
1
vote
1answer
75 views

How to prove “A->B,C then A->B and A->C” from Armstrong Axioms

I know that this is true but I don't understand how to prove it. The Problem: Prove or disprove that if A->B,C, then A->B and A->C This answer is similar but it does not prove the relation using ...
0
votes
1answer
766 views

Duplicated id error, key is actually NOT duplicated

I have the following query: WITH data AS ( SELECT profileid as id_user, cast(terms_accepted as boolean) as terms_accepted_passport, lastname as last_name_user, ...
0
votes
1answer
56 views

Complex `UNIQUE INDEX` scenario - Games, teams, players, positions!

I have a table CREATE TABLE (game, team, player, position); If I create a unique index on (game, player, position) I get a table where in the same game, the same player can't play the same position ...