Een primaire sleutel maken in een bestaande tabel

Probleem

Soms is het nodig om achteraf, wanneer de tabel reeds gemaakt is, een primaire sleutel toe te voegen.

Oplossing

We voegen een Id kolom toe aan de tabel Boeken die we als primaire sleutel gaan gebruiken. Daarna voegen we de primaire sleutel toe. De naam van de primaire sleutel bestaat uit een prefix pk_, gevolgd door de naam van de tabel in pascalnotatie, gevolgd door een underscore en tenslotte de naam van de kolom of kolommen ook in Pascal notatie waarop de primaire sleutel toegepast wordt. Dit is geen verplichting maar een afspraak.

Om een primaire sleutel toe te voegen aan een reeds bestaande tabel, gebruik je de DDL ALTER instructie in combinatie met een DDL ADD instructie:

ALTER TABLE Boeken ADD Id INT auto_increment, ADD CONSTRAINT PK_Person_Id PRIMARY KEY (Id);

Sla de code op in een bestand met de naam Hier volgt de volledige code:

-- JI
-- 23 september 2013
-- Bestandsnaam: BoekenAlterPrimaryKeyAdd.sql
-- eerst een kolom Id toevoegen en dan dan de constraint toevoegen
use ModernWays;
ALTER TABLE Boeken ADD Id INT auto_increment, ADD CONSTRAINT PK_Person_Id PRIMARY KEY (Id);

Je kan ook nagaan of de primaire sleutel is toegevoegd door het volgende statement uit te voeren. Als je de vlogende query uitvoert worden alle indexen van de Boeken tabel getoond:

SHOW INDEXES FROM Boeken;

Als je alleen de primary key(s) voor de Boeken tabel wilt tonen:

SHOW INDEXES FROM Boeken WHERE Key_name = "PRIMARY";

Een primaire sleutel is een CONSTRAINT

Men spreekt van een CONSTRAINT als het een verplichting is. Als je een rij zou toevoegen met een Id waarvan de waarde reeds in een andere rij bestaat, krijg je een foutmelding.

AUTO_INCREMENT

Het is beter om de Id door SQL zelf te laten toekennen. Daarvoor gebruik je de clausule AUTO_INCREMENT. Als je een nieuwe tabel maakt voeg je de clausule toe na de declaratie van de kolom. Zorg ervoor dat je op die kolom een primary key constraint hebt staan:

USE ModernWays;
CREATE TABLE Personen (
    Id int NOT NULL AUTO_INCREMENT,
    Voornaam nvarchar(255) NOT NULL,
    Familienaam nvarchar(255),
    Leeftijd int,
    CONSTRAINT PK_Personen_Id PRIMARY KEY (Id)
);

Je kan de beginwaarde zelf bepalen met:

ALTER TABLE Boeken AUTO_INCREMENT = 5;

Ga naar de MySQL reference voor meer details.

CONSTRAINT verwijderen, wijzigen

Zoals te zien is in bovenstaande tekst is het verplicht om de sleutel woorden constraint en primary key te gebruiken. De naam van de constraint is pk_Boeken_id en het attribuut waaruit de primaire sleutel bestaat is Id. Een primaire sleutel kan uit meerdere attributen bestaan.

Als de Id reeds bestaat en er zit een primary key constraint op, moet je die eerst verwijderen vooraleer je de kolom kan verwijderen.

Een primary key constraint behoort tot de definitie van de tabel. Dus moet je DROP gebruiken (DDL):

-- verkeerde naam gegeven, wijzigen

use ModernWays;
alter table Boeken drop constraint pk_Boeken_Id;

Als je de Id kolom wilt droppen:

use ModernWays;
alter table Boeken drop column Id;

Dan ik krijg je de volgende foutmelding:

ALTER TABLE DROP COLUMN Id failed because one or more objects access this column.

Je kan dus geen kolom verwijderen waarop een constraint ligt. Je moet eerst de constraint verwijderen, de identity wijzigen en de constraint terug toe voegen:

alter table Boeken drop constraint pk_Boeken_Id;
alter table Boeken drop column Id;

Dan pas kan je de kolom weer toevoegen:

use ModernWays;
ALTER TABLE Boeken ADD Id INT auto_increment, ADD CONSTRAINT PK_Person_Id PRIMARY KEY (Id);