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.
235
questions
0
votes
2answers
19 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
25 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
69 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
58 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 ...
0
votes
1answer
30 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
78 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
16 views
Postgresql - should character varying be limited if its going to be unique indexed?
For a column storing email, the length intended to be stored is never going to be over 254 characters. According to \d I currently have character varying (withOUT parentheses denoting a limit). Psql ...
0
votes
0answers
54 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
55 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
275 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
38 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
40 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 ...
0
votes
1answer
485 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
42 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
55 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
146 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
978 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
53 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
231 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
53 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
161 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
448 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
40 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
3k 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
115 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
48 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
198 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
392 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
35 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
379 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
259 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
34 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
44 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
224 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
28 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
161 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
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
305 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
424 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
225 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
312 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
215 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
814 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
310 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 ...
4
votes
1answer
711 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 ...
