SQL Unique Key Constraint und NULL Werte

[Yuuri]

Hallo zusammen,

bin vorhin auf ein Problem gestoßen und weiß noch nicht so recht, wie ich da rangehen soll.

Gegeben ist eine Baumstruktur, spezieller eine Kategoriestruktur, mit Kategorie ID und Parent ID. Beide IDs referenzieren auf die eigentliche Kategorie. Zusätzlich sind Kategorie und Parent ID mit einem Unique Index versehen, sodass keine Dopplungen auftreten können. Die Parent ID kann allerdings NULL sein, denn die oberste Ebene besitzt ja sozusagen keinen Parent bzw. einen Parent "0".

Hierbei fiel mir auf, dass MySQL NULL-Werte nicht eindeutig definiert. Konkret: Ich kann mehrere Datensätze mit den Werten (5,NULL) erzeugen, wobei ich davon ausgegangen bin, dass (5,NULL) eindeutig ist und hierbei der Unique Index greift, sodass ich beim INSERT ON DUPLICATE KEY UPDATE keine doppelten Datensätze erhalte. Falsch gedacht, denn bei MySQL (und auch im SQL-Standard, so wie ich quer gelesen habe) gilt (NULL != NULL) = (NULL = NULL) = false. Ergo gilt der Unique Constraint nicht, sobald NULL Werte in einer Spalte auftreten, selbst wenn die restlichen Werte übereinstimmen.

Preisfrage hierbei: Wie löse ich den Fall bzw. wie löst man diesen Fall generell, sodass auch die Referenzen gewahrt bleiben? NULL nicht zulassen und stattdessen Dummy Datensätze in Tabellen mit einer 0er ID eintragen? Mit Triggern hantieren wollte ich eigentlich nicht. Lässt sich vielleicht die Tabelle so erstellen, dass NULL eindeutig definiert wird/ist? Was ist hierbei Best Practice?



LG


Zur Veranschaulichung:

Spoiler

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
	`id` INT(11) PRIMARY KEY AUTO_INCREMENT
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

DROP TABLE IF EXISTS `cat`;
CREATE TABLE `cat` (
	`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
	`cat` INT(11) NOT NULL,
	`par` INT(11),

	UNIQUE KEY `cat_par`(`cat`,`par`),
	FOREIGN KEY (`cat`) REFERENCES `categories`(`id`)
		ON UPDATE NO ACTION
		ON DELETE NO ACTION,
	FOREIGN KEY (`par`) REFERENCES `categories`(`id`)
		ON UPDATE NO ACTION
		ON DELETE NO ACTION
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO `categories` VALUES (NULL),(NULL),(NULL),(NULL),(NULL);

INSERT INTO `cat` (`cat`,`par`) VALUES (2,1); -- ok
INSERT INTO `cat` (`cat`,`par`) VALUES (3,1); -- ok
INSERT INTO `cat` (`cat`,`par`) VALUES (4,1); -- ok
INSERT INTO `cat` (`cat`,`par`) VALUES (2,1); -- fehler
INSERT INTO `cat` (`cat`,`par`) VALUES (3,1); -- fehler
INSERT INTO `cat` (`cat`,`par`) VALUES (1,NULL); -- ok
INSERT INTO `cat` (`cat`,`par`) VALUES (2,NULL); -- ok
INSERT INTO `cat` (`cat`,`par`) VALUES (1,NULL); -- ok?!
INSERT INTO `cat` (`cat`,`par`) VALUES (2,NULL); -- ok?!

 

[d2boxSteve]

Hi,
definiere dein Null selber, z.B. myNULL und änder diese Spalte auf default Wert myNULL.
Dann nur noch drauf achten, dass nirgends explizit null reingeschrieben wird.

PS: wenns ne Integer Spalte ist kannste die größtmögliche Zahl als "null" nehmen.

 

[John Reese]

Vor dem Problem bzw. der Frage stand ich letztens auch.
Du kannst doch auch einen partiellen Index erstellen,
bei mir sah das bspw. so aus:

CREATE UNIQUE INDEX ratings_profile_id ON ratings (profile_id) WHERE profile_id IS NOT NULL

 

[Yuuri]

@ d2boxSteve: MySQL kann keine eigenen Typen definieren. Auch kann ich nicht mein NULL selbst festlegen bzw. INT_MAX verwenden, denn die Integritäten werden so nicht gewahrt (bzw. wäre es das Selbe, wie wenn ich einen Dummy Datensatz mit der ID 0 verwende).

@ CryNickSystems: Wie das?

https://dev.mysql.com/doc/refman/5.0/en/create-index.html

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_type]

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

bzw. für 5.7 genauso

http://dev.mysql.com/doc/refman/5.7/en/create-index.html

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] 
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

 

[Drexel]

Was spricht denn dagegen den Root mit Id 0, -1 oder was auch immer anzulegen?

 

[Yuuri]

Der Rattenschwanz. Man muss dann in jedem Query drauf achten, dass man den Datensatz 0 ausklammert, man dann in jedem Fall nen extra Datensatz braucht, ... Mit nem einfachem NULL wäre es gegessen, aber leider spielt da MySQL nicht mit.

 

[Wictale]

Bäume in MySQL ... oha.
Einen Tot musst du da sterben.
Wenn das Ganze nur für ein Hobbyprojekt benötigt wird, und du die freie Wahl hast, nimm lieber eine Postgres.
Da gibt es die "NULL Problematik" zwar auch, jedoch kann man mit dieser Probleme viel geschmeidiger lösen.