I've got a table
CREATE TABLE IF NOT EXISTS `attractions` ( `attID` int(11) NOT NULL AUTO_INCREMENT, `attName` varchar(255) NOT NULL, `adultPrice` double NOT NULL, `childPrice` double NOT NULL, `referenceID` varchar(255), PRIMARY KEY (`attID`), KEY (`attName`) ) ENGINE=InnoDB;
Now my initial set up was that referenceID was linked to attID (the primary key). However, i need to change that to reference attName.
The referenceID is more or less the location of an attraction, which may be either itself (NULL) OR another attraction. I've tried setting attName as a KEY but i think that i can only reference a PRIMARY KEY. Is there a way around this?