mysql inserted queries have foreign constraint error (table ok)

Question

I created the following tables in database REGISTRATION:

 CREATE TABLE COLLEGE
              (Cnumber    INT(3) not null,
               Cname        VARCHAR(30) not null,
               Primary key (Cnumber)
    );
 CREATE TABLE DEPARTMENT
              (Dnumber INT(5)  not null,
              Dname  varchar(30) not null,
              Cnumber INT(3),          
              Cprefix         varchar(4) not null,
              Primary key (Dnumber),
              Unique key (Dname),
              Unique key (Cprefix),
              Foreign key (Cnumber) references COLLEGE (Cnumber)
              ON DELETE SET NULL        ON UPDATE CASCADE);

CREATE TABLE STUDENT
        (Snumber INT(9) not null,
        Fname VARCHAR(20) not null,
        Mname VARCHAR(20),
        Lname VARCHAR(20) not null,
        Major varchar(4),
        Pwd   INT(6) not null,
        Stype VARCHAR(6) check (Stype in (‘UGRAD’, ‘GRAD’, ‘PHD’)),
       Primary key (Snumber),
       Foreign key (Major) references DEPARTMENT (Cprefix)
       ON DELETE SET NULL        ON UPDATE CASCADE);

And load data into MySQL no problem for DEPARTMENT:
00001   Accounting  012 ACC
00002   Microbiology    001 MCB
00003   Animal Sciences 001 AMS
00004   Plant Sciences  001 PLS
00005   Landscape Ecology   002 LSE
00006   Heritage Conservation   002 HTC
00007   Early Childhood Education   003 ELC
00008   Special Education   003 SPE
00009   Electrical and Computer Engineering 004 ECE
00010   Civil Engineering   004 CVE
00011   Chemical Engineering    004 CME
00012   Dance   005 DAN
00013   Music   005 MUS
00014   Spanish 006 SPA
00015   German Studies  006 GER
00016   Nursing 008 NUR
00017   Nurse Practitioner  008 NSP
00018   Math    009 MAT
00019   Physics 009 PHY
00020   Chemistry   009 CHE
00021   Pharmaceutical Sciences 010 PMS
00022   Pharmacology    010 PMC
00023   Communication   011 COM
00024   History 011 HIS
00025   Linguistics 011 LIN
00026   Philosophy  011 PHI
00027   Finance 012 FIN

However, when I load STUDENT data into table STUDENT, if the MAJOR is not null or CHE, then it gives the error message:

"ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('registration'.'student', CONSTRAINT 'student_ibfk_1' FOREIGN KEY ('Major') references 'DEPARTMENT' ('Cprefix') ON DELETE SET NULL ON UPDATE CASCADE)
        111111111   John    Sam Davison CHE 123456  UGRAD
        222222222   Jacob   Andy    Oram    CHE 112233  GRAD
        333333333   Ashish  Fu  Bagai   CHE 123123  UGRAD
        444444444   Joe Happy   Harris  CHE 321321  UGRAD
        555555555   Andy    Cyplex  Blignau CHE 654321  PHD
        666666666   Pommie  Meggie  Mbangwa CHE 135246  GARD
        777777777   Ian Pei Healy   CHE 333444  UGRAD
        888888888   Dougie  Max Marilli CHE 444555  PHD
        999999999   Diana   Daisy   Cedeno  CHE 321321  GRAD

Question

Why my STUDENT.Major cannot be any other values in DEPARTMENT.Cprefix except CHE? What is wrong? Thanks!

and you expect us to read that? I don't think even you can read it. Please format the question! — Harry, Nov 13 '14 at 16:12
sorry, it is my first time using it. I am trying to find out how to format....SORRY.... — feijsu, Nov 13 '14 at 16:15
Harry, can you read now? I don't know what I did in editing, but now it seems formatted and readible. Thanks. — feijsu, Nov 13 '14 at 16:16
The error is telling you that you're trying to add/update a row to STUDENT that does not have a valid value for the DEPARTMENT — Harry, Nov 13 '14 at 16:29
possible duplicate of this question http://stackoverflow.com/questions/5005388/cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails — Harry, Nov 13 '14 at 16:30
Thanks! I found my problem is when I create table content in txt, I only hit "enter" for next tuple. However, I need a tab, then hit "enter" for a new tuple. Otherwise the last column won't show correctly. Although it "select * from department" looks alright, but "select Cprefix from department where Dnumber=12" shows empty, and that's why the student.major gave me error except CHE because that particular tuple happened to be correct. — feijsu, Nov 13 '14 at 19:56

score 0 · Answer 1 · answered Nov 14 '14 at 08:41

0

As explained in the comments, the error is telling you that you're trying to add/update a row to STUDENT that does not have a valid value for the DEPARTMENT.

"select Cprefix from department where Dnumber=12" is empty.

answered Nov 14 '14 at 08:41

Harry

3,031
7
42
67

mysql inserted queries have foreign constraint error (table ok)

1 Answers1