SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)

    foreign & primary keys

    I'm a bit confused about foreign & primary (or maybe I just confused myself with circular thinking).

    So, I'm trying to make a set of tables, as listed, followed by the first column name of "UID":



    maintable - UID
    storage - UID
    format - UID
    record_status - UID

    Yes, there are other columns following column "UID", but I'm trying to understand how foreign & primary keys would be set up in this case.

    Anyway, maintable UID will hold the unique ID number of an entry. The UID fields in the other tables will reference maintable UID. How would I set this up in MySQL? This would be a one-to-many relationship. Would the foreign key be maintable UID? Or would maintable UID be the primary key?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'm not quite sure what your application does, but let me give you another example. assume the following tables:
    Code:
    CREATE TABLE `class` (
     `id` int(10) unsigned NOT NULL auto_increment,
     `name` varchar(30) NOT NULL default '',
     `teacher_id` int(10) unsigned NOT NULL default '0',
     PRIMARY KEY  (`id`)
    ) TYPE=MyISAM
    
    CREATE TABLE `teacher` (
     `id` int(10) unsigned NOT NULL auto_increment,
     `name` varchar(30) NOT NULL default '',
     PRIMARY KEY  (`id`)
    ) TYPE=MyISAM
    
    CREATE TABLE `student` (
     `id` int(10) unsigned NOT NULL auto_increment,
     `name` varchar(30) NOT NULL default '',
     PRIMARY KEY  (`id`)
    ) TYPE=MyISAM
    
    CREATE TABLE `registration` (
     `class_id` int(11) NOT NULL default '0',
     `student_id` int(11) NOT NULL default '0',
     PRIMARY KEY  (`class_id`,`student_id`)
    ) TYPE=MyISAM
    in this example, class.id and teacher.id and student.id are primary keys. (registration.class_id, registration.student_id) is a compound primary key.

    class.teacher_id and registration.class_id and registration.student_id are foreign keys.

  3. #3
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    This is part of the reason I'm confused...what's with all that extra code?

    What I'm looking for is one field to be referenced by other fields.

    Let me elaborate a bit on my database...

    Table | column | datatype
    ---------------------------------
    maintable | UID | unique ID
    maintalbe | contact_person | VARCHAR(50)
    storage | UID | REFERENCES MAINTABLE(UID)
    storage | room_num | TINYINT
    format | UID | REFERENCES MAINTABLE(UID)
    format | format_map | BOOLEAN
    format | format_drawing | BOOLEAN
    record_status | UID | REFERENCES MAINTABLE(UID)
    record_status | status | VARCHAR(20)

    There are a lot more fields than that I need to use...I'm just trying to keep it simple here.

    [edit]: Now that I think about it...

    All together I have about 80 fields...would it be more effecient to have everything in a single table, or seperate them into different tables like I have here?

  4. #4
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Foreign keys are used to reference data in another table. i.e. link the two tables.

    Primary key is used as an identifier for a certain table.

    Not sure what your overall aim is but hope that helps a little.

    If its a lot of data then you're always better off to use multiple tables - break the data into separate sections. e.g. a table for format, a table for storage, etc.

    Set up a primary key for each. e.g. format_id, storage_id.

  5. #5
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    Basically, I have one unique number for the 80 or so values I need to put in the database. There will probably be several hundred unique numbers, thus several hundred entries, each keeping about 80 values.

    now, with the tables, for a single entry with the 80 values (split amongst the tables), they will all be referencing a single unique ID because all the data in the tables come under that unique ID.

    Or, should I forget about primary/foreign keys, and just use a unique ID in MAINTABLE, and copy that ID to the other UID fields in the other tables?

    Make sense or am I still losing you?

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    there's a flaw in your table structure. it should be like this:
    Code:
    Table         | column         | datatype
    ----------------------------------------------------------
    maintable     | UID            | unique ID
    maintable     | contact_person | VARCHAR(50)
    storage       | UID            | unique ID
    storage       | maintable_UID  | REFERENCES MAINTABLE(UID)
    storage       | room_num       | TINYINT
    format        | UID            | unique ID
    format        | mailtable_UID  | REFERENCES MAINTABLE(UID)
    format        | format_map     | BOOLEAN
    format        | format_drawing | BOOLEAN
    record_status | UID            | unique ID
    record_status | maintable_UID  | REFERENCES MAINTABLE(UID)
    record_status | status         | VARCHAR(20)

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Force Flow
    would it be more effecient to have everything in a single table, or seperate them into different tables like I have here?

    Or, should I forget about primary/foreign keys, and just use a unique ID in MAINTABLE, and copy that ID to the other UID fields in the other tables?
    NO and NO! by doing that, you completely throw away all of the advantages of a relational database. if this is your direction, you might as well just use a CSV file.

  8. #8
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    So with the table, I *have* to have a unique ID in each table?

    I only need the unique ID (labeled as UID in the columns) in the MAINTABLE. The entries in the other tables are supposed to refer to that unique ID.

    Or does it not work like that?

  9. #9
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    Location
    United States
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Force Flow
    So with the table, I *have* to have a unique ID in each table?

    I only need the unique ID (labeled as UID in the columns) in the MAINTABLE. The entries in the other tables are supposed to refer to that unique ID.

    Or does it not work like that?
    I would suggest that you work out your database by hand, using good old pen and paper first. Figure out what your entities are, then decide on what the relations between them are, and then figuring out where you need foreign keys becomes a simple matter of mapping your relational model to SQL.

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Force Flow
    So with the table, I *have* to have a unique ID in each table?

    I only need the unique ID (labeled as UID in the columns) in the MAINTABLE. The entries in the other tables are supposed to refer to that unique ID.

    Or does it not work like that?
    you don't have to, but it makes certain management tasks easier, like deleting one specific row.

  11. #11
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    ok

    I think I'm starting to understand this a bit better now.

    I just have one question on the SQL code you provided earlier....what is TYPE=MyISAM for?

  12. #12
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    Location
    United States
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Force Flow
    ok

    I think I'm starting to understand this a bit better now.

    I just have one question on the SQL code you provided earlier....what is TYPE=MyISAM for?
    This is the type of storage engine that this particular table is using.

  13. #13
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    Code:
    `id` int(10) unsigned NOT NULL auto_increment,
    What does the "unsigned" mean?

  14. #14
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it means no negative numbers. since mysql only issues positive integers for auto_increment columns, i tell it to not allow negative numbers.

  15. #15
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by longneck
    there's a flaw in your table structure. it should be like this:
    Code:
    Table         | column         | datatype
    ----------------------------------------------------------
    maintable     | UID            | unique ID
    maintable     | contact_person | VARCHAR(50)
    storage       | UID            | unique ID
    storage       | maintable_UID  | REFERENCES MAINTABLE(UID)
    storage       | room_num       | TINYINT
    format        | UID            | unique ID
    format        | mailtable_UID  | REFERENCES MAINTABLE(UID)
    format        | format_map     | BOOLEAN
    format        | format_drawing | BOOLEAN
    record_status | UID            | unique ID
    record_status | maintable_UID  | REFERENCES MAINTABLE(UID)
    record_status | status         | VARCHAR(20)
    Ok, which would be the primary key(s), and which would the foreign key(s)?

    Would the column that everything else is referencing be the primary or foreign key?

  16. #16
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the ones that are marked "REFERENCES" are the FK's. the ones that are marked "unique ID" are the PK's.

    primary keys UNIQUELY identify records WITHIN a table. foreign keys reference primary keys.

  17. #17
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    ok, I attemped what you suggested with one field referencing a primary key, plus another unique ID number column, but I got an error message. What would be the correct syntax? Also, what is the difference between "index" and "primary key"

    Code:
    CREATE TABLE `maintable` (
    `UID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
    PRIMARY KEY ( `UID` ) 
    );
    
    CREATE TABLE `storage` (
    `UID_ref` INT( 10 ) UNSIGNED NOT NULL ,
    `UID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
    FOREIGN KEY (UID_ref) REFERENCES maintable(UID)
    );
    #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

  18. #18
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    CREATE TABLE `storage` (
      `UID_ref` INT( 10 ) UNSIGNED NOT NULL ,
      `UID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
      FOREIGN KEY (UID_ref) REFERENCES maintable(UID),
      PRIMARY KEY (`UID')
    );

  19. #19
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, time for you to buy a mysql book or two. here are some of my reccomendations:

    http://www.sitepoint.com/books/phpmysql1/
    http://www.oreilly.com/catalog/webdbapps2/

  20. #20
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,524
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by longneck
    Code:
    CREATE TABLE `storage` (
      `UID_ref` INT( 10 ) UNSIGNED NOT NULL ,
      `UID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
      FOREIGN KEY (UID_ref) REFERENCES maintable(UID),
      PRIMARY KEY (`UID')
    );
    Thanks, that did the trick


Bookmarks

Posting Permissions

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