63

Is it ok to name my database tables that are already keywords? For my case, I am trying to name the table that will hold my users. I've named it User but it is showing up as pink in SQL Server Management Studio so I am assuming its an existing System Table or Keyword. Thanks for your advice.

Official list of reserved keywords: Reserved Keywords (Transact-SQL)

demp
  • 12,665
  • 2
  • 23
  • 17
EverTheLearner
  • 7,040
  • 16
  • 57
  • 72
  • For mysql: Here's the answer: SELECT * FROM 'keys' works - put the table name in single quotes in my phpmyadmin. Therefore, it is ok. (but not recommended). – ssaltman Nov 30 '15 at 01:54

11 Answers11

130

repeat this three times:

DO NOT DO IT, I WILL NOT USE RESERVED WORDS!

you'll thank me!

racer x
  • 1,502
  • 1
  • 9
  • 3
  • Oh yes. Very strange things happen when you try to query such table/field with a MS Query (which is the querying tool for MS Office). It will NOT work despite you put brackets on Excel side. You will rename the table/field eventually. – GSerg Mar 29 '09 at 23:54
  • 2
    Why torture people that have maintain your code later on or even yourself? – ojblass Mar 30 '09 at 00:03
  • 15
    Respectfully disagree. It's good habit to bracket your table names anyways. And every SQL tool I've used, both MSFT and third party, does this automatically. So in practice, it doesn't make a big difference. – Portman Mar 30 '09 at 01:20
  • 1
    sometimes you really need to use it, like if your entity name is User, changing to another table name makes the project dangerous. – mauris Jul 15 '10 at 07:02
  • 31
    Unfortunately, so many of the reserved keywords have practical every day uses (ie. `user`, `file`, `text`, `view`) – puk Apr 30 '12 at 23:04
  • Plus a thousand! But can you make that text much, much larger? – Nick Vaccaro Jun 22 '12 at 20:08
  • 6
    Let's also not forget that reserved words change from release to release. Back in the SQL Server 7 days, people used the table name 'Function' in a lot of code only to find that it broke when they upgraded the database to SQL 2000 and FUNCTION became a reserved word. Long story short: in production code, always escape your object names! – Dave Markle Mar 14 '13 at 16:35
  • 5
    Fine to say that, but it's not always you that created the table. Given the table exists, and you can't change it - an answer like this doesn't really help. – vikingsteve Apr 30 '15 at 08:36
83

You can create tables with the same name as keywords. If you "quote" the table name it should work. The default quotes in SQL server are square brackets: []

CREATE TABLE [dbo].[user](
    [id] [bigint] NOT NULL,
    [name] [varchar](20) NOT NULL
) ON [PRIMARY]
Andriy M
  • 76,112
  • 17
  • 94
  • 154
Andy White
  • 86,444
  • 48
  • 176
  • 211
  • 3
    You should always quote all field names. You never know what may become a reserved word in a future database. It doesn't ever do any harm. – rjmunro Sep 17 '13 at 11:52
  • Thanks for this! I already had column names that were reserved keywords and I was getting tired of the crummy "answers" to this question (well, my question was more about accessing than creating) that were basically "don't". – ShaneTheKing Sep 16 '14 at 11:55
  • You nailed it! thank you – GETah Dec 22 '15 at 06:21
12

Yes, it is ok. In your queries, you can put [ and ] around your table name so that SQL Server knows you are referring to a table - i.e. 

CREATE TABLE [User] ...
SELECT * FROM [User]
Jakob Christensen
  • 14,826
  • 2
  • 51
  • 81
9

You can use [User] to do this. If at all possible use a table name that doesn't conflict with a keyword, to avoid confusion and bugs.

brian-brazil
  • 31,678
  • 6
  • 93
  • 86
8

Use the 'single quote' for Strings, and "double quote" for column names.

Example:

INSERT INTO AccountMovement
("Date", Info)
VALUES
('2012/03/17', 'aa'),
('2012/03/17', 'bb'),
('2012/03/17', 'cc'),
('2012/03/17', 'dd')
Eduardo Cuomo
  • 17,828
  • 6
  • 117
  • 94
8

For MS Query, I have found the double quotes works perfectly in the Query.

select T1."Reference"
from MyTable T1
Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
Viviane Rosa
  • 81
  • 1
  • 1
6

I sit in the camp that says that table names should be plural, so in your case that would be Users.

I like this convention as it makes sense to me. You have a collection of users so call your table that. Further down stream if you pull out an indvidual row that could then populate an object named User.

If your convention dictates use of singular for table names use something different e.g.: Member, Client etc.

Also see RacerX's answer!

As previously mentioned it is tecnically OK if you [Braket] the name.

Jon P
  • 19,442
  • 8
  • 49
  • 72
3

As mentioned, you can do it by quoting the name. Of course, you'll also have to quote the name anytime you reference it - trust me, it gets old real quick.

As an aside, just because SSMS syntax colors the word doesn't necessarily mean it's a reserved word. Sql can be annoying like that. ;)

Mark Brackett
  • 84,552
  • 17
  • 108
  • 152
3

Basic rule for table and column names (or better object names in general):

Don't use anything the same as, or even similar to, a reserved word. Only use A-Za-z0-9 and underscore. Especially don't use spaces. Only use names that don't require escaping, and then don't use escaping as a perpetual test.

You, and everyone who works with you, or will ever work on your code, don't need the aggravation.

dkretz
  • 37,399
  • 13
  • 80
  • 138
3

As everyone else has said don't do this; however, I was in the same boat. I have a rule that says all my tables are stored in singular form. Organization not Organizations, Asset not Assets a PartsCatalog has many Part etc.

Well I have a User table so what do I call it? I ended up escaping it [User]. Now I regret that decision because I am always forgetting to escape the table; however, I've not come up with a better name yet: Member is the leading candidate.

JoshBerke
  • 66,142
  • 25
  • 126
  • 164
2

Not a good idea - for various good reasons

MORE REASONS WHY NOT 1) The obvious possible conflict with reserved names 2) If in two years you want to do a global replace in your code of say "user" in a form field or anywhere you are screwed when using generic names 3) If you need to search for occasions that use "user" in your code - you know where that goes (we have over a million lines of code, it would kill us).

WHAT WE DID 1) each table name has a unique start like O_nnn for objects F_nnn for finance data... we applied the same to fields like opp_created for opportunity was created at date, SUSR_RID for referencing to a user ID within a sales function versus OPUSR_RID an operational reference to a user... 2) Other than the prefix we use as obvious as possible names such as O_FlightArrivalTime and not O_FltAT. Today's databases show no performance degradation with longer names. 3) Now, when using OF_FlightArrivalTime as a Formfield name you find the association easily but a global search for O_F... would only find either the DB field, a search for OF_F... the form field and _F.... both.

Axel Schultze
  • 121
  • 7