Results 1 to 5 of 5
  1. #1
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100

    What's The Convention For Text Values In A PK Column?


    Hi, as per my attached image, when using text as a PK, can I use words with spaces between them or should I be using multiple words as a single text string like in the Column name? Is what I've done in my column acceptable? Thanks.

    Click image for larger version. 

Name:	Ashampoo_Snap_2016.08.31_13h38m29s_001_.png 
Views:	8 
Size:	21.8 KB 
ID:	25643

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    using text as a PK is generally considered a bad idea - if the text is changed, the link can be lost so it is importance to ensure that you enforce referential integrity in relationships and tick the cascade update related field box. Spaces or not are irrelevant - that relates to table and field naming conventions, not the contents

    If your table is a simply a lookup list to restrict population options of a field in another table there is no need for an autonumber primary key, just use the 'name' field as the primary key (although the main benefit is not that it is a primary key but that it is indexed, no duplicates)

    - then that table will be already have the information it needs so will never need to link to the lookup table in a query.
    - The cascade as mentioned above will ensure all records get corrected for typos etc.

  3. #3
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi Ajax, thanks for that. Will set my tables up accordingly. Cheers.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    one other thing - if you were to type into the caption property of the symbolname field 'symbol name', then that is what will appear at the top of the column or an associated label to the field in a form or report when it is created.

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    using text as a PK is generally considered a bad idea
    That probably deserves a little more explanation.
    - If the text can change, then yes, it should not be used as a PK
    - But if the text is non-changing, there is nothing wrong with using text as PK. As matter as fact, many professional database programs use GUIDs as PKs, and those are text!

    I myself create many PK ID numbers that are text.

    So what it really boils down to is the important thing is to use a unique static, non-changing field. That is more important than whether or not the PK is text or numeric.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  2. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  3. Replies: 5
    Last Post: 11-12-2014, 09:17 AM
  4. Replies: 10
    Last Post: 05-22-2013, 02:56 PM
  5. pulling text values into FK number column
    By REBBROWN in forum Database Design
    Replies: 2
    Last Post: 08-30-2010, 05:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums