Do all table names have to have capital letters?

[axelkaban]

Question

Do all table names have to have capital letters?

Answer

Many developers capitalize a table name for the sake of differentiating it from the column names, although the most common convention is to have table and column names in all lowercase to have a more legible contrast on a written query but then again it is also possible to do the contrary and have table and column names all uppercase and keywords in lower case, let’s see their comparison:

SELECT * FROM table WHERE table_name = 'My table';

/*it can be with capitalized table name*/

SELECT * FROM Table WHERE table_name = 'My table';

/*or it can have all uppercase and keywords lowercase*/

select * from TABLE where TABLE_NAME = 'My table';

As we may see, it is much to preference and also sometimes the tools used, SQLite does not have restrictions that I am aware of on this subject, yet MySQL does have some. The main thing to keep in mind on our decision is to make sure that once created we do not try to go back an forth on how we write our queries, simply because SQL is case sensitive, so if we created a table called My_First_Table, and we try to SELECT * FROM my_first_table; we will find that table does not exist. It is the SQL-92 standard that specifies all identifiers and keywords are case-insensitive.

So if we have the chance to decide, let’s stick to it, otherwise, most commonly we will work with code already created by others, so we will need to respect their preference on how it is written.

[masakudamatsu]

@axelkaban

SQL is case sensitive, so if we created a table called My_First_Table , and we try to SELECT * FROM my_first_table; we will find that table does not exist. It is the SQL-92 standard that specifies all identifiers and keywords are case-insensitive.

I’m a bit confused about whether SQL is case-sensitive or case-insensitive. Am I correct to say that SQL keywords are case-insensitive, but table names and column names are case-sensitive?

[midlindner]

Considering that these queries both work and produce the same output:

sElEct id, name, category, t_or_f 
froM fake_apps
wheRE t_or_f = 4 > 200
ordeR bY name;

sElEct id, name, category, t_or_f 
froM FakE_AppS
wheRE t_or_f = 4 > 200
ordeR bY name;

The actual table name is fake_apps, so it doesn’t appear that the table name is case sensitive.

Perhaps someone with far more experience than me like @factoradic could weigh in?

P.S.
This works as well:

sElEct Id, nAme, caTegory, t_Or_f 
froM FakE_AppS
wheRE t_or_f = 4 > 200
ordeR bY name;

So, the column names don’t appear to be case-sensitive either.

[factoradic]

Thanks, @midlindner for such a welcoming invitation

---

It’s quite difficult to answer this question. SQL is a standardized language, but each DBMS vendor can freely interpret the standard, extend it or simply ignore some pieces of the standard. That is why there are so many differences between SQLite and MySQL, between MySQL and MS SQL, etc.

I would like to focus on the SQLite - this is what codecademy uses in this course. In SQLite, all identifiers are case insensitive. And this is proven, to some extent, by the brilliant detective work by Tod.

I would quote the latest SQL standard, but unfortunately, I don’t have access to it. But usually, all the identifiers (table names, column names etc.) are case insensitive when they are not quoted and case sensitive when they are. But in SQLite quotation marks do not impact case sensitiveness:

CREATE TABLE test_table (id INTEGER);		-- ok
CREATE TABLE Test_Table (id INTEGER);		-- fail
CREATE TABLE "Test_Table" (id INTEGER);		-- fail
CREATE TABLE "test_table" (id INTEGER);		-- fail
ALTER TABLE test_table ADD "ID" INTEGER;	-- fail
ALTER TABLE test_table ADD "iD" INTEGER;	-- fail

Also, the operating system itself might make a difference when it comes to case sensitivity. Some versions of MySQL act differently when they are deployed on Linux and on Windows

---

@axelkaban

It is the SQL-92 standard that specifies all identifiers and keywords are case-insensitive.

92 stands for 1992. I wasn’t even born then… Please remember that if someone will try to substantiate some statements by quoting standards from 20 years ago it is best to simply avoid this person in your professional career. SQL is a living language, with a changing standard. SQL standard was revised numerous times since '92.

---

As a developer, you will work with many different databases. It does not make sense to rely on the case sensitivity. Trust me, it never does make sense to create columns id and ID in the same table, even when the database allows it.

[midlindner]

Thanks for the explanation!

[masakudamatsu]

Thank you (again!) for your response! It’s very helpful.

@factoradic

It does not make sense to rely on the case sensitivity. Trust me, it never does make sense to create columns id and ID in the same table, even when the database allows it.

I fully agree with you. But I also wanted to know whether SQLite is case-sensitive or not, for the sake of error-handling. If it’s not case-sensitive, then one possible source of error (i.e. a wrong case) is ruled out.

[factoradic]

You’re very welcome (again!) I really appreciate your questions, if this will save some other users from confusion - that’s the time well spent.

@masakudamatsu

But I also wanted to know whether SQLite is case-sensitive or not, for the sake of error-handling.

Makes sense!

[lipalex1]

Hi!
Thanks for taking the time to coming back on this.

Does this mean that as long as I am building the original database in SQLite I can keep writing the keywords in lowercase? Because that uppercase keyword thingy feels kinda tedious with time and as keywords are reserved anyway…?

[thepragdev]

I really appreciate how you correct every wrongly explained statement posted by the thread owner. Sometimes, people are tempting to keep showing incorrect knowledge, there’s still someone like you to fix it.