how do you reference a foreign key

I have been trying to write a database schema in mysql, and I cant
figure this out.

Here is a database schema



DROP DATABASE IF EXISTS d;
CREATE DATABASE d;

USE d;


CREATE TABLE t (
tid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE s (
sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
tid INT NOT NULL REFERENCES t(tid),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


I thought this would work, at least that is what the manual says.
I also tried Foreign Key instead of references, but I am having trouble
using Hibernate.

But I dont think it is referencing the foreign key properly.
When I back up my schema with the administrator tool, it does not have
the foreign key referenced.
I am using Server version 4.1.14
What am I doing wrong?


Here is the back up database schema


Thanks

Kal



-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 4.1.14


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;


--
-- Create schema d
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ d;
USE d;

--
-- Table structure for table `d`.`s`
--

DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
`sid` int(11) NOT NULL auto_increment,
`tid` int(11) NOT NULL default '0',
PRIMARY KEY (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `d`.`s`
--

/*!40000 ALTER TABLE `s` DISABLE KEYS */;
LOCK TABLES `s` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `s` ENABLE KEYS */;


--
-- Table structure for table `d`.`t`
--

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`tid` int(11) NOT NULL auto_increment,
PRIMARY KEY (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `d`.`t`
--

/*!40000 ALTER TABLE `t` DISABLE KEYS */;
LOCK TABLES `t` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `t` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Re: how do you reference a foreign key

kal stevens wrote:
> When I back up my schema with the administrator tool, it does not have
> the foreign key referenced.
> I am using Server version 4.1.14
> What am I doing wrong?

You're not doing anything wrong. The syntax you're using is correct
according to the SQL standard, and is recognized by MySQL without error.

However, MySQL silently parses but ignores foreign keys when using the
MyISAM storage engine. MyISAM does not enforce the foreign key
relationship, or even store any information about it. It just accepts
the syntax and discards it. The purpose is to allow schema from other
database vendors to be imported to MySQL without generating errors.

IMHO, this is a terrible idea for MySQL to have done this. They
*should* generate an error when we try to use a feature that is not
going to function as we expect. It is misinformation that the syntax is
accepted but is inoperative.

MySQL AB occasionally states an intention to add foreign key support to
the MyISAM storage engine, but so far this hasn't been implemented.

To enforce foreign keys, you must use InnoDB or BDB as your storage
engine for the tables in the foreign/primary relationship. Most people
seem to choose InnoDB.

Furthermore, even when using table storage types that support foreign
keys, the syntax of "columnname datatype REFERENCES table(columnname)"
doesn't seem to work either, even though the syntax is documented and
accepted without warning or error. You have to use a table-level
constraint, on its own line, such as:

CREATE TABLE s (
sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
tid INT NOT NULL,
FOREIGN KEY(tid) REFERENCES t(tid),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

See http://bugs.mysql.com/bug.php?id=13301

Regards,
Bill K.

Re: how do you reference a foreign key

Arrg ... thats just retarted.

Yes, they should at least give a warning or something.

Thank you for your help