On Mar 17, 8:06 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Mon, 17 Mar 2008 11:29:18 -0700 (PDT),
>
> 123be...@gmail.com wrote:
> >Hello
>
> >I have a problem with creating a foreign key in table members:
>
> >I create a foreign key for userlevel(members) to level(levels)
> >I put in table levels two values: 0 and 9.
> >When I fill the table members, and try to give value 5 for userlevel,
> >I don't get an error (isn't this a foreign key)?
>
> >Following is my script.sql, can anyone please have a look at it what I
> >am doing wrong, and explain me what was wrong?
>
> >Thank you in advance, Betty
>
> For foreign keys (REFERENCES CONSTRAINT) you have to
> use InnoDB. For MyISAM tables, the syntax is accepted,
> but the referential integrity is not enforced.
>
> DROP TABLE IF EXISTS levels;
>
> CREATE TABLE levels (
> level TINYINT(1) UNSIGNED NOT NULL PRIMARY KEY
> );
>
> DROP TABLE IF EXISTS members;
>
> CREATE TABLE members (
> id INT(4) NOT NULL AUTO_INCREMENT,
> userlevel TINYINT(1) UNSIGNED
> NOT NULL
> REFERENCES levels(level),
> username VARCHAR(65) NOT NULL,
> password VARCHAR(65) NOT NULL,
> PRIMARY KEY (id)
> ) ENGINE InnoDB;
> --
> ( Kees
> )
> c[_] The fecal material has hit the air circulating device. (#151)

Thank you for your help Kees.

But it's still not working (used innodb) correctly?