*Mysql version: 5.0.51a*
I used the following create table command:
Code:
CREATE TABLE `book_loan` (`isbn` varchar(13) NOT NULL,
`student_no` varchar(12) NOT NULL,
`borrow_date` date NOT NULL,
`due_date` date NOT NULL,
PRIMARY KEY (`isbn`,`student_no`,`borrow_date`),
CONSTRAINT `book_loan_ibfk_1`
FOREIGN KEY (`isbn`) REFERENCES `books` (`isbn`) ON DELETE CASCADE,
CONSTRAINT `book_loan_ibfk_2`
FOREIGN KEY (`student_no`) REFERENCES `students` (`student_no`) ON
DELETE CASCADE)
ENGINE=InnoDB;
When I use show create table, I'm expecting to get this:
Code:
CREATE TABLE `book_loan` (`isbn` varchar(13) NOT NULL,
`student_no` varchar(12) NOT NULL,
`borrow_date` date NOT NULL,
`due_date` date NOT NULL,
PRIMARY KEY (`isbn`,`student_no`,`borrow_date`),
KEY `isbn` (`isbn`),
KEY `student_no` (`student_no`),
CONSTRAINT `book_loan_ibfk_1`
FOREIGN KEY (`isbn`) REFERENCES `books` (`isbn`) ON DELETE CASCADE,
CONSTRAINT `book_loan_ibfk_2`
FOREIGN KEY (`student_no`) REFERENCES `students` (`student_no`) ON
DELETE CASCADE)
ENGINE=InnoDB;
But instead I get this:
Code:
CREATE TABLE `book_loan` (`isbn` varchar(13) NOT NULL,
`student_no` varchar(12) NOT NULL,
`borrow_date` date NOT NULL,
`due_date` date NOT NULL,
PRIMARY KEY (`isbn`,`student_no`,`borrow_date`),
KEY `student_no` (`student_no`),
CONSTRAINT `book_loan_ibfk_1`
FOREIGN KEY (`isbn`) REFERENCES `books` (`isbn`) ON DELETE CASCADE,
CONSTRAINT `book_loan_ibfk_2`
FOREIGN KEY (`student_no`) REFERENCES `students` (`student_no`) ON
DELETE CASCADE)
ENGINE=InnoDB;
Why do I see only one index?? This Seems to contradict with MySQL docs which say MySQL automatically creates an index on the referencing column whenever you create a foreign key.
dBforums
>
Why mysql creates an index for one foreign key and ignores the other?
03-07-10, 01:44
Why mysql creates an index for one foreign key and ignores the other?
Linear Mode
