unique foreign key

[Hubertus]

Hallo,

mit der Angabe FOREIGN KEY( fk) REFERENCES x (x_id ) im CREATE TABLE legt MySQL automatisch einen Index an. Wie kann ich es erreichen, dass dieser Index UNIQUE ist? Ein DROP und CREATE UNIQUE geht nicht, weil es ein FOREIGN KEY ist.

Gruß Hubertus

 

[akretschmer]

Hallo,

mit der Angabe FOREIGN KEY( fk) REFERENCES x (x_id ) im CREATE TABLE legt MySQL automatisch einen Index an. Wie kann ich es erreichen, dass dieser Index UNIQUE ist? Ein DROP und CREATE UNIQUE geht nicht, weil es ein FOREIGN KEY ist.

Gruß Hubertus

test=*# create table a (i int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
Time: 309,140 ms
test=*# create table b (a_i int references a unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "b_a_i_key" for table "b"
CREATE TABLE
Time: 115,582 ms
test=*#

Unter PostgreSQL geht es so wie gezeigt.

 

[Hubertus]

Danke, MySQL ist da irgendwie noch nicht so weit ...

 

[akretschmer]

Danke, MySQL ist da irgendwie noch nicht so weit ...

Es ist schon immer wieder schön ...

mysql> create table a (i int primary key);
Query OK, 0 rows affected (0.06 sec)

mysql> create table b (i int references a );
Query OK, 0 rows affected (0.07 sec)

mysql> create unique index idx2 on b(i);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into a values (1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into b values (1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into b values (2);
Query OK, 1 row affected (0.06 sec)

Man beachte, daß in a der Wert 2 nicht vorhanden ist. Man muß da IIRC das separat anlegen, und dann wohl noch mal einen unique index.

 

[Hubertus]

Danke,

ich wollte eigentlich ohne einen zusätzlichen Index auskommen, aber das geht anscheinend in MySQL nicht.
Es sieht so aus, als ob der foreign key nicht geprüft wird. Bei folgender Formulierung lässt sich die "2" nicht einfügen:
create table b (i int, foreign key(i) references a(i) );

Gruß
Hubertus

 

[akretschmer]

Danke,

ich wollte eigentlich ohne einen zusätzlichen Index auskommen, aber das geht anscheinend in MySQL nicht.
Es sieht so aus, als ob der foreign key nicht geprüft wird. Bei folgender Formulierung lässt sich die "2" nicht einfügen:
create table b (i int, foreign key(i) references a(i) );

Gruß
Hubertus

Sicher?

mysql> drop table a;
Query OK, 0 rows affected (0.06 sec)

mysql> drop table b;
Query OK, 0 rows affected (0.06 sec)

mysql> create table a (i int primary key);
Query OK, 0 rows affected (0.07 sec)

mysql> create table b (i int, foreign key(i) references a(i) );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into b values (2);
Query OK, 1 row affected (0.06 sec)

mysql> insert into b values (2);
Query OK, 1 row affected (0.06 sec)

Andreas

​

 

[Hubertus]

Hallo Andreas,

bitte probiere dasselbe nochmal mit vorher
SET FOREIGN_KEY_CHECKS=1;

Dann sollte die "2" nicht inserted werden können. Bei mir ist das jedenfalls so. Es kommt eine Fehlermeldung.

Gruß
Hubertus

 

[akretschmer]

Hallo Andreas,

bitte probiere dasselbe nochmal mit vorher
SET FOREIGN_KEY_CHECKS=1;

Dann sollte die "2" nicht inserted werden können. Bei mir ist das jedenfalls so. Es kommt eine Fehlermeldung.

Gruß
Hubertus

mysql> drop table a;
Query OK, 0 rows affected (0.06 sec)

mysql> drop table b;
Query OK, 0 rows affected (0.06 sec)

mysql> create table a (i int primary key);
Query OK, 0 rows affected (0.07 sec)

mysql> create table b (i int, foreign key(i) references a(i) );
Query OK, 0 rows affected (0.07 sec)

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into b values (2);
Query OK, 1 row affected (0.06 sec)

 

[Hubertus]

Hallo Andreas,

komisch.

drop table if exists b;
drop table if exists a;
create table a (i int primary key);
create table b (i int, foreign key(i) references a(i) );
SET FOREIGN_KEY_CHECKS=1;
insert into b values (2);

liefert bei mir die Fehlermeldung: insert into b values (2) Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`i`) REFERENCES `a` (`i`)) 0.016 sec

Ich habe MySQL Version 5.6.11.

Viele Grüße und vielen Dank
Hubertus

 

[akretschmer]

mysql> insert into b values (2);
Query OK, 1 row affected (0.06 sec)

Es funktioniert bei Angabe engine innodb ;-)

Trotzdem schon doof, keine Warnung, alles okay, Syntax richtig etc.

 

[Hubertus]

Na ja, die Wege des MySQL sind unergründlich. Aber man kann sich das Datenbanksystem, mit dem man zu arbeiten hat, gewöhnlich nicht aussuchen.
Ich komme aus der DB2-Schiene. Da ist die Welt noch halbwegs in Ordnung. PostgreSQL kann ich (noch) nicht.

 

[akretschmer]

PostgreSQL kann ich (noch) nicht.

Der Lernaufwand würde sich aber lohnen...