The unique-key tag has no wiki summary.
16
votes
3answers
4k views
When should I use a unique constraint instead of a unique index?
When I want a column to have distinct values, I can either use a constraint
create table t1(
id int primary key,
code varchar(10) unique NULL
);
go
or I can use a unique index
create table t2(
id ...
9
votes
3answers
4k views
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
constraint ...
7
votes
4answers
245 views
What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?
I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind:
Simplicity; there are no ...
6
votes
1answer
154 views
UNIQUE index key violation
I have a table with a PK and a unique non-clustered index, as follows:
CREATE TABLE Table1
(
Id INT IDENTITY(1,1) NOT NULL,
Field1 VARCHAR(25) NOT NULL,
Field2 VARCHAR(25) NULL,
...
5
votes
3answers
694 views
Differences between “Unique Key” and “Primary Key”
What are the major differences between Unique Key and Primary Key in MySQL?
5
votes
2answers
104 views
Resolving DUPLICATE for a column with UNIQUE
I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test ...
5
votes
3answers
2k views
Does making a field unique make it indexed?
If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly ...
4
votes
2answers
109 views
Custom unique column constraint, only enforced if one column has a specific value
Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is "true", then ...
4
votes
1answer
2k views
Optimal way to ignore duplicate inserts?
Background
This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code:
-- Ignores duplicates.
INSERT INTO
db_table ...
4
votes
1answer
79 views
Unexpected Unique Constraint Violation
Given the following CREATE TABLE statement:
CREATE TABLE [dbo].[Unit]
(
[id] SMALLINT NOT NULL IDENTITY(1,1),
[name] VARCHAR(100) NOT NULL,
CONSTRAINT ...
4
votes
2answers
129 views
What happens to the index of a primary key after a DROP CONSTRAINT?
I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the ...
3
votes
2answers
3k views
How to use Unique key via combinations of table fields?
Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1
CREATE TABLE contacts
(
id int auto_increment primary key,
name varchar(20),
network_id int,
...
3
votes
1answer
131 views
Is UNIQUE(), INDEX() different from UNIQUE INDEX()?
Is there any different how mysql treats
UNIQUE (column1, column2),
INDEX (column1)
and
UNIQUE INDEX (column1, column2)
and
UNIQUE (column1, column2)
In the latter, I think mysql will ...
3
votes
1answer
88 views
Should I always make an attribute id that's an primary key?
I was thinking, sometimes several columns in a table uniquely specify the whole row.
In that case, is there a need to create a special column for a primary key?
Is that good for something?
3
votes
2answers
725 views
MySQL: Storing unique URLs
I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
3
votes
1answer
580 views
Duplicate UNIQUE KEY error after ROLLBACK in MySQL
I have a table like this
mysql> describe seudonimos;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra ...
2
votes
4answers
560 views
Two-sided UNIQUE INDEX for two columns
Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
2
votes
1answer
65 views
SQL Uniqueness Across Multiple Tables
I have two SQL tables: Users and clients.
Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field.
The users ...
2
votes
3answers
571 views
Unique combination key MySQL
Lets say I have a table with 3 fields:
id1
id2
mapTypeId
What I want is a primary/unique key that is any combination of id1 and id2. For example if I have this record already in the database:
...
2
votes
1answer
154 views
MySQL Unique Index Keylength
I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key.
The engine of choice here is InnoDB.
There are ...
1
vote
2answers
254 views
Best type to use in a composite key when one of the values varies in type
I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table
BlogId Url ...
1
vote
2answers
99 views
Unique Constraint with multiple null columns
I have two tables
PC(Id,EmpName Not NULL, PCName NULL, HostName NULL,.... PhysicalLocation NULL, PCType Not NULL)
PCNetwork(Id, EmpName Not NULL, PCName NULL, HostName ...
1
vote
1answer
3k views
How can I optimize my mysql setup to create my index faster?
I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored.
I have an MyISAM like ...
1
vote
1answer
28 views
checksum(newid()) as primary key - what happens if a collision occurs?
Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). ...
1
vote
1answer
25 views
Is it better to add an index and search via the index or add a unique constraint and let an insert fail?
I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job:
mysql> DESCRIBE job_views;
...
1
vote
1answer
41 views
Correct data model for users with clients
I have an application where users are granted access. Part of the system allows users to have their own 'clients'.
I have a users table
********************************
users
...
1
vote
1answer
494 views
PostgreSQL duplicate key violates unique constraint error
CentOS 6.2 64bit Server
PostgreSQL 8.4.9 installed via yum
I am seeing errors from time to time in PostgreSQL's logs about a table called events. The events table is used to stores information ...
1
vote
1answer
968 views
How to use ON DUPLICATE KEY for UPDATE
Consider a table of
CREATE TABLE test
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
external_id int(11),
number smallint(5),
value varchar(255),
UNIQUE INDEX (external_id, number),
PRIMARY KEY(id)
...
1
vote
1answer
68 views
Database design problem: where to put UNIQUE constraint
I have the following three tables:
Form
-----------
formId INTEGER PK
name NVARCHAR(50) NOT NULL
Block
-----------
blockId INTEGER PK
name NVARCHAR(30) NOT NULL
...
1
vote
0answers
36 views
mysql update unique values from 2 different tables based on 2 index fields
i have 2 tables with the same unique id. i want to change table1 unique_id sequence from (1-100) to random 8 digit ids (43342359 - 83083224) and cascade that to table2.
what i have:
- table1
id1 ...
1
vote
0answers
70 views
How can I force myisamchk to modify the original data file in case of duplicate keys?
In the documentation for myisamchk, it says:
--quick, -q
Achieve a faster repair by modifying only the index file, not the data file. You can specify this option twice to force myisamchk to ...
1
vote
2answers
532 views
MySql - partition by range and unique key
i'm trying to create the following table:
CREATE TABLE `s_relations_with_partition` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`source_persona_id` int(11) NOT NULL,
`relation_type` int(11) NOT ...
0
votes
2answers
67 views
Unique indexed column in where clause and mysql execution after finding first row
Suppose in a table there are 2 columns: login and password
login is indexed as unique index, password not indexed.
query: SELECT * FROM mytable WHERE login = 'Jhon'
query: SELECT * FROM mytable ...
0
votes
0answers
146 views
Can't UPDATE record with unique key [closed]
Im trying to do an UPDATE statement.
UPDATE `users` SET
`Email` = 'email@domain.com',
`Mobile` = '123456789',
`City` = 'Chicago',
`Firstname` = 'John',
`Lastname` = 'Smith',
`DOB` = '1980-01-01',
...
