Introductie tot de ALTER TABLE-instructie in SQL

In SQL zijn er grofweg drie soorten commando’s:

• DDL - staat voor Data Definition Language en omvat SQL-commando’s voor het definiëren van de database, zoals het aanmaken van tabellen, specificaties opgeven, tabellen wijzigen, enzovoort. Voorbeelden van DDL zijn CREATE, DROP, ALTER, etc. Wijzigingen die door DDL-commando’s worden uitgevoerd, kunnen niet worden teruggedraaid.

• DML - staat voor Data Manipulation Language en laat je select-, insert-, update- en delete-queries uitvoeren. De wijzigingen die door DML-commando’s worden uitgevoerd, kunnen worden teruggedraaid. Voorbeelden: SELECT, INSERT, UPDATE en DELETE.

• DCL - staat voor Data Control Language en laat je rechten, permissies en andere controles van je databasesysteem specificeren.

In deze tutorial zie je het gebruik van de ALTER TABLE-instructie, die tot DDL behoort. De volgende onderwerpen komen aan bod:

• De noodzaak van de ALTER TABLE-instructie
• Een tabel maken in een PostgreSQL-database
• Verschillende ALTER TABLE-instructies uitvoeren in PostgreSQL

Let op: Deze tutorial legt niet uit hoe je een tabel maakt, data in een tabel invoegt of data uit een tabel selecteert in PostgreSQL. Als je deze onderwerpen wilt opfrissen, bekijk dan dit artikel.

De noodzaak van de ALTER TABLE-instructie

Specificaties van een database kunnen in de loop van de tijd veranderen. Zo kan het zijn dat je een kolom aan een bepaalde tabel moet toevoegen, of dat je een extra primary key aan een tabel wilt toevoegen. Je kunt ook het datatype van een specifieke kolom in een tabel moeten wijzigen. Het is vaak geen optie om een tabel te droppen en helemaal opnieuw te maken. Met de instructie ALTER TABLE kun je dit allemaal heel soepel doen. Met ALTER TABLE kun je:

• Een kolom toevoegen, wijzigen of verwijderen
• Een constraint toevoegen of verwijderen

Je kunt ook constraints in- of uitschakelen, maar dat valt buiten de scope van deze tutorial.

Je ziet elk van bovenstaande use-cases in deze tutorial, maar voordat we daarmee aan de slag gaan, heb je een tabel in PostgreSQL nodig. In de volgende sectie maak je een eenvoudige tabel.

Een tabel maken in een PostgreSQL-database

Je maakt een tabel met de naam student_details in PostgreSQL (met de pgAdmin-tool) met de volgende kolommen en datatypes:

• student_id (integer)
• student_full_name (character)
• student_year (integer)

Je begint met deze tabel met slechts drie kolommen. Later, terwijl je door de tutorial gaat, voeg je meer kolommen toe en wijzig je de tabel op verschillende manieren. De volgende create-query maakt een tabel volgens bovenstaande specificaties:

CREATE TABLE student_details(
 student_id SERIAL,
 student_full_name VARCHAR (50),
 student_year VARCHAR (100)
);

Laten we ook een paar records in deze tabel invoegen:

• INSERT INTO student_details(student_full_name, student_year) VALUES('John Doe','1st');
• INSERT INTO student_details(student_full_name, student_year) VALUES('Chris Villa','3rd');

Omdat het datatype van student_id is ingesteld op SERIAL, verhoogt PostgreSQL de kolomwaarde automatisch en wordt het datatype door PostgreSQL standaard geïnterpreteerd als integer.

De tabel student_details zou er zo uit moeten zien:

Je hebt nu een tabel klaarstaan in PostgreSQL. Laten we in de volgende sectie kijken naar verschillende toepassingen van ALTER TABLE.

Verschillende ALTER TABLE-instructies uitvoeren in PostgreSQL

In deze sectie zie je het gebruik van de instructie ALTER TABLE op de volgende twee manieren, zoals hierboven genoemd:

• Een kolom toevoegen, wijzigen en verwijderen
• Een constraint toevoegen en verwijderen

Een kolom toevoegen, wijzigen of verwijderen

Laten we beginnen met het toevoegen van nog een kolom met de naam student_grade (van het type character) aan de tabel:

ALTER TABLE student_details ADD student_grade VARCHAR(5);

De tabel zou nu als volgt moeten zijn:

Let op dat voor de records die je hebt ingevoerd de waarden null zijn voor de kolom student_grade. Dit gebeurt impliciet bij de meeste RDBMS’en. Je kunt echter altijd een update-query uitvoeren om de waarden bij te werken. Laten we nu zien hoe je een kolom wijzigt met ALTER TABLE. Je verandert de maximale toegestane grootte van de kolom student_year van 100 naar 10, omdat te veel ruimte toewijzen voor slechts drie tekens (student_year kan 1st, 2nd of 3rd zijn) niet erg praktisch is.

ALTER TABLE student_details ALTER COLUMN student_year TYPE VARCHAR(10);

Je ziet het resultaat in de volgende afbeelding:

Je kunt ook een kolom hernoemen. Zo doe je dat:

ALTER TABLE student_details RENAME student_id TO student_roll;

Bovenstaande query hernoemt de kolom student_id naar student_roll. Je kunt dit ook verifiëren met een select-query; je resultaat zou moeten lijken op de volgende afbeelding:

Laten we nu zien hoe je een kolom uit een tabel kunt verwijderen:

ALTER TABLE student_details DROP COLUMN student_year;

De query verwijdert de kolom student_year en de tabel na uitvoering van de query zou er zo uitzien:

Let op: als de kolom die je verwijdert wordt gerefereerd door een andere tabel, kun je fouten krijgen vanwege schending van referentiële integriteit.

Nu zie je hoe je constraints aan een tabel toevoegt en verwijdert.

Een constraint toevoegen en verwijderen.

De tabel student_details heeft geen primary key. Een primary key is een combinatie van not null en unique constraints. Laten we de kolom student_roll (die eerder is aangepast vanuit student_id) als primary key van student_details instellen:

ALTER TABLE student_details ADD PRIMARY KEY (student_roll);

Je kunt ook andere constraints toevoegen; zo kun je een check-constraint toevoegen aan de kolom student_grade zodat de waarden altijd A, B of C zijn.

ALTER TABLE student_details ADD CONSTRAINT year_check CHECK (student_grade IN ('A', 'B', 'C'));

year_check is een naam (placeholder) voor de constraint die je aan de tabel toevoegt. Als je nu de volgende invoer in de tabel probeert te doen, zou je een fout moeten krijgen:

INSERT INTO student_details(student_full_name, student_grade) VALUES('David Lune','3rd');

De fout ziet er ongeveer zo uit:

Een constraint verwijderen gaat vergelijkbaar. Je moet de naam van de constraint opgeven die je wilt verwijderen. Laten we de constraint verwijderen die je year_check hebt genoemd:

ALTER TABLE student_details DROP CONSTRAINT year_check;

Laten we dit verifiëren. Als de constraint year_check daadwerkelijk is verwijderd, dan zou deze query INSERT INTO student_details(student_full_name, student_grade) VALUES('David Lune','3rd'); geen fout meer mogen veroorzaken. De tabel bevat nu het record:

Je kunt ook met andere check-constraints experimenteren.

Dat was het voor deze tutorial!

Deze tutorial heeft je geïntroduceerd in de ALTER TABLE-instructie om de schema’s van een tabel effectief te wijzigen. Wil je je SQL-skills verder aanscherpen, dan zijn de volgende DataCamp-cursussen misschien interessant: