Results 1 to 1 of 1

Thread: Database - What names should I NOT use for tables/fields/..?

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Database - What names should I NOT use for tables/fields/..?

    There are several limitations to what names you can give to your fields/tables/views/... , and big problems if you use ones that you shouldn't be using.

    Many database systems will have strict rules to what you can name items, such as:
    • "each name must start with a letter, and be [x] to [y] characters long"
    • "a name can only contain alpahnumeric characters, and spaces"
    • ...
    ..but there are other things that you need to be careful about too, which usually aren't made clear enough.

    If you use any names which break the following guidelines, you are likely to get errors (which may have 'unrelated' messages, and/or only occur in some situations!) or a noticable loss of speed when working with the data.


    Avoid spaces and other non-alphanumeric characters
    Many database systems will allow you to use spaces (and punctuation, etc) in names, but doing so can cause errors if those names aren't handled in a particular way - especially within SQL statements.

    It is recommended that you do not use these characters, for example use uppercase letters instead of spaces to split words (eg: instead of "order details" use "OrderDetails").

    If you do use names with these characters, then sooner or later you will almost certainly forget to use the special syntax (yes, I know I haven't explained what it is ), and will get problems of one kind or another (perhaps without realising it).


    Avoid specific prefixes
    Several database systems have their own special naming prefixes (for things like system tables), and using names that start with the same characters is very likely to cause slow-down, and possibly errors too.

    As a very general guide, these include things like "sys", "msys", "pg_", and "sp". Check with the documentation of your database system to see an appropriate list.


    Avoid reserved words
    This is something that comes up on the forums far too regularly (many times each week), as people dont realise that there is an issue until it is too late...

    There are several words that already have meanings within each database system (such as Name, User, Group, Index, Date, Month, Year, ..), and using them as a name can either make your queries return the wrong results (without an error), or create seemingly random errors (basically because the database system gets confused, thinking you meant a built-in function instead of your field/table/...). Unfortunately you do not get any kind of warnings when creating objects with these names, at least not in any database system I have used!

    Note that some systems will have issues if any of these words are included as part of a name with spaces around them (such as "My Date"), but then you wouldn't use a space in a name anyway, right?

    The list of reserved words is slightly different for each database system (and often by version too!), so you should check the help files to find them (there is usually at least one article called "Reserved Words"). To help you out, here are direct links to the relevant pages of the online help for some popular systems:
    (note that these first two apply to the connection technology, so are only partial lists - they should be checked in addition to the database-specific ones below)
    All databases accessed via: ODBC OLEDB

    Jet (Access/Text files/CSV/Excel/...): 4.0

    Access: 2000 2002 and 2003 2007 Note that these are for usage in Access itself, for usage from a separate program see Jet above.

    SQL Server/MSDE: 2000 2000 CE 2005 (including Express) 2005 CE (MDX in 2005)

    Oracle: PL/SQL 9i Lite 10g

    MySQL: 3.23 to 4.1 5.1 other versions

    Visual Foxpro: 7 8 9
    If you are using a version of one of the above systems that is earlier than those listed (say Access 97), then the next-newest one that is listed (so Access 2000) will probably be appropriate.. except in the case of SQL Server, in which case any version earlier than 2000 is basically a different product!

    If in any doubt, check the documentation that comes with your installation.

    If any of the above links fail, please tell me via PM


    On a final note... if you want to get fancy, you can use code to find out what the provider-specific reserved words are for the system you are connected to, eg:
    VB Code:
    1. 'Classic VB code for ADODB - open a Recordset with one column, which contains the reserved words (see warning below!)
    2. Set objRS = objConn.OpenSchema(adSchemaDBInfoKeywords)
    3.  
    4. 'VB.Net code for ADO.Net - open a DataTable with one column, which contains the reserved words (see warning below!)
    5. Dim table As DataTable = connection.GetSchema("ReservedWords");
    Note that these do not include words that are reserved by the connection technology (eg: OLEDB) itself - such as obvious ones like "Date"!

    Shameless plug/useful code.. here is a Classic VB function I wrote using this method, which checks the names of tables/fields/views in a database to see if they are reserved words - including all of the OLEDB/ODBC reserved words too!
    Last edited by si_the_geek; Feb 1st, 2009 at 05:01 PM. Reason: updated MS link which had changed

Posting Permissions

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



Click Here to Expand Forum to Full Width