[MYSQL] dubbelen in many-to-many table met foreign keys

[nika]

Hallo,

ik zit met het volgende. Ik heb 3 InnoDB tabellen in MySQL, te weten:

user,
event,
userevent

user bevat gebruikersgegevens
event bevat evenementgegevens

userevent vormt de link tussen beiden en bevat 2 velden, namelijk:

userid
eventid

Beide velden hebben constraints in de zin dat ze verwijzen naar respectievelijk user en event dmv een foreign key.

Nu wil ik alleen voorkomen dat een gebruiker en een evenment meer dan een keer aan elkaar kunnen worden verbonden.

Dus: ik maak een composite primary key aan op userid en eventid dacht ik. Maar dat leidt tot de foutmelding in bijvoorbeeld phpadmin dat userid en of eventid niet EN primary EN index kunnen hebben.

Mijn vraag is daarom: hoe zorg ik er op een andere manier voor dat er maar 1 unieke userid - eventid combi kan bestaan in userevent?

[RobIII]

SQL:

CREATE TABLE  `Test`.`event` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `description` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `Test`.`user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `Test`.`userevent` (
  `userid` int(10) unsigned NOT NULL,
  `eventid` int(10) unsigned NOT NULL,
  PRIMARY KEY  USING BTREE (`userid`,`eventid`),
  KEY `FK_userevent_event` (`eventid`),
  CONSTRAINT `FK_userevent_user` FOREIGN KEY (`userid`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_userevent_event` FOREIGN KEY (`eventid`) REFERENCES `event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Werkt hier gewoon

nika schreef op woensdag 11 februari 2009 @ 00:58:
Maar dat leidt tot de foutmelding in bijvoorbeeld phpadmin dat userid en of eventid niet EN primary EN index kunnen hebben.

Je hebt niet toevallig nog indexen staan ergens in die tabel?

[ Voor 69% gewijzigd door RobIII op 11-02-2009 01:09 ]

[nika]

Ja, hier ook, maar nu:

code:

CREATE  TABLE IF NOT EXISTS `test`.`userevent` (
  `userid` INT NOT NULL ,
  `userid` INT NOT NULL ,
PRIMARY KEY (`userid`, `eventid`) ,
  CONSTRAINT `fk_release_user`
    FOREIGN KEY (`userid` )
    REFERENCES `test`.`user` (`userid` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_release_event`
    FOREIGN KEY (`eventid` )
    REFERENCES `test`.`event` (`eventid` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Dus en niet duplicaat en foreign keys.

[ Voor 6% gewijzigd door nika op 11-02-2009 01:12 . Reden: verkeerde copy-paste, excuus ]

[RobIII]

nika schreef op woensdag 11 februari 2009 @ 01:08:
Ja, hier ook, maar nu:

Waarom maak je die indexen dan ook Ik ben geen MySQL expert, maar een primary key is normaliter ook een index hoor

nika wijzigde dit bericht 11-02-2009 01:12 (6%)
Reden: verkeerde copy-paste, excuus

Ah

nika schreef op woensdag 11 februari 2009 @ 01:08:

code:

  `userid` INT NOT NULL ,
  `userid` INT NOT NULL ,

En je hebt 2x userid

nika schreef op woensdag 11 februari 2009 @ 01:08:
Dus en niet duplicaat en foreign keys.

Eh, het is al laat, maar dat doet mijn code toch?

[ Voor 52% gewijzigd door RobIII op 11-02-2009 01:16 ]

[nika]

Hmmm,

interesting. Even afgezien van wat slecht copy-paste werk mijnerzijds, komt het CREATE statement zo uit MySQL Workbench rollen.

Naar aanleiding van je reply heb ik mijn statement aangepast. Er wordt nu een PRIMARY aangemaakt op userid en eventid en vervolgens nog een INDEX op eventid.

Blijkbaar pakt MySQL alleen het eerste veld van de PRIMARY als het gaat om het controleren van integriteit (met user tabel).

Bedankt!

[nika]

Nog even met MySQL Workbench geprobeerd, maar die maakt een extra index aan in de child tabel zodra ik een foreign key definieer.

Vreemd. Een bug?

[RobIII]

nika schreef op woensdag 11 februari 2009 @ 01:28:
Nog even met MySQL Workbench geprobeerd, maar die maakt een extra index aan in de child tabel zodra ik een foreign key definieer.

Vreemd. Een bug?

Nah, ik ken de specifieke vaagheden van MySQL niet goed genoeg om daar iets zinnigs over te zeggen. Ik werk sporadisch met MySQL maar doorgaans word ik verwend met MSSQL
Ik weet wel dat MySQL + indexen wel wat vaagheden heeft (of had) zoals in een query altijd maar 1 index kunnen benutten etc. maar of dat (nog) zo is weet ik niet zo 1 2 3...

[ Voor 16% gewijzigd door RobIII op 11-02-2009 01:35 ]

[cariolive23]

Mijn vraag is daarom: hoe zorg ik er op een andere manier voor dat er maar 1 unieke userid - eventid combi kan bestaan in userevent?

Gewoon een gecombineerde unique-constrataint op deze kolommen aanmaken en klaar ben je.

code:

CREATE UNIQUE INDEX u_userevent ON userevent(userid, eventid);

[Confusion]

nika schreef op woensdag 11 februari 2009 @ 01:18:
Blijkbaar pakt MySQL alleen het eerste veld van de PRIMARY als het gaat om het controleren van integriteit

Absoluut niet.

code:

mysql> create table t1 (foo integer not null);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (bar integer not null);
Query OK, 0 rows affected (0.05 sec)

mysql> create table t3 (foo integer not null references t1 (foo), bar integer not null references t2 (bar));
Query OK, 0 rows affected (0.05 sec)

mysql> alter table t3 add primary key (foo, bar);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t3 values (1,1), (1,2), (1,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t3 values (1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 1

cariolive23 schreef op woensdag 11 februari 2009 @ 07:17:
Gewoon een gecombineerde unique-constrataint op deze kolommen aanmaken en klaar ben je.

Hij heeft er al een primary key opzetten. Die heeft over het algemeen de neiging ook aardig unique te zijn .

[ Voor 22% gewijzigd door Confusion op 11-02-2009 07:41 ]

[cariolive23]

Stom, niet gezien... Het is nog vroeg