Why does this SQL query work?

I am following Phil Greenspun's tutorial on SQL.

Phil uses Oracle for the tutorial but I am using MySQL. Here are my queries:

mysql> CREATE TABLE mailing (
    -> email varchar(100) not null primary key,
    -> name varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO mailing (email, name) VALUES ('foo@bar.com', 'FooBar');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE phone (
    -> email varchar(100) not null references mailing,
    -> phone varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

As you can see, I have set the email column of phone table to be a reference.

The tutorial says:

开发者_StackOverflow社区

The phone table has a referential integrity constraint ("references mailing") to make sure that we don't record e-mail addresses for people whose names we don't know

Then how come this query works?:

mysql> INSERT INTO phone (email, phone) VALUES ('new@new.com', '112223');
Query OK, 1 row affected (0.00 sec)

... note that I don't have new@new.com in the mailing table.

What am I missing?

References in MySQL are available only for InnoDB engine, rewrite your CREATE TABLE as follows:

 CREATE TABLE phone (
    -> email varchar(100) not null references `mailing` (`email`),
    -> phone varchar(20)
    -> ) ENGINE=InnoDB;

By default, MySQL uses MyISAM engine that ignores "references" statement. Read more documentation on creating tables in MySQL

UPDATE: The foreign key reference must be placed outside the CREATE TABLE statement. Thanks, @jswolf and @a_horse_with_no_name

CREATE TABLE phone (
-> email varchar(100) not null,
-> phone varchar(20),
-> REFERENCES `mailing` (`email`)
-> ON DELETE NO ACTION
-> ON UPDATE NO ACTION
-> ) ENGINE=InnoDB;

Did not test, but more likely it will work.

In addition to Nemoden's answer:

Your next problem will be Phil's example with the check constraint which is not supported by MySQL at all (and there is no storage engine that does this).

As Nemoden's answer, you should use InnoDB, not MyISAM.

The FOREIGN KEY relationship can (and should in MYSQL) be added like this:

CREATE TABLE phone
( email varchar(100) NOT NULL
, phone varchar(20) DEFAULT NULL

, FOREIGN KEY fk_mailing (email)         --- the "fk_mailing" name is optional
    REFERENCES mailing (email)
) 
  ENGINE=InnoDB 
  DEFAULT CHARSET = utf8 ;               --- or other charset you prefer

From the tutorial you use:

I'm using MySQL, and I wanted to comment on a snag I ran into while I was following the tutorial in this page. Maybe other newbies can benefit from this.

As far as I can tell:

a) MySQL supports different "storage engines" for tables. This is presumably a good thing. However, not all engines support referencial constraints.

b) For a MySQL table to support a "references" constraint, it must be of type InnoDB. In my installation (on SuSE Linux, right out of a standard RPM binary package), this is not the default. So you have to either change the server configuration to make this the default, or specify "ENGINE = InnoDB" after the closing parenthesis in the table definition.

c) Even for InnoDB, the syntax described by Phil above does not work, though it is not rejected, merely ignored. According to the manual, this is effectively just a comment to the developer that this column is supposed to reference another column, even if the constraint is not enforced by mysql.

d) So, the only way to make this kind of constraint work is to: 1. make the table InnoDB and 2. use the "FOREIGN KEY (email) REFERENCES mailing_list(email)" format as a separate entry inside the table definition.

[MySQL won't even give a warning! Not even a reminder that such reference clauses are merely "comments". It will just happily ignore them and allow any old value in that row. Ugh.]

  -- Antonio Ramirez, March 19, 2007