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

7
votes
3answers
168 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 ...
0
votes
0answers
24 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 ...
7
votes
3answers
913 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
2answers
63 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?
1
vote
1answer
36 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 ...
2
votes
1answer
109 views

Multi-row 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 ...
3
votes
3answers
661 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
35 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
58 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
73 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
28 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
97 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
69 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 ...
0
votes
1answer
54 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 ...
-1
votes
2answers
84 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 ...
0
votes
1answer
54 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
32 views

Records in a table unique period

I have the following table: +--+---------+-------+--------------+--------------+ |ID|HEADER_ID|VERSION|FROM_DATE_TIME| TO_DATE_TIME| +--+---------+-------+--------------+--------------+ | 1| ...
2
votes
1answer
360 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, ...
1
vote
2answers
130 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 ...
0
votes
1answer
165 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 ...
8
votes
3answers
1k 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 ...
5
votes
2answers
238 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 ...
1
vote
1answer
329 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 ...
2
votes
1answer
2k 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); ...
1
vote
1answer
181 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
114 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
50 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 ...
1
vote
1answer
181 views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
2
votes
2answers
57 views

Strategy to find the unique bivariate polynomials

I have table that stores Tutte polynomials of a large set of graphs. These are bivariate polynomials with integer coefficients. An example of a polynomial taken from Wikipedia is: Since the ...
0
votes
1answer
97 views

Creating unique constraint to be validated from input

I have this table name tbl with this table definition: create table tbl ( id serial primary key not null, counter integer not null default 1, ref_id integer not null, date_booked date ...
1
vote
0answers
32 views

Clarify unique constraint and index, cannot grasp the concepts to 100% [duplicate]

When reading about UNIQUE, I understand the concept but when it comes to the practical part of it, I get confused and I'm unsure of what it means. What I do understand, is that there are two ways to ...
1
vote
2answers
183 views

Relationship with a table without unique/primary key

I have a database with 3 tables. Table Work, with a field named workName. Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName. And table Activity with ...
0
votes
2answers
96 views

Should I create a multi-column UNIQUE index?

I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits): CREATE TABLE `Visits` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
0
votes
0answers
22 views

Should a surrogate key reflect one of my columns? [duplicate]

I currently have a column called LoadID which is a surrogate key that just increment by max()+1. I also have LoadNumber which always seems to have the exact same value of LoadID, since they will ...
0
votes
1answer
54 views

“key violation” when trying to import data into MS Access

I am trying to import data into an MS Access table from a flat ASCII file. I've done this many times before but now I am getting a "key violation." There is only one key in the table and it's an ...
1
vote
0answers
33 views

Preventing unique key constraint violation when pre-existing row has 'expired' [duplicate]

I have the following table schema; CREATE TABLE LOCKS ( lock_uid INTEGER NOT NULL, file_uid VARCHAR2(32 char) NOT NULL UNIQUE, owner VARCHAR2(255 char) NOT NULL, expires DATE NOT ...
1
vote
1answer
107 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
1
vote
1answer
30 views

Duplicate Data from two column

i have a table which has only two column one is pid(Photo ID) and another one is tid(Tag ID) +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
5
votes
1answer
455 views

Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE index on all fields except for the PRIMARY KEY. The SHOW CREATE TABLE: ...
2
votes
2answers
2k views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
1
vote
1answer
58 views

What problem prevents well defined behavior of a foreign key on a non-unique column?

In my noob opinion, it strikes me that in a serialized environment, this shouldn't be a problem. Is that correct? If not, please explain to me why a foreign key's behavior on a non-unique column is ...
0
votes
1answer
88 views

Is it possible to have a unique key condition based on another value?

I have a table with the following columns: ID, STRING_VALUE, ENUM_VALUE, CATEGORY_IDENTIFIER I'd like to create a condition that won't allow two identical ENUM_VALUEs, but only if their ID is also ...
4
votes
1answer
3k views

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now ...
4
votes
2answers
476 views

How can I enforce ensure uniqueness of an association with a time dimension?

I have a database table AB which tracks the association of A and B over time: create table AB columns ( ARef ..., -- FK of A BRef ..., -- FK of B Since as Timestamp, Until as Timestamp, ...
2
votes
1answer
170 views

Does a SQL server unique constraint partially or fully fail a multi-row write?

If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?
1
vote
1answer
159 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 ...
2
votes
1answer
300 views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting ...
1
vote
1answer
200 views

Unique constraint on multiple nullable columns Sql Server

We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null). We are using this table to figure out which price to assign various combos ...
2
votes
1answer
161 views

ER relation with unique key

I've a problem about my ER diagram. Assume that I want to draw a diagram with student, member and team. Here is my specs: A student does not have to be a member of any team. A student could be a ...
1
vote
1answer
2k views

Duplicate key violates unique constraint

When I use this query everything is fine: UPDATE users SET f_name='Mike', l_name='MyLastName', username='blabla', "password"='asdfsdaf', "class"=12, ...