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.
222
questions
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 ...
