Foreign keys (MySQL)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

Basics

Alles bij elkaar:

De tabel customers en orders hebben een 1-op-veel-relatie. De velden met een '*' zijn de primaire sleutels van de betreffende tabellen. Veld orders.customerNumber is een foreign key (want ergens anders is-ie PK). Tabel customers is de parent table of referenced table. De andere tabel is de child table of referencing table. Een tabel kan meerdere foreign keys bevatten. Zelf gebruik ik enkelvoud voor de namen van tabellen, en nooit camel case: Altijd alles in kleine letters en met underscores als scheidingsteken tussen woorden

Hoe importeer je gerelateerde records?

Hoe importeer je records + subrecords, waarbij de PK's automatisch worden geregeld? Tot op heden (herfst 2019) maakte ik in zulke gelegenheden zelf PK's aan, en maakte ik geen gebruik van referentiële integriteit. Dat heeft z'n beperkingen (bv. als je een hoofdrecord verwijderd, blijven er wezen-subrecords over). Waarschijnlijk kan dit handiger/beter...

Hoe het vermoedelijk werkt:

  • De data moet vantevoren al passende PK/FK's hebben
  • In de tabel-definitie incorporeer je deze PK/FK
  • Je importeert de data
  • Klaar.

PK's/FK's - Wat voor sleutels?

Tja, wat is wijsheid, betreffende systeem voor PK's/FK's?

Natuurlijke sleutels

Een natuurlijke sleutel heeft betrekking op een unieke eigenschap van de betreffende records. Dat is meestal een 'externe' grootheid. Dus iets wat buiten de database om is gedefineerd. Tot op heden gebruikte ik in deze situatie zelfgedefineerde PK's/FK's.

Nadelen:

  • Als de inhoud van de records veranderen, is er een kans dat deze sleutels niet meer accuraat weergeven wat er in het record zit
  • Ik kan niet automatisch records toevoegen, zoals dat wel kan bij gebruik van auto_increment - Maar komt dit echt voor?
  • Altijd een kans dat de pk toch niet uniek blijkt te zijn.

Voordelen:

  • Lekker duidelijk, want aan de sleutel zag je al waar het om ging.
  • Gemakkelijk bij importeren

Surrogaat-sleutels

Surrogaat-sleutels of kunstmatige sleutels, zijn sleutels die alleen binnen de betreffende db betekenis hebben, zoals GUID's en auto-increments.

GUID's

Met GUID's wordt bedoeld, dat er willekeurige sleutels bedacht worden. Klinkt nogal bizar, maar komt vaak voor [1]

Nadelen:

  • Onhandig om mee te werken, want lang
  • Ik vind het een hele rare aanpak, om zo met een niet-deterministische oplossing te komen.

Voordelen:

  • Gemakkelijk bij importeren, als je de sleutels al vantevoren moet aanmaken
  • Handig voor replication - Da's ver van m'n bed.

auto-increments

Nadelen:

  • Bij import van child tables misschien lastig om de sleutels vantevoren goed te krijgen? Mag je auto-increment-velden überhaupt overschrijven?
  • Bij mergen van tabellen kun je problemeen krijgen - Beetje theoretisch.

Voordelen:

  • Dit is bv. wat WordPress gebruikt, en dat geeft vertrouwen
  • Gemakkelijk
  • Je wordt niet afgeleid, zoals bij eigen sleutels, waarvan de inhoud van de tabel misschien niet meer overeen komt met de sleutel
  • Data-efficiënt.

Overig:

  • Je kunt auto-increment-velden later van andere waardes voorzien. Zie test-script elders in dit artikel.
  • Dit scenario gaat er van uit, dat alle PK's een eigen auto-incrementveld zijn. Dat kun je bij WordPress goed zien: Elk object heeft een eigen PK

Globale auto-increments

Dit is het bastaardkind van auto-increments & GUID: Bouw een sproc die de globale lijst van nummers beheert, zodat elke entititeit een uniek veld krijgt. Voor mij is dat overkill.

Conclusies

Ik gebruik auto-increment-velden, met aparte PK's per object - Net als WordPress (okt. 2019).

Referentiële integriteit

Kleine test [2]: 

# Experiments with FK's & referential integrity
##############################################################################################################
#
use tmp;

##############################################################################################################
# Define tables
##############################################################################################################
#
# Drop parent table first - Even when they're both empty
########################################################
#
drop table if exists product;


# Then drop the other table
########################################################
#
drop table if exists category;


# Create child table first
########################################################
#
create table category
(
    category_id		int		auto_increment primary key,
    category_name	varchar(100)	not null
);


# Create parent table second - Otherwise FK-error
########################################################
#
create table product
(
	product_id		int	auto_increment primary key,
        product_name	varchar(100)	not null,
        category_id		int,
        
        constraint fk_category
	    foreign key (category_id)
            references category(category_id)
);


##############################################################################################################
# Insert some data
##############################################################################################################
#
# Child table first
########################################################
#
insert into category(category_name)
values
	("Smartphone"),
    ("Smartwatch");


# Parent table
########################################################
#
insert into product(product_name, category_id)
values
	("Note 10", 1);


##############################################################################################################
# Test: Kun je een auto_increment-veld overschrijven?
##############################################################################################################
#
select * from product;

update product
set product_id = 12 where product_id=1;

select * from product;

##############################################################################################################
# Debug
##############################################################################################################
#
# select * from category;
# select * from product;

Zie ook

Bronnen

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Foreign_keys_(MySQL)&oldid=19730"