Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20

    Unanswered: Help with syntax is MySQL

    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
    Last edited by Alex Wright; 02-16-09 at 04:00.

  2. #2
    Join Date
    Jun 2007
    Posts
    197

    Smile

    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

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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.

  4. #4
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20
    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.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2007
    Posts
    197

    Smile

    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

  7. #7
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20
    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
    Last edited by Alex Wright; 02-16-09 at 08:39.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20
    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

  10. #10
    Join Date
    Jun 2007
    Posts
    197

    Smile

    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
    Last edited by ankur02018; 02-16-09 at 09:15.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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)
    );

  13. #13
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20
    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
    Last edited by Alex Wright; 02-16-09 at 10:36.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •