On Mar 17, 8:37 pm, 123be...@gmail.com wrote:
> 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?

Got it!

Your INNODB-suggestion helped me a lot.

It was necessary to create an index on the foreign key + both tables
had to be innodb.