---
title: "MySQL och UNIQUE INDEX"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/99095-mysql-och-unique-index"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "Hansen"
published: "2004-03-09T16:33:32.000Z"
updated: "2004-03-09T16:33:32.000Z"
replies: 0
views: 252
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/99095-mysql-och-unique-index"
---

# MySQL och UNIQUE INDEX

## #1 — Hansen, 2004-03-09T16:33Z

Detta har bitit mig och andra, postas här för kännedom.

Tabell och data

```
CREATE TABLE test (
  title   CHAR(1) NOT NULL PRIMARY KEY,
  sortkey INTEGER NOT NULL UNIQUE
);

INSERT INTO test VALUES ( 'A', 1 );
INSERT INTO test VALUES ( 'B', 2 );
INSERT INTO test VALUES ( 'C', 3 );
INSERT INTO test VALUES ( 'D', 4 );
```

Problem

Detta statement borde/skall fungera:

```
  UPDATE test
     SET sortkey = sortkey + 1
```

Du kommer få detta felmeddelandet: *Duplicate entry '2' for key '2'*

Workaround

Ibland är det möjligt att använda denna workaround.
 

```
  UPDATE test
     SET sortkey = sortkey + 1
ORDER BY sortkey DESC

  UPDATE test
     SET sortkey = sortkey - 1
ORDER BY sortkey ASC
```

Varför?

> **Mark Matthews @ MySQL skrev:**
>
> Because MyISAM tables are not transactional, MySQL \_can't\_ defer
> constraint checking to end-of-statement, because if a constraint was
> violated during the processing of the statement, there would be nothing
> to roll-back to. Therefore, MySQL has to perform constraint checks at
> row-update time. This behavior is the same across all table types,
> transactional or not, for consitencies' sake.

Marks inlägg
<http://groups.google.se/groups?selm=bbqkjc%2412fq%241%40FreeBSD.csie.NCTU.edu.tw>

Hela tråden:
<http://groups.google.se/groups?threadm=bbop7d%242s4l%241%40FreeBSD.csie.NCTU.edu.tw>

Hmm, i konsekvensens namn... Undra vad konsekvensen borde vara!

Nu vet ni IAF :)

Permalänk: https://www.webforum.nu/p/99095

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/99095-mysql-och-unique-index