hello all
i am getting confused with foreign key constraints..
i have the following table:
-
--
-
-- Table structure for table `reviewer`
-
--
-
-
CREATE TABLE `reviewer` (
-
`ReviewerID` varchar(8) NOT NULL,
-
`Reviewer_fname` varchar(100) NOT NULL,
-
`Reviewer_lname` varchar(100) NOT NULL,
-
`Reviewer_address` varchar(300) NOT NULL,
-
etc...
-
...
-
...
-
PRIMARY KEY (`ReviewerID`)
-
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
-
-
-- Table structure for table `papers`
-
--
-
-
CREATE TABLE `papers` (
-
`PaperID` varchar(8) NOT NULL,
-
`Title` varchar(100) NOT NULL,
-
`FileName` varchar(100) NOT NULL,
-
`FileType` varchar(50) NOT NULL,
-
`FileSize` int(11) NOT NULL,
-
`FileContent` mediumblob NOT NULL,
-
`FileMime` varchar(50) NOT NULL,
-
`AuthorID` varchar(8) NOT NULL,
-
ect....
-
...
-
-
PRIMARY KEY (`PaperID`),
-
KEY `AuthorID` (`AuthorID`)
-
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
-
now i have another table where i have put both reviewerID and paperID as primary key. But i am getting confused about how to put the foreign keys..
-
CREATE TABLE `reviewer_paper` (
-
`ReviewerID` varchar(8) NOT NULL,
-
`PaperID` varchar(8) NOT NULL,
-
`Date_rated` date default NULL,
-
etc..
-
...
-
-
PRIMARY KEY (`ReviewerID`,`PaperID`),
-
KEY `PaperID` (`PaperID`)
-
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
-
ALTER TABLE `reviewer_paper`
-
ADD CONSTRAINT `reviewer_paper_ibfk_1` FOREIGN KEY (`PaperID`) REFERENCES `papers` (`PaperID`) ON DELETE CASCADE ON UPDATE CASCADE;
-
-
-
but i am not sure if i have done this right..
do i need to remove the index i placed on paperID and just put the primary key
PRIMARY KEY (`ReviewerID`,`PaperID`)
also for the foreign key, do i need to add the foreign keys for reviewerID also, i.e
-
ALTER TABLE `reviewer_paper`
-
ADD CONSTRAINT `reviewer_paper_ibfk_1` FOREIGN KEY (`ReviewerID`) REFERENCES `reviewer` (`ReviewerID`) ON DELETE CASCADE ON UPDATE CASCADE;
plzzz help
