Greetings. When I execute the SQL script below in
mySQL 4.1, I get
ERROR 1216: Cannot add or update a child row: a
foreign key constraint fails
It is, of course, choking on the enroll row insert.
Why is this happening? Here are some things that make
the problem go away:
1. Take out the "name VARCHAR(30)" attribute from
student OR change the type to CHAR(30): In Section
6.5.3.1 (second bullet), it says that if one attribute
is variable length, all attributes "silently" become
variable length. Does this mean student.sid is really
a VARCHAR? Could this be related to the problem?
2. Change type of enroll.sid to VARCHAR(5).
3. Change "Earl" to "Early"
My theory: student.sid get "silently changed" from
CHAR to VARCHAR since student.name is VARCHAR;
however, enroll.sid does not change because there are
no variable length fields in enroll. This means that
enroll.sid = "Earl "
student.sid = "Earl"
Consequently, there is no match.
Note that I did try specifying a length for the index
on sid (i.e., "INDEX sidindex (sid(5)),") but that
didn't help. Even shortening to 4 doesn't help, which
doesn't jive with my cohersion theory, assuming I
understand the index length specification.
Ideas?
Thanks in advance!
CREATE TABLE student (
sid CHAR(5) PRIMARY KEY,
name VARCHAR(30)
);
CREATE TABLE course (
cid CHAR(5) PRIMARY KEY,
name CHAR(20)
);
CREATE TABLE enroll (
cid CHAR(5) NOT NULL,
sid CHAR(5) NOT NULL,
PRIMARY KEY(cid, sid),
INDEX sidindex (sid),
FOREIGN KEY (sid) REFERENCES student(sid),
FOREIGN KEY (cid) REFERENCES course(cid)
);
INSERT INTO student VALUES ('Earl', 'Earl Jones');
INSERT INTO course VALUES ('BRTLT', 'British
Literature');
INSERT INTO enroll VALUES ('BRTLT', 'Earl');
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
