Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,589
    Thanks
    0
    Thanked 644 Times in 634 Posts
    Quote Originally Posted by shaunthomson View Post
    Thanks makes sense. Thanks Felgall.

    Can you use three columns that are unique together as the primary key, or is that not workable?

    If a table is just one column, how can it relate to any other tables - how do you create a relationship?
    You can have three columns in a key.

    A one column table would need to have its column as part of the key in the other tables that refer to it. Its purpose would be to limit the values that can be contained in that column in the other table(s) to the values in the one column table.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  2. Users who have thanked felgall for this post:

    shaunthomson (02-12-2013)

  3. #17
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by shaunthomson View Post
    Can you use three columns that are unique together as the primary key, or is that not workable?
    *SIGH*

    Didn't we have this discussion already.

    Didn't we talk about "skipping" fields in multi-field indexes?

    Didn't I even suggest that a good primary key would be
    Code:
    CREATE TABLE djBooks (
        djid INT,
        songid INT,
        bookNum TINYINT,
        PRIMARY KEY( djid, booknum, songid )
    );
    And didn't I note the reasons for putting the fields in that order in the (yes, three field) primary key?

    I even gave the examples:
    -- a WHERE involving just DJID will use the index
    -- a WHERE involving DJID and BOOKNUM will use the index
    -- a WHERE involving all three fields will use the index
    BUT:
    -- a WHERE involving just BOOKNUM will *NOT* use the index
    -- a WHERE involving just SONGID will *NOT* use the index
    -- a WHERE involving just BOOKNUM and SONGID will *NOT* use the index
    AND:
    -- a WHERE invovling just DJID and SONGID will use the index only for the DJID.

    (All the above may not be true in other databases--SQL Server in particular--but it is a well document limitation of MySQL.)
    Last edited by Old Pedant; 02-11-2013 at 07:35 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  4. Users who have thanked Old Pedant for this post:

    shaunthomson (02-12-2013)

  5. #18
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by felgall View Post
    A one column table would need to have its column as part of the key in the other tables that refer to it. Its purpose would be to limit the values that can be contained in that column in the other table(s) to the values in the one column table.
    An reasonable is a simple lookup table. Say something like
    Code:
    CREATE TABLE AvailableColors( 
        color VARCHAR(20) PRIMARY KEY 
    );
    INSERT INTO AvailableColors 
    VALUES('Red'),('Blue'),('Black'),('White'),('Pink polka dots');
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  6. Users who have thanked Old Pedant for this post:

    shaunthomson (02-12-2013)

  7. #19
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,589
    Thanks
    0
    Thanked 644 Times in 634 Posts
    Quote Originally Posted by felgall View Post
    A one column table would need to have its column as part of the key in the other tables that refer to it.
    Just to clarify - it doesn't have to be part of the primary key (although it would be in a join table used to attach multiple values), it could be a foreign key (in a table where exactly one of the values is required)..
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  8. Users who have thanked felgall for this post:

    shaunthomson (02-12-2013)

  9. #20
    New Coder
    Join Date
    May 2012
    Posts
    89
    Thanks
    51
    Thanked 0 Times in 0 Posts
    @Felgall - thank you mate - I get it now

    @Old Pedant - you're quite right - that was a lazy question


 
Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

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