how do you reference a foreign key

[kal stevens]

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=@@CH ARACTER_SET_CLI ENT */;
/*!40101 SET @OLD_CHARACTER_ SET_RESULTS=@@C HARACTER_SET_RE SULTS */;
/*!40101 SET @OLD_COLLATION_ CONNECTION=@@CO LLATION_CONNECT ION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHE CKS=@@UNIQUE_CH ECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KE Y_CHECKS=@@FORE IGN_KEY_CHECKS,
FOREIGN_KEY_CHE CKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@ @SQL_MODE, SQL_MODE='NO_AU TO_VALUE_ON_ZER O'
*/;
--
-- 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_S QL_MODE */;
/*!40014 SET FOREIGN_KEY_CHE CKS=@OLD_FOREIG N_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@ OLD_UNIQUE_CHEC KS */;
/*!40101 SET CHARACTER_SET_C LIENT=@OLD_CHAR ACTER_SET_CLIEN T */;
/*!40101 SET CHARACTER_SET_R ESULTS=@OLD_CHA RACTER_SET_RESU LTS */;
/*!40101 SET COLLATION_CONNE CTION=@OLD_COLL ATION_CONNECTIO N */;
/*!40101 SET CHARACTER_SET_C LIENT=@OLD_CHAR ACTER_SET_CLIEN T */;

[Bill Karwin]

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(columnnam e)"
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.

[kal stevens]

Arrg ... thats just retarted.

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

Thank you for your help