[Løst] Referential integrety i MySQL

[William_S]

Hei!

Holder på med et lite skoleprosjekt i databaser hvor jeg skal koble mot en hjemmeside. Men det er ikke den biten jeg lurer på, det er heller hvordan man skal sørge for at referential integrety blir strengt håndhevet i databasen. Saken er den at det går an å sette inn verdier i fremmednøklle kolonnene som ikke eksisterer i tabellen hvor verdiene er primærnøkkel. Dette er kanskje et idiotisk spørsmål, men det skal ikke gå an.

Her er SQL scriptet mitt (som er generert ut fra at datamodelleringsprogram)

/*

Created: 4/18/2011

Modified: 4/18/2011

Model: MySQL 5.0

Database: MySQL 5.0

*/



-- Create tables section -------------------------------------------------



-- Table Movie

DROP TABLE Movie;

DROP TABLE Actor; 

DROP TABLE Role_handling; 

DROP TABLE Review; 

DROP TABLE Reviewer;



CREATE TABLE Movie

(

 MovieID Int NOT NULL,

 Mname Varchar(20) NOT NULL,

 Genre Varchar(20) NOT NULL,

 Year Int(4) NOT NULL

)

;

TYPE = INNODB;



ALTER TABLE Movie ADD PRIMARY KEY (MovieID)

;



ALTER TABLE Movie ADD UNIQUE Movie_ID (MovieID)

;





-- Table Actor



CREATE TABLE Actor

(



 Fname Varchar(20) NOT NULL,

 Lname Varchar(20) NOT NULL,

 Birthplace Varchar(20) NOT NULL,

 Birthdate Date NOT NULL

)

TYPE = INNODB;





ALTER TABLE Actor ADD PRIMARY KEY (ActorID)

;



ALTER TABLE Actor ADD UNIQUE ActorID (ActorID)

;



-- Table Role_handling



CREATE TABLE Role_handling

( 



 Roles Varchar(300),

 MovieID Int NOT NULL,

 ActorID Int NOT NULL

INDEX (MovieID),

INDEX (ActorID),



FOREIGN KEY (MovieID) REFERENCES Movie (MovieID)

FOREIGN KEY (ActorID) REFERENCES Actor (ActorID) 



)

TYPE = INNODB;





--ALTER TABLE Role_handling ADD PRIMARY KEY (MovieID,ActorID)

--;



-- Table Review



CREATE TABLE Review

(

 StarRating Int(1) NOT NULL,

 Rtext Varchar(1000) NOT NULL,

 Rdate Date NOT NULL,

 MovieID Int NOT NULL,

 Rid Int NOT NULL

)

;



ALTER TABLE Review ADD PRIMARY KEY (MovieID,Rid)

;



-- Table Reviewer



CREATE TABLE Reviewer

(

 Rid Int NOT NULL,

 Description Varchar(100)

)

;



ALTER TABLE Reviewer ADD PRIMARY KEY (Rid)

;



ALTER TABLE Reviewer ADD UNIQUE Rid (Rid)

;



-- Create relationships section ------------------------------------------------- 



ALTER TABLE Role_handling ADD CONSTRAINT Relationship1 FOREIGN KEY (MovieID) REFERENCES Movie (MovieID) ON DELETE CASCADE ON UPDATE CASCADE

;



ALTER TABLE Role_handling ADD CONSTRAINT Relationship2 FOREIGN KEY (ActorID) REFERENCES Actor (ActorID) ON DELETE CASCAE ON UPDATE CASCADE

;



ALTER TABLE Review ADD CONSTRAINT Relationship3 FOREIGN KEY (MovieID) REFERENCES Movie (MovieID) ON DELETE NO ACTION ON UPDATE NO ACTION

;



ALTER TABLE Review ADD CONSTRAINT Relationship4 FOREIGN KEY (Rid) REFERENCES Reviewer (Rid) ON DELETE NO ACTION ON UPDATE NO ACTION

;



COMMIT;

 

Dette oppdaget jeg først nå når jeg hadde kommet langt på hjemmesida.

Jeg trodde at så fort man hadde definert primærnøkler og fremmednøkler i en tabell så ville dette bli håndhevet automatisk (altså at man får en feilmelding når man setter inn en ikke eksisterende fremmednøkkel i modertabellen)

Endret 8. mai 2011 av William_S

[torbjørn marø]

Slik jeg forstår det støtter mySQL kun håndheving av foreign key constraints om begge tabellene er InnoDB. Er de det i dette tilfellet?

[William_S]

Slik jeg forstår det støtter mySQL kun håndheving av foreign key constraints om begge tabellene er InnoDB. Er de det i dette tilfellet?

 

Det vet jeg faktisk ikke så mye om. Men kan være forklaringa ja. Har også funnet ut at MySQL bare ignorerer check constraints. Skal teste det nå. Er egentlig et tullete spørsmål jeg stilte, men var vant til at slike ting bare fungerte i OracleSQL. Oracle eier jo MySQL også. Ser at jeg har litt smør på dobbeltflesk i SQL koden jeg postet her. Var bare litt forskjellige ting jeg testet, om modelleringsprogrammet generete ting feil sted osv.

Satte nå dette etter hver eneste tabell i databasen:

CREATE TABLE Review

(

 StarRating Int(1) NOT NULL,

 Rtext Varchar(1000) NOT NULL,

 Rdate Date NOT NULL,

 MovieID Int NOT NULL,

 Rid Int NOT NULL


)

;

ENGINE = INNODB;

 

Det skulle sette INNODB tabell type for hver tabell, men ser ikke ut som referential integretiy blir håndhevet enda. Så det må jo værIe feil et annet sted. PHP skulle iallefall gi meg SQL error, men det skjer ikke. Det skjer heller ikke når jeg kjører MySQL og setter inn fra terminalen ikke eksisterende fremmednøkkelverdier.Mistenker kanskje at relasjonene er satt feil.

Endret 8. mai 2011 av William_S

[torbjørn marø]

Har ikke jobbet med mySQL på mange år selv, så jeg er litt på tynn is når jeg hjelper deg. Men for å være sikker på at tabellene er opprettet med InnoDB kan du visstnok kjøre spørringen "SHOW CREATE TABLE tablename" og se det der...

[William_S]

Har ikke jobbet med mySQL på mange år selv, så jeg er litt på tynn is når jeg hjelper deg. Men for å være sikker på at tabellene er opprettet med InnoDB kan du visstnok kjøre spørringen "SHOW CREATE TABLE tablename" og se det der...

 

 

Da fikk jeg fikset det. Men et annet problem måtte selvsagt oppstå. Nå lar den meg ikke slette tabeller i det hele tatt... Grunnen til det er at jeg i begynnelsen av SQL scriptet har Drop spørringer for å få alt tilbake til sånn det var.

 

Jeg har satt følgende på relasjonene:

ALTER TABLE Role_handling ADD CONSTRAINT Relationship1 FOREIGN KEY (MovieID) REFERENCES Movie (MovieID) ON DELETE CASCADE

;





ALTER TABLE Role_handling ADD CONSTRAINT Relationship2 FOREIGN KEY (ActorID) REFERENCES Actor (ActorID) ON DELETE CASCADE

;



ALTER TABLE Review ADD CONSTRAINT Relationship3 FOREIGN KEY (MovieID) REFERENCES Movie (MovieID) ON DELETE CASCADE 

;



ALTER TABLE Review ADD CONSTRAINT Relationship4 FOREIGN KEY (Rid) REFERENCES Reviewer (Rid) ON DELETE CASCADE 
;

Slik at det skal gå å slette tabeller, men nei det vil ikke la seg gjøre. I Oracle var det bare å si

drop table tabellnavn cascade constraints;

[quantum]

 

)

 

;

 

ENGINE = INNODB;[/code]

 

Det skulle sette INNODB tabell type for hver tabell, men ser ikke ut som referential integretiy blir håndhevet enda. Så det må jo værIe feil et annet sted. PHP skulle iallefall gi meg SQL error, men det skjer ikke. Det skjer heller ikke når jeg kjører MySQL og setter inn fra terminalen ikke eksisterende fremmednøkkelverdier.Mistenker kanskje at relasjonene er satt feil.

 

Se http://dev.mysql.com/doc/refman/5.5/en/using-innodb-tables.html

 

Alle tabellene som er involvert må nok være av innodb type, og seff må også syntaxen være riktig. Om kommandoen heter type=innodb eller engine=innodb tror jeg avhenger litt av hvilken versjon du bruker, pass på å lese doc til riktig versjon.

[William_S]

Da er alle problemer løs.t

Fant ut at jeg redigerte feil .SQL fil og satte engine til InnoDB i konfiurasjonsfila til My SQL installasjonen. Så nå fungerer ting som det skal!