A set of zero, one or more attributes, the value(s) of which are guaranteed to be unique for each tuple (row) in a relation.

learn more… | top users | synonyms

3
votes
2answers
75 views

Enforce uniqueness across optional attributes for an entity

I have an entity that has multiple attributes, and these attributes are themselves entities. No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. ...
14
votes
4answers
2k views

Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value ...
0
votes
0answers
30 views

Create a not unique FK in MySQL Workbench

I'm trying to model a database using MySQL Workbench (version 6.3.6, 64 bits, using linux mint). After syncing my model with a MariaDB (running in localhost, version 10.0.25), I discovered that the ...
1
vote
2answers
43 views

Find duplicate rows violating unique constraint

Recently We meet a mysql error on slave, because a transaction can not be executed on slave. We found two records have the same unique in master, but inserted successfully. I have post two ...
1
vote
1answer
35 views

Constraint for a unique column if other column has same value

I'm wondering if it's possible to have a column be unique for each value of another column in MySQL. What I'm wanting to have is the same functionality as what a GROUP BY would do. Here's the table: ...
3
votes
2answers
67 views

3rd Party Platform Unique Identifier for Joins vs Internal Identifiers

What is the general practice when it comes to choosing a unique identifier? In my case I have the option to use the eBay unique listing ID (Item ID) or my own internal ID for use in table joins and ...
0
votes
0answers
10 views

HA_ERR_FOUND_DUPP_KEY error after pt-OST optimize

I run a simple alter to optimize a table that had a lot of stale data using the pt-online-schema tool (wrapped for readability): pt-online-schema-change --alter='ENGINE=InnoDB;' --no-drop-old-table ...
2
votes
2answers
733 views

DynamoDB - Multiple range keys

Is it possible to have multiple fields as range key? Say I have a table where each row is uniquely identified by <A,B,C> ------------------------------- A | B | C | D | E | ...
0
votes
0answers
27 views

unique BIGINT as foreign key instead of primary key in distributed system

I am creating user management database schema. I am using Postgresql as database. Following is my approach. Please suggest if there is any performance issue if I use this structure. Requirement: ...
1
vote
1answer
43 views

Constraining inserts/updates for summary tables with complex validation logic

I am designing some MySQL tables and am running into an issue that I believe can be solved by a uniqueness constraint, but not sure exactly how. My actual use case is complicated and confusing, so ...
0
votes
2answers
53 views

Ignore white spaces for comparison in unique constraints of Oracle

How can I tell to Oracle DBMS to ignore white spaces while comparing varchar values for Unique Constraints. It means that NAME 1 is exists in a table column with Unique Key, inserting NAME1, causes ...
3
votes
1answer
54 views

MySQL: Add column to unique index or add new index?

For a MySQL database, I have an InnoDB table name user_metrics with the following columns: id INT(11) NOT NULL AUTO_INCREMENT, userid INT(11) NOT NULL, name VARCHAR(32) NOT NULL, value ...
10
votes
5answers
679 views

Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem. Running MS SQL Server 2012. The problem is with this UPDATE statement: UPDATE dbo.tAccts SET Ticket ...
3
votes
3answers
354 views

Programmatically finding minimum set of fields necessary to create a unique composite key

I am importing flat files from different sources into tables in SQL Server. I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for ...
1
vote
1answer
73 views

How to reference a UNIQUE KEY CONSTRAINT in parent table as a foreign key in child table?

I have created two tables, the parent in this case being "department" and "employee" being child. CREATE TABLE department ( department_id NUMBER(4) CONSTRAINT department_id_not_null NOT NULL, ...
0
votes
1answer
51 views

MySQL Finding index size for compound unique indexes

I'm using following query to get index and data sizes for tables in database. But unfortunately it doesn't show index size for compound unique indexes. How can i get index sizes for compound unique ...
0
votes
0answers
13 views

Unique attributes as a group [duplicate]

create table test (a int, b int); I want to make a and b be unique, but as a whole. Example: a b - - 1 1 1 2 - this is ok, because even if the previous entry was 1, the b value is different 2 1 - ...
1
vote
0answers
19 views

Windows Azure Table Row Key strategy, what makes a useful RowKey?

I read somewhere that the row key should be useful - which is why auto numbers are not supported. However, for all 3 tables I've started saving I can't think of anything better to save in the row key ...
1
vote
1answer
178 views

Can one part of a multi-column primary key be used across tables?

I want to use SQLite as file format for my app, a reporting program. Below is an idea of the kind of schema I'll use (just the first few tables). There will be one report per file and all tables are ...
1
vote
1answer
59 views

Unique key constraint in mysql

I have a table in mysql which has a many columns,one of them being time.I want HOUR(time) as unique key for the table,that is there should only be one record for every hour.How can i do it?
1
vote
1answer
181 views

SQL Server unique index, per subset of data, based on values of another column

Say I have a Customers table, and an Emails table. Emails table has these fields: Id CustomerId EmailValue Now, I want to create a constraint so that no duplicate emails should be ...
2
votes
2answers
235 views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
2
votes
1answer
419 views

computed column as foreign key allow null

I have two tables. TABLE A: One of them stores some integer identificators, which are unique, but actually are structured entities. I will call them full_id. Actually its a two parted ...
2
votes
1answer
173 views

Multi-column uniqueness constraint, checking a field has one of two values

I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example create table builders ( id text serial ); create table ...
8
votes
3answers
231 views

Is it reasonable to mark all columns but one as primary key?

I have a table representing movies. The fields are: id (PK), title, genre, runtime, released_in, tags, origin, downloads. My database cannot be polluted by duplicated rows, so I want to enforce ...
7
votes
3answers
8k views

Differences between “Unique Key” and “Primary Key”

What are the major differences between Unique Key and Primary Key in MySQL?
0
votes
0answers
45 views

SQLite table design for PK and/or UNIQUE

I'm trying to design a table in SQLite3, where I can perform these 2 actions. INSERT new row where a unique primary key is created. And INSERT new row with an given primary key Think of a ...
0
votes
2answers
188 views

Is it redundant to set your primary key as a unique key?

I'm putting tables together in MySQL workbench and I'm currently ticking my primary keys to also be uniquely indexed. Is this a redundant configuration?
3
votes
5answers
3k 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 ...
1
vote
1answer
56 views

Can you enforce uniqueness in MySQL based on column in foreign table?

I am refactoring one of my database tables and I've come up with an issue. Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c. I have a uniqueness ...
4
votes
3answers
1k views

How does unique key help to improve SQL query performance?

Suppose I have a table with an id column and and I am maintaining unique values manually while inserting and updating records instead of creating a unique key on that column (e.g. by using a date ...
0
votes
1answer
45 views

auto_increment incremented on non-affecting queries

When performing the following query on 'sessions': INSERT IGNORE INTO sessions (user_id, archived) VALUES (3, 0) while: CREATE TABLE `sessions` ( `id` bigint(20) unsigned NOT NULL ...
1
vote
1answer
176 views

Percona MySQL 5.6 databases ignoring key length at constrait creation

We have a 3 servers running Percona MySQL 5.6 in a Master/Master configuration that are all reporting errors on key length, either too long or not specified. The structure of the databases is all ...
2
votes
0answers
96 views

Unique Index much bigger than the table itself

Pg 9.3 on debian 7. I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
0
votes
1answer
35 views

Clustering a heap by date in stead of unique composite key

I've been working on improving the performance of a large reporting database. This database is 2TB in size, and one of the larger tables is a heap with three non-clustered indexes on it. The ...
2
votes
2answers
472 views

Unique across tables

I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users). However, each user can also have additional emails. ...
0
votes
1answer
132 views

Why does InnoDB allow a 772 byte unique constraint?

I've defined a unique constraint for two columns: A bigint and a VARCHAR(256) COLLATE utf8mb4_unicode_ci Then this error occured (which I'm already familiar with): Specified key was too long; max ...
-1
votes
2answers
117 views

Question about choosing primary key

My scenario is that I have a super type named PERSON and two sub types EMPLOYEE and CUSTOMER. I use the Primary key from PERSON, PersonID, in both EMPLOYEE and CUSTOMER as foreign keys. However, I ...
2
votes
1answer
3k views

Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dropped my UNIQUE KEY index 'myindex' on a table and I wanted to change the UNIQUE KEY to something else, so after dropping it I did: alter table mytable add unique key anykey (add1, city, state); ...
0
votes
1answer
174 views

set unique key checks 0 in mydql

In a table i have unique value check for the combination of box_number,price,start_val,end_val so there can not be a two box number with same number which have same price , start_val and end_val.. ...
0
votes
1answer
59 views

Records in a table unique period

I have the following table: +--+---------+-------+--------------+--------------+ |ID|HEADER_ID|VERSION|FROM_DATE_TIME| TO_DATE_TIME| +--+---------+-------+--------------+--------------+ | 1| ...
5
votes
2answers
461 views

Managing CTE execution order under uncertainty

I am writing a large, multi-step CTE for performance reasons. In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero. In a ...
2
votes
1answer
691 views

Foreign Key NULL vs Foreign Key to an empty string

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) CREATE TABLE medicine ( id serial PRIMARY KEY, ...
46
votes
3answers
39k 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), ...
0
votes
1answer
344 views

MySQL performance of applying unique constraint single column vs combination column

I have a large MySQL InnoDB table containing around 137 million rows. I need to add a unique constraint to the table. We have two options: 1) A combination index on three columns, one of which will ...
10
votes
3answers
2k views

Email address unique or primary key?

I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in ...
3
votes
1answer
1k views

Merge Problems SQL Server

I'm trying to create a procedure that will take the values from a temporary table, and load them into another table. The second table has a composite unique constraint across its three columns (Not ...
1
vote
1answer
385 views

MySql - unique key not working

I have the following table: CREATE TABLE `timeline_lists` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `list_type` int(11) NOT NULL, `s_object_id` int(11) NOT NULL ...
0
votes
4answers
124 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...
1
vote
0answers
69 views

Combining fields to make a unique record or keeping them separated

I've been asked to implement what has before, been a bunch of CSV files, into a MySQL database. I'm at a point in the design where all the schemas, tables and fields have been mocked-up; however I'm ...