Poisto / muutos säännöt

Viite-avaimen (FOREIGN KEY) oletusarvo poistosäännölle on:
ON DELETE RESTRICT
- estää 1-pään rivin poiston jos M-päässä riviin viittauksia

Toinen vaihtoehto
ON DELETE CASCADE
- poistaa 1-pään rivin ja vastaavat rivit myös M -päästä

Kolmas vaihtoehto:
ON DELETE SET NULL
- poistaa 1-pään taulun rivin ja 'tyhjentää' eli asettaa viiteavaimen arvoksi NULL

Samoin ON UPDATE -kohdalla eli sallitaanko (CASCADE) vai kielletäänkö 1-pään avaimen muuttaminen vai merkitäänkö se tyhjäksi arvoksi!

Linkki: MySQL

Viiteavain virhe poistossa

Kun olet syöttänyt Tuoterivi -tauluun (M-pää) rivejä ja yrität poistaa Tuoteryhmä taulusta (1-pää) vastaavaa riviä, saat aikaiseksi seuraavan virheilmoituksen:

#1451 - Cannot delete a parent row: a foreign key constraint fails (`KiukaJu/Tuote`, CONSTRAINT `Tuote_ibfk_1` FOREIGN KEY (`tuoteryhmaID`) REFERENCES `Tuoteryhma` (`tuoteryhmaID`))

Esimerkiksi: DELETE FROM Tuoteryhma WHERE tuoteryhmaID = '11111';

Eli 1 -pään taulusta (parent) et voi poistaa sellaista riviä (tuoteryhmaID = 11111), jolle on syötetty tietoa Monta -pään tauluuun.

Tämä johtuu siitä että tietokannan Tuote -taulu luotiin:

FOREIGN KEY (tuoteryhmaID) REFERENCES Tuoteryhma ON DELETE RESTRICT -poistosäännöllä.
Jos jätät ON DELETE -määrityksen pois niin RESTRICT eli poiston esto on oletusarvo!

Viiteavain virhe lisäyksessä

Jos yrität syöttää sellaista tuotetta, jonka viite-avainta (FOREIGN KEY) ei löydy Tuoteryhma -taulusta saat aikaiseksi virhetilanteen:

Esimerkiksi:
INSERT INTO Tuote(tuoteID,nimi,tuoteryhmaID,yksikkohinta,pakkauskoko,toimitustapa)VALUES ('A1000','Astianpesukone A','99999',100.60,'lava','kotiinkuljetus');

Virheilmoitus:

#1452 - Cannot add a child row: a foreign key constraint fails (`KiukaJu/Tuote`, CONSTRAINT `Tuote_ibfk_1` FOREIGN KEY (`tuoteryhmaID`) REFERENCES `Tuoteryhma` (`tuoteryhmaID`))

Eli et voi lisätä Tuote -tauluun sellaista viite-avaimen arvoa (99999), jota ei löydy 1 -pään taulun perusavaimesta!

Perusavain virhe

Jos yrität syöttää Tuoteryhma -tauluun sellaista koodia (tuoteryhmaID) joka on jo olemassa niin saat aikaiseksi perusavain virheen:

INSERT INTO Tuoteryhma (tuoteryhmaID, nimi) VALUES ('11111','Pakastimet');

Virheilmoitus: #1062 - Duplicate entry '11111' for key 1

Luentokoodit viikko 2

/* 1 -pään taulun perustaminen */
CREATE TABLE Tuoteryhma
(
tuoteryhmaID CHAR(5) PRIMARY KEY,
nimi VARCHAR(50) NOT NULL
)
Type=InnoDB;

/* Tämän jälkeen voit perustaa Monta -pään taulun eli tuotteen: */
CREATE TABLE Tuote
(tuoteID CHAR(5) NOT NULL PRIMARY KEY,
nimi VARCHAR(60) NOT NULL,
tuoteryhmaID CHAR(5),
yksikkohinta DECIMAL(5,2),
pakkauskoko VARCHAR(10),
toimitustapa VARCHAR(30),
FOREIGN KEY (tuoteryhmaID) REFERENCES Tuoteryhma(tuoteryhmaID) ON DELETE RESTRICT ON UPDATE RESTRICT)Type=InnoDB;

/* Seuraavassa lisätään 1 -pään tauluun rivit */
INSERT INTO Tuoteryhma (tuoteryhmaID,nimi)
VALUES ('11111','Ykkösryhmä'),
('22222','kakkosryhmä'),
('33333','kolmasryhmä'),
('44444','neljäsryhmä'),
('55555','viidesryhmä');

/* Ja sitten lisätään M -pään tauluun rivit */
INSERT INTO Tuote(tuoteID,nimi,tuoteryhmaID,yksikkohinta,pakkauskoko,toimitustapa)
VALUES ('A1000','Astianpesukone A','11111',100.60,'lava','kotiinkuljetus');

INSERT INTO Tuote(tuoteID,nimi,tuoteryhmaID,yksikkohinta,pakkauskoko,toimitustapa)
VALUES('A2000','Astianpesukone B','11111',100.60,'lava','kotiinkuljetus');

INSERT INTO Tuote(tuoteID,nimi,tuoteryhmaID,yksikkohinta,pakkauskoko,toimitustapa)
VALUES('A3000','Astianpesukone C','11111',100.60,'lava','kotiinkuljetus');

INSERT INTO Tuote(tuoteID,nimi,tuoteryhmaID,yksikkohinta,pakkauskoko,toimitustapa)
VALUES('B1000','Uuni A','22222',100.60,'lava','kotiinkuljetus');

INSERT INTO Tuote(tuoteID,nimi,tuoteryhmaID,yksikkohinta,pakkauskoko,toimitustapa)
VALUES('B2000','Uuni B','22222',100.60,'lava','kotiinkuljetus');

Lisäyksen yhteydessä tulee huolehtia siitä, että viiteavaimella tuoteryhmaID:lla on kelvollinen arvo, joka löytyy 1-pään taulusta!

Manuaalilinkit: INSERT

Syntaksista

Kaikissa ohjelmointikielissä kielen rakenne kuvataan syntaksin avulla:CREATE TABLE -komennon syntaksi:

http://dev.mysql.com/doc/refman/5.0/en/create-table.html
Merkkien syntaksi:
-------------------------------------------------------------
[ ] --- valinnainen osio eli voit siis jättää sulkujen sisällä olevan määritteen pois:
Syntaksi: [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

Esimerkki perusavaimen määrittelystä (jossa CONSTRAINT on valinnainen):

CREATE TABLE Taulu
( kentta1 CHAR(3),
...,
PRIMARY KEY (kentta1)
);
- tai
CREATE TABLE Taulu
( kentta1 CHAR(3),
...,
CONSTRAINT pkey PRIMARY KEY,...
);
Eli perusavaimen määrittelyn yhteydessä voit jättää perusavaimen nimeämättä!

-------------------------------------------------------------
(pystyviiva) merkitsee tai eli sinun tulee valita jokin tai jompikumpi vaihtoehdoista
Syntaksi:
data_type: BIT[(length)]
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

Esimerkiksi: sinun tulee siis määritellä kentälle jokin tietotyyppi
-------------------------------------------------------------
{} eli valitse vaihtoehdoista jokin
Syntaksi:
table_option:
{ENGINE TYPE} [=] engine_name
Esimerkiksi:
CREATE TABLE Taulu ( ...)
TYPE = InnoDB;
- tai
CREATE TABLE Taulu ( ...)
ENGINE InnoDB
-------------------------------------------------------------
... edellisen osan toisto
Syntaksi:
CREATE TABLE tbl_name (create_definition,...)

Esimerkiksi: ko. syntaksi kertoo että voit tauluun määritellä useamman kentän:

CREATE TABLE Taulu
( kentta1 CHAR(3),
kentta2 VARCHAR(35),
kentta3 INT,
PRIMARY KEY (kentta1)
);