[MySQL] Multi column unique i.c.m. foreign key

[Verwijderd]

Bij het ontwerpen van m'n database tabellen loop ik tegen een probleem aan, waarover ik nergens iets kan vinden. Hopelijk weet iemand van jullie het antwoord:

In een many-to-many koppeltabel heb ik 1 unique index ingesteld op meerdere kolommen. Wanneer ik nu echter een relatie (foreign key) in wil stellen, denkt MySQL uitsluitend op de eerste kolom een index te hebben, waardoor ik dus geen foreign keys op de andere kolommen in kan stellen.

De remedie zou zijn om op de andere kolommen nog eens een extra INDEX in te stellen. Is dat de juiste manier om hiermee om te gaan of ben ik dan dubbele indexen op dezelfde columns in aan 't stellen? Kortom, kan het efficienter?

[ Voor 11% gewijzigd door Verwijderd op 08-12-2009 11:25 ]

[Verwijderd]

Ik heb eens bekeken hoe ik dat in zo'n situatie doe. Aangezien ik altijd de queries hiervoor met de hand schrijf heb ik het volgende gemaakt:

SQL:

CREATE TABLE naampje
(
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
project_id SMALLINT(5) NOT NULL,
company_id SMALLINT(5) NOT NULL,
office_id SMALLINT(5) NOT NULL,
contact_id SMALLINT(5) NOT NULL,
UNIQUE KEY(project_id, company_id, office_id, contact_id),
FOREIGN KEY(project_id) REFERENCES project(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(company_id) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(office_id) REFERENCES office(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(contact_id) REFERENCES contact(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Met als resultaat:


't Lijkt mij dus wel vrij normaal, maar ben toch benieuwd hoe andere hier mee om gaan, bookmarked dus.

[mithras]

Ik maak ook nog eens apart indeces aan voor de andere tabellen. Stel je stelt een unique in op k1, k2, k3 en k4, maak ik nog apart een index k2, index k3 en index k4

[Verwijderd]

Overigens heb ik geen flauw idee waarom je nog een apart "id" kolom hebt in deze tabel. Dat is dus echt nergens voor nodig.

[Verwijderd]

Verwijderd schreef op maandag 16 november 2009 @ 18:57:
Overigens heb ik geen flauw idee waarom je nog een apart "id" kolom hebt in deze tabel. Dat is dus echt nergens voor nodig.

Is inderdaad in de meeste gevallen niet nodig, maar als er naar één specifieke row verwezen moet kunnen worden is het wel makkelijk, je weet ook niet hoe het verder gebruikt gaat worden.

@TS:
Verder neem ik aan dat je in de andere tabellen ook smallint(5) gebruikt voor de id's en niet int(10) zoals in deze table?

[RobIII]

Verwijderd schreef op maandag 16 november 2009 @ 19:57:
maar als er naar één specifieke row verwezen moet kunnen worden is het wel makkelijk

Die kun je dan toch specificeren als id1+id2 Ik zie niet waarom daar nog een key aan gehangen zou moeten worden?

[Verwijderd]

Verwijderd schreef op maandag 16 november 2009 @ 19:57:

Verder neem ik aan dat je in de andere tabellen ook smallint(5) gebruikt voor de id's en niet int(10) zoals in deze table?

Hij heeft ze in elk geval unsigned gemaakt

[pedorus]

Wat voor een gekke megarelatie is dit eigenlijk, die optreed tussen een bepaald project, een bepaald bedrijf, een bepaald kantoor en een bepaald contact? Niet bepaald een standaard many-to-many relatie...

[thomaske]

RobIII schreef op maandag 16 november 2009 @ 20:09:
[...]

Die kun je dan toch specificeren als id1+id2 Ik zie niet waarom daar nog een key aan gehangen zou moeten worden?

Ten eerste: voor het beheer van de tabel is het een stuk eenvoudiger (DELETE's en UPDATE's obv 1 key ipv een combinatie van 4 velden). En ten tweede, stel dat ik (later) aan deze tabel een andere tabel koppel met een 1-op-n relatie, dan zou ik voor die 2de tabel een FK moeten maken die bestaat uit alle FK-velden van tabel 1, dan lijkt het me handiger om op id te kunnen koppelen.

[Gomez12]

thomaske schreef op maandag 16 november 2009 @ 23:07:
[...]


Ten eerste: voor het beheer van de tabel is het een stuk eenvoudiger (DELETE's en UPDATE's obv 1 key ipv een combinatie van 4 velden). En ten tweede, stel dat ik (later) aan deze tabel een andere tabel koppel met een 1-op-n relatie, dan zou ik voor die 2de tabel een FK moeten maken die bestaat uit alle FK-velden van tabel 1, dan lijkt het me handiger om op id te kunnen koppelen.

Punt 1 lijkt me over het algemeen niet relevant, voor beheer moet je beheertools schrijven niet toegang geven tot de dbase. Je beheertools kunnen zonder het te tonen perfect werken met een combinatie van 4 velden.
Punt 2 is imho premature optimisation. Als je later die tabel aan een andere tabel wilt koppelen dan moet je het dan regelen. Niet van te voren overal blind een id als primary key invoeren die nu al je updates / inserts vertraagd voor een mogelijk nut in de toekomst...

[cariolive23]

Een UNIQUE-index is een beetje een bijzondere index, het is een constraint. Deze index is lang niet altijd bruikbaar bij vergelijkingen, bv. de derde kolom die in de index staat, is alleen bruikbaar wanneer óók de eerste twee kolommen worden gebruikt in de vergelijking/sortering.

Omdat innoDB (de engine waar het hier om gaat) een bruikbare index nodig heeft om een FK aan te maken, is de UNIQUE-index alleen bruikbaar voor de FK op de eerste kolom die in de index staat. Voor alle andere kolommen zullen eigen indexen moeten worden aangemaakt. Wanneer je de FK's aanmaakt met het SQL-statement ALTER TABLE, zal innoDB zelf indexen aanmaken. Dat jouw tooltje dit niet ondersteunt, het is niet anders, het is maar PhpMyAdmin.

Leer SQL en je bent van dit soort onzinnige probleempjes verlost, ze worden veroorzaakt door jouw tool. En je gebruikt geen tools om meer problemen te krijgen, of wel dan?

Ps. Een id lijkt mij hier ook niet zinvol, een PK op de combinatie van kolommen ter vervanging van de huidige UNIQUE-constraint is voldoende. Je moet de records toch opsporen op basis van de inhoud, niet op basis van de onbeduidende waarde van het id.

Pps. Er zijn verschillende types indexen, het zal hier gaan om een btree.

[ Voor 3% gewijzigd door cariolive23 op 17-11-2009 08:51 . Reden: btree ]

[JayVee]

cariolive23 schreef op dinsdag 17 november 2009 @ 08:49:
Een UNIQUE-index is een beetje een bijzondere index, het is een constraint. Deze index is lang niet altijd bruikbaar bij vergelijkingen, bv. de derde kolom die in de index staat, is alleen bruikbaar wanneer óók de eerste twee kolommen worden gebruikt in de vergelijking/sortering.

Omdat innoDB (de engine waar het hier om gaat) een bruikbare index nodig heeft om een FK aan te maken, is de UNIQUE-index alleen bruikbaar voor de FK op de eerste kolom die in de index staat. Voor alle andere kolommen zullen eigen indexen moeten worden aangemaakt.

Volgens mij is een UNIQUE index een key + constraint. Als het een "normale" index was geweest had je hetzelfde probleem gehad. Het probleem is dus niet de UNIQUE key, maar de multiple-column index in het algemeen. Zie Multiple-Column Indexes