Stack Overflow
Stack Overflow

Questions tagged [unique-constraint]

Unique-constraint is an index that sets one or multiple fields to be unique in a data entity

1353 questions
1068
votes
11 answers

How can I do 'insert if not exists' in MySQL?

I started by googling and found the article How to write INSERT if NOT EXISTS queries in standard SQL which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure…
warren
  • 32,620
  • 21
  • 85
  • 124
1037
votes
14 answers

How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Of course the example is just... an example. So please…
Niyaz
  • 53,943
  • 55
  • 151
  • 182
415
votes
6 answers

Does MySQL ignore null values on unique constraints?

I have an email column that I want to be unique. But I also want it to accept null values. Can my database have 2 null emails that way?
Hugo Mota
  • 11,200
  • 9
  • 42
  • 60
282
votes
5 answers

How do I ALTER a PostgreSQL table and make a column unique?

I have a table in PostgreSQL where the schema looks like this: CREATE TABLE "foo_table" ( "id" serial NOT NULL PRIMARY KEY, "permalink" varchar(200) NOT NULL, "text" varchar(512) NOT NULL, "timestamp" timestamp with time zone NOT…
Baishampayan Ghose
  • 19,928
  • 10
  • 56
  • 60
268
votes
4 answers

Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created?

I have the following table: tickername | tickerbbname | tickertype ------------+---------------+------------ USDZAR | USDZAR Curncy | C EURCZK | EURCZK Curncy | C EURPLN | EURPLN Curncy | C USDBRL | USDBRL Curncy | C USDTRY …
Thomas Browne
  • 23,824
  • 32
  • 78
  • 121
258
votes
4 answers

ERROR: there is no unique constraint matching given keys for referenced table "bar"

Trying to create this example table structure in Postgres 9.1: CREATE TABLE foo ( name VARCHAR(256) PRIMARY KEY ); CREATE TABLE bar ( pkey SERIAL PRIMARY KEY, foo_fk VARCHAR(256) NOT NULL REFERENCES foo(name), …
ams
  • 60,316
  • 68
  • 200
  • 288
211
votes
13 answers

@UniqueConstraint annotation in Java

I have a Java bean. Now, I want to be sure that the field should be unique. I am using the following code: @UniqueConstraint(columnNames={"username"}) public String username; But I'm getting some error: @UniqueConstraint is dissallowed for this…
xyz
  • 2,197
  • 2
  • 14
  • 7
198
votes
5 answers

SQLite table constraint - unique on multiple columns

I can find syntax "charts" on this on the SQLite website, but no examples and my code is crashing. I have other tables with unique constraints on a single column, but I want to add a constraint to the table on two columns. This is what I have that…
Rich
  • 36,270
  • 31
  • 115
  • 154
188
votes
7 answers

MySQL - Make an existing Field Unique

I have an already existing table with a field that should be unique but is not. I only know this because an entry was made into the table that had the same value as another, already existing, entry and this caused problems. How do I make this field…
Lothar
  • 3,409
  • 8
  • 43
  • 58
184
votes
2 answers

Postgresql: Conditionally unique constraint

I'd like to add a constraint which enforces uniqueness on a column only in a portion of a table. ALTER TABLE stop ADD CONSTRAINT myc UNIQUE (col_a) WHERE (col_b is null); The WHERE part above is wishful thinking. Any way of doing this? Or should I…
EoghanM
  • 25,161
  • 23
  • 90
  • 123
160
votes
3 answers

Naming convention for unique constraint

Naming conventions are important, and primary key and foreign key have commonly used and obvious conventions (PK_Table and FK_Table_ReferencedTable, respectively). The IX_Table_Column naming for indexes is also fairly standard. What about the…
Kirk Broadhurst
  • 27,836
  • 16
  • 104
  • 169
158
votes
5 answers

How can I create a unique constraint on my column (SQL Server 2008 R2)?

I have SQL Server 2008 R2 and I want to set a unique column. There seems to be two ways to do this: "unique index" and "unique constraint". They are not much different from what I understand, although unique constraint is recommended by most,…
White Island
  • 2,571
  • 4
  • 17
  • 13
131
votes
19 answers

Unique Constraint in Entity Framework Code First

Question Is it possible to define a unique constraint on a property using either the fluent syntax or an attribute? If not, what are the workarounds? I have a user class with a primary key, but I would like to make sure the email address is also…
kim3er
  • 6,306
  • 4
  • 41
  • 69
93
votes
16 answers

How to delete duplicate entries?

I have to add a unique constraint to an existing table. This is fine except that the table has millions of rows already, and many of the rows violate the unique constraint I need to add. What is the fastest approach to removing the offending rows?…
gjrwebber
  • 2,658
  • 2
  • 22
  • 26
90
votes
14 answers

IntegrityError duplicate key value violates unique constraint - django/postgres

I'm following up in regards to a question that I asked earlier in which I sought to seek a conversion from a goofy/poorly written mysql query to postgresql. I believe I succeeded with that. Anyways, I'm using data that was manually moved from a…
the_man_slim
  • 1,155
  • 2
  • 11
  • 18
1
2 3
…
90 91