Help with syntax is MySQL

Alex Wright · #1 (**permalink**) 02-16-09, 02:57

Hi there,

I have a problem that I hope you can help me with.

In MySQL I've created two tables: one called person that contains people's ID's, names, addresses, DOB's and suchlike. The other table - named interests - contains the person's interests. What I'd like to do is create an additional table that contains the user's ID in one field with their interests presented besides it in another (for example: 233 & Fishing or 455 & Sport).

The 'person' field is a foreign key to the person table; the 'interest' field a foreign key to the interest table.

When I use the following code to create the third table I receive an error message (error code was 1500 - something about not being able to create the table):

Code:

CREATE TABLE hobbies (
person SMALLINT UNSIGNED,
interests VARCHAR(20),
CONSTRAINT pk_hobbies PRIMARY KEY (person, interests),
CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES person (person_id), 
CONSTRAINT fk_interests FOREIGN KEY (interests) REFERENCES interests (name)
);

Can anybody advise as to where I'm going wrong? I don't have a Internet connection at home at the moment so I apologise if there are any delays to your responses.

Alex

ankur02018 · #2 (**permalink**) 02-16-09, 05:31

Quote:

Originally Posted by Alex Wright

Hi there,

I have a problem that I hope you can help me with.

In MySQL I've created two tables: one called person that contains people's ID's, names, addresses, DOB's and suchlike. The other table - named interests - contains the person's interests. What I'd like to do is create an additional table that contains the user's ID in one field with their interests presented besides it in another (for example: 233 & Fishing or 455 & Sport).

The 'person' field is a foreign key to the person table; the 'interest' field a foreign key to the interest table.

When I use the following code to create the third table I receive an error message (error code was 1500 - something about not being able to create the table):

Code:

CREATE TABLE hobbies (
person SMALLINT UNSIGNED,
interests VARCHAR(20),
CONSTRAINT pk_hobbies PRIMARY KEY (person, interests),
CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES person (person_id), 
CONSTRAINT fk_interests FOREIGN KEY (interests) REFERENCES interests (name)
);

Can anybody advise as to where I'm going wrong? I don't have a Internet connection at home at the moment so I apologise if there are any delays to your responses.

Alex

You had not define what engine are you using
if MyISAM engine it will not supprt foreign key
but if you use Innodb then you can use foreign key relation

shammat · #3 (**permalink**) 02-16-09, 05:51

Quote:

Originally Posted by ankur02018

You had not define what engine are you using
if MyISAM engine it will not supprt foreign key
but if you use Innodb then you can use foreign key relation

But he wouldn't get an error message with MyISAM.
MySQL will simply ignore the constraints without telling the user.

Quote:

Originally Posted by Alex Wright

When I use the following code to create the third table I receive an error message (error code was 1500 - something about not being able to create the table):

The correct error message would be useful.
I tried your syntax and it works fine for me.
Maybe a typo when creating the other tables?
Are the datatypes for the PK and FK fields the same?

It would help if you posted the full DDL for all tables.

Alex Wright · #4 (**permalink**) 02-16-09, 06:14

Quote:

Originally Posted by ankur02018

You had not define what engine are you using
if MyISAM engine it will not supprt foreign key
but if you use Innodb then you can use foreign key relation

I've been able to use foreign keys before in the past without specifying which DB engine I'm using so I don't believe that that's the problem here.

healdem · #5 (**permalink**) 02-16-09, 06:39

MySQL will allow you to define as many foreign keys as you wish, however if you are using the MyISAM engine it will ignore such definitions, as MyISAM and other MySQL engines will ignore other features which are not (yet) supported.

ankur02018 · #6 (**permalink**) 02-16-09, 06:58

Quote:

Originally Posted by Alex Wright

I've been able to use foreign keys before in the past without specifying which DB engine I'm using so I don't believe that that's the problem here.

Use syntax Show create table tablename \G;

it will show what engine are you using foreingn key is actullay formed are not

Alex Wright · #7 (**permalink**) 02-16-09, 07:02

Quote:

Originally Posted by shammat

But he wouldn't get an error message with MyISAM.
MySQL will simply ignore the constraints without telling the user.

The correct error message would be useful.
I tried your syntax and it works fine for me.
Maybe a typo when creating the other tables?
Are the datatypes for the PK and FK fields the same?

It would help if you posted the full DDL for all tables.

I'm not at home at the moment but the error message was something along the lines of: "Unable to create table. Error 1500".

Here's how the other two tables have been created:

person

Code:

CREATE TABLE person (
person_id SMALLINT UNSIGNED AUTO_INCREMENT,
last_name VARCHAR(20),
first_name VARCHAR(20),
title VARCHAR(20),
birth_date DATE,
address VARCHAR(30),
city VARCHAR(20),
county VARCHAR(20),
country VARCHAR(20),
post_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);

interests

Code:

CREATE TABLE interests (
interest_id SMALLINT UNSIGNED AUTO_INCREMENT,
name VARCHAR(20),
CONSTRAINT pk_interests PRIMARY KEY (interest_id)
);

Does that help?

Alex

r937 · #8 (**permalink**) 02-16-09, 07:12

the error message (i seem to recall it's error number 150 or something equally helpful) is not enough to pinpoint the problem

usually it's because the PK anf FK are subtly different, like one might be SMALLINT and the other SMALLINT UNSIGNED

that not being the case here, it's gotta be the other reason -- no index on your FK

in recent versions of mysql, the index is created "silently," but in earlier versions, you have to do it yourself --

Code:

CREATE TABLE hobbies 
( person SMALLINT UNSIGNED
, interests VARCHAR(20)
, CONSTRAINT pk_hobbies 
    PRIMARY KEY ( person , interests )
, CONSTRAINT fk_person 
    FOREIGN KEY ( person ) REFERENCES person ( person_id )
, INDEX ix_interests ( interests )
, CONSTRAINT fk_interests 
    FOREIGN KEY ( interests ) REFERENCES interests ( name )
 );

note that the FK on person can piggyback on the PK's index (which is created by default)

Alex Wright · #9 (**permalink**) 02-16-09, 07:32

Not having an Internet connection at home at the moment (have to wait until Sunday) is frustrating as I have to wait hours befofe I can put your suggestions into practice.

I will print off this thread and when I'm home I'll try what you've recommended and report back again tomorrow and let you know whether or not the problem has been fixed.

Thanks for all of your help.

Alex

ankur02018 · #10 (**permalink**) 02-16-09, 08:04

Here is final solution

CREATE TABLE person (
person_id SMALLINT UNSIGNED AUTO_INCREMENT,
last_name VARCHAR(20),
first_name VARCHAR(20),
title VARCHAR(20),
birth_date DATE,
address VARCHAR(30),
city VARCHAR(20),
county VARCHAR(20),
country VARCHAR(20),
post_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);

CREATE TABLE interests (
interest_id SMALLINT UNSIGNED ,
name VARCHAR(20),
CONSTRAINT pk_interests PRIMARY KEY (name)
);

CREATE TABLE hobbies (
person SMALLINT UNSIGNED,
interests VARCHAR(20),
KEY ix_interests (interests),
CONSTRAINT pk_hobbies PRIMARY KEY (person, interests),
CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES person (person_id),
CONSTRAINT fk_interests FOREIGN KEY (interests) REFERENCES interests (name)
);

Make name colum in interests table as primary key

One important thing If you are using MyISAM engine as default then there will be no error
but If you are using default engine innodb then only you get foreign key error

use show create table hobbies\G; You will know the engine is Innodb

r937 · #11 (**permalink**) 02-16-09, 08:07

Quote:

Originally Posted by ankur02018

Here is final solution

...

Make name colum in interests table as primary key

then what would be the reason for keeping interest_id?

perhaps you need to post a "final final" solution

shammat · #12 (**permalink**) 02-16-09, 08:42

Quote:

Originally Posted by Alex Wright

interests

Code:

CREATE TABLE interests (
interest_id SMALLINT UNSIGNED AUTO_INCREMENT,
name VARCHAR(20),
CONSTRAINT pk_interests PRIMARY KEY (interest_id)
);

Does that help?

Yes, it does

The Primary key for the table interests is interest_id but in your table hobbies you have a column interests that "references" the column name in interests. But you can only reference primary (or unique) keys.
So you'll need to reference interest_id from your interests table (which makes a lot more sense than storing the "name" a second time).

Hobbies should look like this:

Code:

CREATE TABLE hobbies (
person SMALLINT UNSIGNED,
interest SMALLINT UNSIGNED,
CONSTRAINT pk_hobbies PRIMARY KEY (person, interest),
CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES person (person_id), 
CONSTRAINT fk_interests FOREIGN KEY (interest) REFERENCES interests (interest_id)
);

Alex Wright · #13 (**permalink**) 02-16-09, 09:10

Quote:

Originally Posted by shammat

Yes, it does

The Primary key for the table interests is interest_id but in your table hobbies you have a column interests that "references" the column name in interests. But you can only reference primary (or unique) keys.
So you'll need to reference interest_id from your interests table (which makes a lot more sense than storing the "name" a second time).

Hobbies should look like this:

Code:

CREATE TABLE hobbies (
person SMALLINT UNSIGNED,
interest SMALLINT UNSIGNED,
CONSTRAINT pk_hobbies PRIMARY KEY (person, interest),
CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES person (person_id), 
CONSTRAINT fk_interests FOREIGN KEY (interest) REFERENCES interests (interest_id)
);

Of course! That makes a lot of sense now. <embarrassed/>

Ideally, what I'd like is an additional table (hobbies) that lists the person's ID number alongside what he\she is interested in ('name' field from the interests table). As I'm not able to reference the field directly, is there any other way of achieving the desired results without breaking the rules of normilization?

Having the person's ID and the ID of the catergories they're interested in besides each other is pointless in this instance as the user of the database would be required to keep browsing back 'n forth between the other tables to check who person_id number 1 is for example, or what interest_id number 4 is.

Would I be better off making the 'interest_id' field a foreign key to the interest table from the person table?

--

I'm still new to database modelling, so apologies if my questions seem stupid at this stage.

Alex

r937 · #14 (**permalink**) 02-16-09, 09:30

Quote:

Originally Posted by Alex Wright

Having the person's ID and the ID of the catergories they're interested in besides each other is pointless in this instance as the user of the database would be required to keep browsing back 'n forth between the other tables to check who ID number 1 is for example, or what category interest ID 4 is.

no, it isn't pointless

most times, that's exactly what the many-to-many table has in it -- just two id columns, where the combination of them is the PK, and each sre FKs to their respective tables

what you seem to be missing is the idea that you don't show the users the ids, you show them the names, which is accomplished with a join query

Quote:

Originally Posted by Alex Wright

Would I be better off making the 'interest_id' field a foreign key to the interest table from the person table?

absolutely not, as that way you'd only be able to record one interest per person

Quote:

Originally Posted by Alex Wright

I'm still new to database modelling, so apologies if my questions at this stage seem stupid.

don't apologize -- we were all at that same point at some time in the past

Alex Wright · #15 (**permalink**) 02-16-09, 09:49

Quote:

Originally Posted by r937

no, it isn't pointless

most times, that's exactly what the many-to-many table has in it -- just two id columns, where the combination of them is the PK, and each sre FKs to their respective tables

what you seem to be missing is the idea that you don't show the users the ids, you show them the names, which is accomplished with a join query

absolutely not, as that way you'd only be able to record one interest per person

don't apologize -- we were all at that same point at some time in the past

Thanks r937,

I like to think that it's all starting to digest now.

I have actually read through a whole introductory book on database design, and although I like to think that I understood all of the concepts, when it came to creating my first database on Saturday, let's just say that I felt less than comfortable. Still, trial and error is the best method of learning.

Internet Services

Web Development

Digital Marketing

Consumer Tech