www.webdeveloper.com
Results 1 to 4 of 4

Thread: [RESOLVED] foreign keys

  1. #1
    Join Date
    Jul 2009
    Location
    UK
    Posts
    174

    resolved [RESOLVED] foreign keys

    hi

    I have the following two tables:

    Code:
    CREATE TABLE IF NOT EXISTS `categories` (
     `id` smallint not null auto_increment,
     `category` varchar(30) not null,
     PRIMARY KEY (`id`),
     UNIQUE KEY `category` (`category`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    Code:
    CREATE TABLE IF NOT EXISTS `pages` (
     `id` mediumint unsigned not null auto_increment,
     `category_id` smallint unsigned not null,
     `title` varchar(100) not null,
     `description` tinytext not null,
     `content` longtext not null,
     `date_created` timestamp not null default current_timestamp,
     PRIMARY KEY (`id`),
     KEY `category_id` (`category_id`),
     KEY `creation_date` (`date_created`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    and are allegedly in a one to many relationship with each other. A category can have many pages associated with it, and one page belongs to one category.

    The example is from a book, so it's cosha. It's just that i'm used to seeing foreign keys that link tables where the fields are named the same. So, continuing in this vein; 'categories' would link to 'pages' via 'category_id' say. Hence I'm expecting to see two 'category_id' fields, one in categories and one in pages, with the one in categories being a primary key and the one in pages a foreign one.

    Or is it that the two tables (fields) are linked by data type i.e. 'id' in categories being smallint and 'category_id' in pages also being smallint? This would mean naming conventions don't really matter, it's just a matter of preference.

    Cam someone clarify.

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    As I understand it, the PK and FK don't have to be named the same, they just need to be the same datatype (PK int must relate to FK int; PK varchar(x) must relate to FK varchar(x); etc.)

    Can anyone clarify?

  3. #3
    Join Date
    Jul 2009
    Location
    UK
    Posts
    174
    Quote Originally Posted by WolfShade View Post
    As I understand it, the PK and FK don't have to be named the same, they just need to be the same datatype (PK int must relate to FK int; PK varchar(x) must relate to FK varchar(x); etc.)

    Can anyone clarify?
    I think you're right. I found this to help: http://dev.mysql.com/doc/refman/5.1/...nstraints.html

    thanks

  4. #4
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Yeah, the naming conventions are really more for personal comfort/ease of design and use.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles