Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    general rule for max pk field length?

    Is there a field length I should not exceed for my primary key field length if I want to be compatible with most DBMS?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the datatype of this key that you have in mind?

    when people speak of "field length" it usually means a VARCHAR column, but i thought i'd ask to make sure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    104
    I'm drawing the ERD in visio, which uses Access perspective. Char. type is TEXT, though VARCHAR would work as well. Does the answer differ between the two?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What's the maximum length? And the RDBMS?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2009
    Posts
    104
    Since I'm building a generic model (I don't know what RDBMS I'll employ, though I'm using Access for now), I'm looking for a rule-of-thumb figure. I'm trying to avoid having to change field names later, depending upon what RDBMS I settle on.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rule of thumb: maximum 255 bytes

    any more than that and i would question the viability of this column as a key

    and you would ~never~ need to adjust a column name based on the RDBMS chosen, assuming you do not use special characters in the name


    say, i just realized something -- are you talking about the length of the column, or the length of the name of the column???

    Last edited by r937; 02-15-09 at 21:29.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2009
    Posts
    104

    both

    Originally, I was interested in the column length. I could imagine using a primary key with say 10 ANSI characters, so c. 20 bytes. (2 bytes per char. with ANSI chars, right?).

    However, what is the max length of a field/column name?

    I see now how I confused things inserting "field names." I did mean field lengths. I was thinking about two things as I was writing. Sorry 'bout that.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    2 bytes per character only in some character encodings (unicode?)

    maximum length of column for viability as a key is (in my opinion) 255 bytes, preferably substantially less

    maximum length of column name varies by database system (access 64, sql server 128, as little as 6 characters for a couple of older ones) but for practical purposes shouldn't be any longer than ~40 characters
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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