Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Need help on a sql script

    Hi I'm working on a MySql script for a software I'm developing on a school project, however I'm not particulary familiar with MySql databases.
    But I keep getting issues with the database. since the entities are in norwegian it is quite simply the sqp code I need some help with.

    Remember this is just a draft for the script all entities are not finalized.
    Right now I keep getting this errorcode

    <</* ** Legger inn gyldige data i tabellene */ INSERT INTO SunnCateringAS
    VALUES (

    '1337', 'Sunn Catering AS', 'Cateringveien 2', '80081351', '0001'
    );

    MySQL sa:

    #1452 - Cannot add or update a child row: a foreign key constraint fails (`kjetinb`.`SunnCateringAS`, CONSTRAINT `SunnCateringAS_ibfk_1` FOREIGN KEY (`Postnr`) REFERENCES `Sted` (`Postnr`)) >>

    script start
    /*
    ** objektorientert-script-mysql.txt
    /*
    ** DROP TABLE-setninger som sletter gamle tabeller
    */

    DROP TABLE IF EXISTS SunnCateringAS;
    DROP TABLE IF EXISTS Kunder;
    DROP TABLE IF EXISTS Bestillinger;
    DROP TABLE IF EXISTS Sted;
    DROP TABLE IF EXISTS Ansatte;
    DROP TABLE IF EXISTS Avdeling;

    /*
    ** Oppretter tabeller med entitetsintegritet
    */
    CREATE TABLE SunnCateringAS
    (
    Org_Nr INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Firma_navn VARCHAR(50),
    adresse VARCHAR(50),
    telefon INT(50),
    Postnr INT(50) UNSIGNED,
    PRIMARY KEY(Org_Nr)
    )TYPE=INNODB;

    CREATE TABLE Kunder
    (
    Kunde_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Navn VARCHAR(50),
    Telefon INT(50),
    Ordre_Id INT(50) UNSIGNED,
    Epost VARCHAR(50),
    Allergier VARCHAR(50),
    PRIMARY KEY(Kunde_id)
    )TYPE=INNODB;

    CREATE TABLE Bestillinger
    (
    Ordre_Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Forhaandsbestillinger VARCHAR(50),
    Direktebestillinger VARCHAR(50),
    Spesialbestillinger VARCHAR(50),
    Pakkelosninger VARCHAR(50),
    Tilbud VARCHAR(50),
    PRIMARY KEY(Ordre_id)
    )TYPE=INNODB;

    CREATE TABLE Sted
    (
    Postnr INT UNSIGNED NOT NULL,
    Post_Sted VARCHAR(50),
    PRIMARY KEY(Postnr)
    )TYPE=INNODB;

    CREATE TABLE Ansatte
    (
    Ansatt_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Avd_id INT(50) UNSIGNED,
    Navn VARCHAR(50),
    Telefon INT(50),
    Epost VARCHAR(50),
    PRIMARY KEY(Ansatt_id)
    )TYPE=INNODB;

    CREATE TABLE Avdeling
    (
    Avd_id INT(8) UNSIGNED NOT NULL,
    PRIMARY KEY(Avd_Id)
    )TYPE=INNODB;

    /*

    ** Legger paa referanseintegritet (fremmednoekler)
    */
    ALTER TABLE Ansatte
    ADD FOREIGN KEY(Avd_id)REFERENCES Avdeling(Avd_id);

    ALTER TABLE Kunder
    ADD FOREIGN KEY(Ordre_Id)REFERENCES Bestillinger(Ordre_Id);

    ALTER TABLE SunnCateringAS
    ADD FOREIGN KEY(Postnr)REFERENCES Sted(Postnr);


    /*
    ** Legger inn gyldige data i tabellene
    */

    INSERT INTO SunnCateringAS VALUES('1337','Sunn Catering AS','Cateringveien 2','80081351','0001');
    INSERT INTO SunnCateringAS VALUES('1014','Catering Sunn AS','Sunnveien 2','73530302','0002');

    INSERT INTO Kunder VALUES('1','Hilde Hildurson','45859565','1','mumihilde91@gmail.com', 'Ingen');
    INSERT INTO Kunder VALUES('2','Kathrine Kathrineson','16468696','2','mumikathrine92@hotmai l.com','Gluten allergi');
    INSERT INTO Kunder VALUES('3','Juba Jubalai','87986321','3','bublabai@online.no','Sjok oladeallergi');
    INSERT INTO Kunder VALUES('4','Kjetil Superson','99773762','4','jokki_pallanoit@samtinge t.sa','Fiskeallergi');

    INSERT INTO Bestillinger VALUES('1','Ja','Nei','Ingen','Nei','30%rabatt');
    INSERT INTO Bestillinger VALUES('2','Nei','Ja','Glutenallergi','Nei','Nei') ;
    INSERT INTO Bestillinger VALUES('3','Ja','Nei','Sjokoladeallergi','Ja','50% rabatt');
    INSERT INTO Bestillinger VALUES('4','Nei','Ja','Fiskeallergi','Ja','Nei');

    INSERT INTO Sted VALUES(7053,'Ranheim');
    INSERT INTO Sted VALUES(7054,'Ranheim');
    INSERT INTO Sted VALUES(7043,'Tyholt');
    INSERT INTO Sted VALUES(7041,'Trondheim');

    INSERT INTO Ansatte VALUES('41','3','Kokk Kokkesen','98574822','k.kokkesen@sunn.no');
    INSERT INTO Ansatte VALUES('14','3','Lokk Kokkelure','99554521','l.kokkelure@sunn.no');
    INSERT INTO Ansatte VALUES('44','3','Spock Matomnom','95784521','s.matomnom@sunn.no');
    INSERT INTO Ansatte VALUES('1','1','Leder Sjefsen','99889988','l.sjefsen@sunn.no');
    INSERT INTO Ansatte VALUES('2','2','Sekretær Hjelpesdottir','98745622','s.hjelpesdottir@sunn.no ');
    INSERT INTO Ansatte VALUES('3','4','Svein Olgt','98554422','s.olgt@sunn.no');
    INSERT INTO Ansatte VALUES('4','4','Kari Selgerino','94651235','k.selgerino@sunn.no');
    INSERT INTO Ansatte VALUES('5','4','Billeba Trivelig','99453521','b.trivelig@sunn.no');

    INSERT INTO Avdeling VALUES('1');
    INSERT INTO Avdeling VALUES('2');
    INSERT INTO Avdeling VALUES('3');
    INSERT INTO Avdeling VALUES('4');

    /*
    ** Avslutter transaksjonen og lagrer tabellene og data fysisk i databasen
    */

    COMMIT;
    script end

    I would really appriciate some help
    best regards Ashley

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    [quote]'1337', 'Sunn Catering AS', 'Cateringveien 2', '80081351', '0001'
    );

    MySQL sa:

    #1452 - Cannot add or update a child row: a foreign key constraint fails (`kjetinb`.`SunnCateringAS`, CONSTRAINT `SunnCateringAS_ibfk_1` FOREIGN KEY (`Postnr`) REFERENCES `Sted` (`Postnr`)) >>{/quote]
    its telling you that it cannot insert the row into table because to do so would violate the foreign key constraint
    Code:
    ALTER TABLE SunnCateringAS
    ADD FOREIGN KEY(Postnr)REFERENCES Sted(Postnr);
    you need to populate the primary key table(s) FIRST, then populate the table(s) with Foreign Keys.

    so change the sequence / order you try to insert the data
    insert data into table Sted first
    then make certain that you have data for each foreign key, ie for every value of Postnr in SunnCateringAS there is a similar value in Sted.Postnr first.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    Thanks it works perfectly now

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •