Hot answers tagged unique-key
27
Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.
So for a database ...
14
You can do that in pure SQL. Create a partial unique index in addition to the one you have:
CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;
This way you can have (1, 2, 1) and (1, 2, 2) and (1, 2, NULL) for (a, b, c) in your table, but none of these a second time.
Additional notes
No use for mixed case identifiers ...
10
In other words, you want subset to be unique if type = 'true'.
A partial unique index will do that:
CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true';
This way you can even make combinations with NULL unique, which is not possible otherwise - as detailed in this related answer:
PostgreSQL multi-column unique constraint and NULL ...
8
A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints.
Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys.
Another ...
7
UNIQUE (column1, column2) implies UNIQUE INDEX (column1, column2) because the INDEX keyword is optional. So an index is created. However, the MySQL 5.5 docs show that the INDEX (or KEY) keyword is mandatory so UNIQUE (column1, column2) should give an error
INDEX (column1, column2) does not mean UNIQUE INDEX (column1, column2): it means an index that does ...
7
What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?
There is no significant disadvantage using the natural key as the clustered index
there are no non-clustered indexes
no foreign keys referencing this table (it is a parent row)
The downside would be increased page splits as data inserts would be distributed throughout the data, instead of at the end.
Where you do have FKs or NC indexes, the using a ...
7
I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead).
I tried to make different results comparable by using more than one ...
6
You could have a third table which stores both. Something like this:
unique_ids
----------
identifier (UNIQUE)
used_where (name/id of table that the identifier is used in)
users
-----
username (FK to unique_ids.identifier)
clients
-------
clientname (FK to unique_ids.identifier)
This way, both tables share one common table that contains the ...
5
I wouldn't even consider forcing data of different types into one field.
Another option:
synthetic post_id and
child tables for each 'type' of 'native_post_id'
There are various ways to go about enforcing the subset relationship between these child tables and the parent if necessary
5
Why not just collate that column as case-sensitive? I don't think you need to resort to binary. This seems to work fine, just note that it may affect sorting, comparisons, unions etc:
CREATE TABLE [dbo].[Unit]
(
[id] SMALLINT NOT NULL IDENTITY(1,1),
[name] VARCHAR(100) COLLATE Latin1_General_CS_AS NOT NULL ,
...
4
In a standard SQL dbms, you'd enforce that kind of requirement by ordering the id numbers, and using a CHECK constraint. Application code, a stored procedure, or a user-defined function is responsible for putting the id numbers in the right order.
create table friends (
user_a integer not null, -- references users, not shown
user_b integer not null, ...
4
I would definitely go with a hash of the url and make the hash a unique index. A hash has a fixed length, so you can use CHAR to specify the length of the column, which grants a slight performance boost over VARCHAR or TEXT.
But might I suggest using INSERT IGNORE instead of making two calls to the database? Something like:
INSERT IGNORE INTO urlTable ...
4
Change the table's definition by adding a UNIQUE KEY constraint on the combination of the two columns:
CREATE TABLE contacts
(
id int auto_increment primary key,
name varchar(20),
network_id int,
network_contact_id int,
CONSTRAINT network_id_contact_id_UNIQUE
UNIQUE KEY (network_id, network_contact_id)
);
You ...
4
There are two schools of thought about this and some of it is database dependent.
In general, on PostgreSQL, I define natural primary keys wherever I can (and make these composite keys where appropriate) and I (usually) add a secondary, surrogate id key which is an integer and is used as a surrogate for the primary key in joins. I see this as a good way to ...
4
This is supplemental to Erwin's answer above, but PostgreSQL supports a bunch of types of indexes. These are not generally mutually exclusive. You can think of these as being:
Index method (btree, GiST, GIN, etc). Choose one, if necessary (btree being the default)
Partial or full. If partial use a where clause
Direct or functional. You can index the ...
3
I would look at your model first. Placing a unique key constraint on a non-unique column gets you into this kind of problem. What happens when you get a legitimate value like test title-2 but you've already used that value to resolve a collision on test title.
If I had to resolve your problem, I would build a query for each unique key of the form:
SELECT ...
3
For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big.
Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this:
Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab
none ...
3
A Null can mean that value is not known for that row at the moment but will be added, when known, in the future (example FinishDate for a running Project) or that no value can be applied for that row (example EscapeVelocity for a black hole Star).
In my opinion, it's usually better to normalize the tables by eliminating all Nulls.
In your case, you want to ...
2
--EDIT--
My original answer (below) is probably not useful to you at all because it does not address the question of unique constraints. As others have said, these constraints are usually implemented with an implied unique index. In special cases this might not be true (eg disable novalidate for Oracle).
The question could be: Is it possible to enforce ...
2
In one of your comments to your own question, you're saying you're using MyISAM.
However, MyISAM does not support transactions (see ref. table). Therefore, it will always autocommit, whether you try to turn it off or not.
If you want to use transactions, you need to use an engine that supports it, such as InnoDB.
EDIT (following comments and additional ...
2
What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?
Although I risk stating the obvious, an index on a surrogate key (an id number) is useful if you need to locate things by their id number. Users are not going to deal with the id number; they're going to deal with human-readable text. So you have to pass around the text and its id number a lot, so the user interface can display the text and operate on the id ...
2
What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?
I have a whole oltp database designed using identity columns for clustering + pk.
It work pretty fast on insert/seeks but i've seen a few problems:
1. the index fill option is useless because the inserts happen only to the end of the index
2. more storage space. I have tables with tens of millions of records and 1 int takes up space by itself. Each table ...
2
You may want to try creating a trigger that will check for the presence of (col1,col2) existing as (col2,col1)
Here is an example:
use test
drop table if exists ali;
create table ali
(
col1 int not null,
col2 int not null,
primary key (col1,col2)
);
DELIMITER $$
CREATE TRIGGER ali_bi BEFORE INSERT ON ali FOR EACH ROW
BEGIN
DECLARE ...
2
Yes you can but not with an index. You have to remember that the values [2,1,3] are a unique value even if the value [1,2,3] already exists. However you have 2 options available to you:
You could enforce this logic using a trigger to check that the value of id1 does not exist in id2 and vice-versa.
You could perform the check inside the stored procedure ...
2
First a sample table
mysql> drop database if exists ali;
Query OK, 1 row affected (0.10 sec)
mysql> create database ali;
Query OK, 1 row affected (0.00 sec)
mysql> use ali;
Database changed
mysql> CREATE TABLE test
-> (
-> id int(11) unsigned NOT NULL AUTO_INCREMENT,
-> external_id int(11),
-> number smallint(5),
...
2
Some things others have not pointed out:
//Redacted
If you don't explicitly declare a PK in innodb tables it will create one under the covers for you. You cannot access, order, or filter by this implicit key. This has ramifications in terms of resources as each secondary index contains a copy pointer to the PK of the row.
2
(I try to compile an answer from the comments.)
It looks like there is a problem in the process with which your application generates the value for the primary key. Generally it is wiser to leave this to the DBMS: define a sequence and leave it fill the PK values. If you have a sequence, you have at least to options: either set the value like
$eventid = ...
2
Disclaimer
This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.
For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in ...
2
I would agree with @BillThor that what you want to do may create some problems but what you need is ON DUPLICATE KEY UPDATE statement so your query would be something like this:
INSERT INTO table1
(`title`, `abr`, `name`)
VALUES
('title', 'abr', 'name')
ON DUPLICATE KEY UPDATE `title` = CONCAT(`title`, '-2');
You can change CONCAT to any other function ...
2
You can add a unique index across the user_id and username in the Clients table this will ensure that the username can only occur once per user_id. Though you should be aware that if the user_id column is nullable it will also apply to clients that do not have a user_id assigned to them.
EDIT
Using a composite primary key that consists of the client_id, ...
Only top voted, non community-wiki answers of a minimum length are eligible
