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

DROP TABLE IF EXISTS members;

CREATE TABLE members (
id INT(4) NOT NULL AUTO_INCREMENT,
userlevel TINYINT(1) UNSIGNED NOT NULL,
username VARCHAR(65) NOT NULL,
password VARCHAR(65) NOT NULL,
PRIMARY KEY (id)
);


DROP TABLE IF EXISTS levels;

CREATE TABLE levels (
level TINYINT(1) UNSIGNED NOT NULL PRIMARY KEY
);


ALTER TABLE members
ADD FOREIGN KEY(userlevel) REFERENCES levels(level);

On Mon, 17 Mar 2008 11:29:18 -0700 (PDT),
123betty@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)

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?

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.