MySQL referential integrity

[tyson.crouch]

G'day,

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?

Regards,
TC

[urtrivedi]

If you want to refer to same table only once than you can do it in same table. If there are more than one references then you need to create another table with one to many relation.

You should always link to primary key, DO NOT USE ATTNAME. It is not a good practice.
you just refer to attid.

[tyson.crouch]

Cheers!

I was thinking that i could refer it to attName just to make it easier when retrieving the name of the attraction.

Regardless of my intent. I will stick to what i've got.

Thanks.