Results 1 to 10 of 10

Thread: MySQL Relations Problem

  1. #1
    Join Date
    Nov 2010
    Location
    Damanhour-North-Egypt
    Posts
    90

    Default MySQL Relations Problem

    Hello every one,


    Im having a problem in setting up a relation between two tables

    the first table (the parent) : visitors_data

    Code:
    +-------------+------------------+------+-----+---------+----------------+
    | Field       | Type             | Null | Key | Default | Extra          |
    +-------------+------------------+------+-----+---------+----------------+
    | visitor_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | visitor_ip  | varchar(50)      | YES  |     | NULL    |                |
    | access_date | datetime         | YES  |     | NULL    |                |
    +-------------+------------------+------+-----+---------+----------------+
    the secound table (the child) : messages_data

    Code:
    +---------------+------------------+------+-----+---------+----------------+
    | Field         | Type             | Null | Key | Default | Extra          |
    +---------------+------------------+------+-----+---------+----------------+
    | message_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | visitor_id    | int(10) unsigned | NO   |     | NULL    |                |
    | visitor_name  | varchar(50)      | YES  |     | NULL    |                |
    | visitor_email | varchar(50)      | YES  |     | NULL    |                |
    | message_date  | datetime         | YES  |     | NULL    |                |
    | message_txt   | varchar(255)     | YES  |     | NULL    |                |
    +---------------+------------------+------+-----+---------+----------------+
    note that the two tables are empty

    now i'd like to add a relation to bind messages_data.visitor to visitors_data.visitor_id
    i've tried the following command

    Code:
    mysql> ALERT TABLE messages_data
        -> ADD FOREIGN KEY (visitor_id) REFERENCES visitors_data (visitor_id)
        -> ON DELETE CASCADE;
    but i always get the following error

    Code:
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALERT TABLE messages_data
    ADD FOREIGN KEY (visitor_id) REFERENCES visitors_data ' at line 1
    here is the out put of mysqladmin version :

    Code:
    mysqladmin  Ver 8.42 Distrib 5.1.53, for suse-linux-gnu on i686
    Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL license
    
    Server version		5.1.53-log
    Protocol version	10
    Connection		Localhost via UNIX socket
    UNIX socket		/var/run/mysql/mysql.sock
    Uptime:			3 hours 15 min 57 sec
    
    Threads: 1  Questions: 22  Slow queries: 0  Opens: 20  Flush tables: 1  Open tables: 10  Queries per second avg: 0.1

  2. #2
    Join Date
    Feb 2010
    Location
    Germany
    Posts
    4,654

    Default Re: MySQL Relations Problem

    mostafaxxx wrote:

    > ALERT TABLE


    ALTER TABLE

    --
    PC: oS 11.3 64 bit | Intel Core2 Quad Q8300@2.50GHz | KDE 4.6.2 | GeForce
    9600 GT | 4GB Ram
    Eee PC 1201n: oS 11.4 64 bit | Intel Atom 330@1.60GHz | KDE 4.6.0 | nVidia
    ION | 3GB Ram

  3. #3
    Join Date
    Nov 2010
    Location
    Damanhour-North-Egypt
    Posts
    90

    Default Re: MySQL Relations Problem

    hahaha

    by the way when i set a column as primary key does that make it index key too

  4. #4
    Join Date
    Feb 2010
    Location
    Germany
    Posts
    4,654

    Default Re: MySQL Relations Problem

    mostafaxxx wrote:

    >
    > hahaha
    >
    > by the way when i set a column as primary key does that make it index
    > key too
    >

    I do not know the answer, because I do not use mysql, but as far as I can
    see you can show the indexes in mysql with the command
    Code:
    SHOW INDEX FROM <table name>
    so you can easily verify with it if it is indexed or not.

    --
    PC: oS 11.3 64 bit | Intel Core2 Quad Q8300@2.50GHz | KDE 4.6.3 | GeForce
    9600 GT | 4GB Ram
    Eee PC 1201n: oS 11.4 64 bit | Intel Atom 330@1.60GHz | KDE 4.6.0 | nVidia
    ION | 3GB Ram

  5. #5
    Join Date
    Jun 2008
    Location
    Germany
    Posts
    301

    Default Re: MySQL Relations Problem

    Hi,

    martin_helm is right, in MySQL key is a synonym for index. If you use MyISAM as storage engine the primary key is a unique index/key but if you use InnoDB it is the primary key is a clustered Index/key. You should keep this in mind when using InnoDB.

    Hope this helps

  6. #6

    Default Re: MySQL Relations Problem

    Monex wrote:
    > martin_helm is right, in MySQL key is a synonym for index. If you use
    > MyISAM as storage engine the primary key is a unique index/key but if
    > you use InnoDB it is the primary key is a clustered Index/key. You
    > should keep this in mind when using InnoDB.


    What is that you think needs to be kept in mind?

  7. #7
    Join Date
    Jun 2008
    Location
    Germany
    Posts
    301

    Default Re: MySQL Relations Problem

    Hi,

    I mean the cluster index. The clustered index can have performance advantages if you use it the right way but also it could slowdown the whole table access. The problem is when the clustered index gets fragmented (due to many almost empty pages) the whole access will slow down. Then the only possibility is to recreate the table (which is done automatic by optimize tables).

    One good thing to avoid this is using an auto increment value as clustered index. This should avoid creating new pages which aren't filled completely as when it happen on random primary key inserts. Of course it is not possible to avoid this completely.

    A good resource MySQL Performance Blog for getting informations around InnoDB and MySQL.

    Hope this helps

  8. #8

    Default Re: MySQL Relations Problem

    Monex wrote:
    > ... The problem is when the clustered index gets
    > fragmented (due to many almost empty pages) the whole access will slow
    > down. ...


    Ah, thanks for that.

    > One good thing to avoid this is using an auto increment value as
    > clustered index. ...


    That's lucky, then. I always do!

    > A good resource 'MySQL Performance Blog'
    > (http://www.mysqlperformanceblog.com/) for getting informations around
    > InnoDB and MySQL.


    Thanks for the link.

    Cheers, Dave

  9. #9
    Join Date
    Apr 2009
    Location
    Annapolis, MD
    Posts
    204

    Default Re: MySQL Relations Problem

    A clustered index is preferred for primary key, auto-increment fields. This type of index stores the data on disk in the same order as your primary key. A clustered index guarantees the order of your data. Generally, you can only specify one clustered index per table but many non-clustered index. Non-clustered index are generally for columns in joins, columns in where clauses, and columns in order by and where clauses.

    In short, a clustered index designates a "physical ordering" of data. A non-clustered index specifies a "logical ordering" of data.

    In any RDBMS I have used, when you designate a primary key you are also creating an index on that table, usually of the clustered kind. I like to explicitly create the primary key by an alter table statement, that way I can specify a name for the index instead of some system-generated index name.

    ALTER TABLE employees
    ADD CONSTRAINT employees_pk PRIMARY KEY (employee_id);

  10. #10
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    1,529

    Default Re: MySQL Relations Problem

    @mostafaxxx

    What table type are you using? MyISAM?
    Technology is 'stuff that doesn't work yet.' -- Bran Ferren

Posting Permissions

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