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.
249
questions
0
votes
2answers
20 views
Why is there “no unique constraint” error for primary keys?
I have following table:
create table tbl (id_p int, id_c int, primary key (id_p, id_c));
I want to create a new table:
create table tbl_tbl (id_p int references tbl(id_p), id_c int references tbl(...
0
votes
0answers
14 views
Primary Key and Unique Constraints in Partitioned Table
I am currently creating a project that applies to a table that contains about 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second will be inserted into the ...
1
vote
2answers
44 views
Hierarchical data using closure table, how to ensure that a column is unique within the parent node?
I store hierarchical data in a Node-table, combined with a closure table (NodeClosure). Each node has a column "name" that should be unique among the sibling nodes (link to parent-Node + ...
0
votes
1answer
25 views
UNIQUE constraint on a specific pairing [duplicate]
I am trying to work out a special UNIQUE constraint: there are many columns in a Users table but only these two are relevant for the question:
id bigint,
active boolean,
...
I want to allow multiple ...
1
vote
1answer
643 views
Does a primary key contain exactly one Null value…?
My work
A primary key is unique and only one contains for a table.
There are many unique keys that can contain in a table.
In SQL Server, the primary key is not Nullable. But, a unique key may ...
1
vote
2answers
41 views
3 additional varchar(32) keys and also updating them — too bad for performance?
I have a table in mind which basicaly would look like this:
table1
--------
id bigserial autoincrement primary key,
token1 varchar(32) unique not null,
token2 varchar(32) unique not null,
...
0
votes
0answers
10 views
Use generated IDs or meaningful fields in mapping table? (user, permission, user permission)
Given the below tables, what is best practice and why for defining mapping table? Should mapping table consist of user.id, permission.id, or say user.email + permission.permission? Are there any ...
0
votes
0answers
19 views
Generate integer on SQL insert with unique constraint to value in another table
I have two tables:
Assuming a user already exists, when I insert a vehicle I want the unique_number field to auto-populate with a value between 00000-99999. It only has to be unique based on the ...
1
vote
2answers
40 views
Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries
I have a 'Users' table with columns user_email, user_company_id and user_status. The user_status column is an enum with values '1' or '0' which represents the users being either active or inactive. Is ...
0
votes
0answers
19 views
Depicting a Unique Constraint on a UML Diagram
I need to show on a diagram a unique constraint involving two attributes. For example, UNIQUE(COLA, COLB).
This is what I have done:
9
votes
1answer
200 views
Best way to model the relationship of unique pairs
I have two tables; one for storing thing and one for storing the relationship between two thing objects.
dbfiddle example
Assume:
AB == BA. Storing both would be redundant
A != B. The ...
1
vote
1answer
54 views
Best way to handle this type of constraint
Guidance please. We have a small configuration table where we need to ensure only 1 record has bit checked. In the past, we have done check constraint with function that checks count, which we are ...
0
votes
0answers
318 views
Error: duplicate key value violates unique constraint, but the column does not have a unique constraint? postgreSQL
By the way, I'm trying to automate the below using python, but I've isolated the SQL code and tried it by itself on pgAdmin III, and it still doesn't work.
So I have a table called tableName, and I ...
2
votes
1answer
52 views
MariaDB handling composite unique indexes that are foreign keys at the same time [closed]
I have a possible issue with how MariaDB (and probably MySQL) handles composite unique indexes that are also foreign keys. Not sure if that is by design and if it is, why does it work like that.
I ...
0
votes
2answers
22 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
29 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
71 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
284 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
166 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
85 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
18 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
56 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
57 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
494 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
165 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
95 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
1k 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
46 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
63 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
346 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
2k 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
56 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
429 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
59 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
269 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
556 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
41 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
4k 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
156 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
52 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
250 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
559 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
37 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
26 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
474 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
333 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
36 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
47 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 ...
