Is it ok for fields to have the same name as SQL types?

Question

Can I name fields and tables with the same names as SQL data types? For example, would the following SQL definition work in MySQL?

CREATE TABLE if not exists TABLE1 (
id INT PRIMARY KEY,
text TEXT)

Furthermore, can I give fields the name of other tables?

CREATE TABLE if not exists TABLE2 (
id INT PRIMARY KEY,
table1 INT,
FOREIGN KEY (table1) REFERENCES table1(id))

Would this work?

@TeymourAldridge except your link is for SQL Server, and the OP is asking about MySQL — LittleBobbyTables - Au Revoir, Jan 07 '20 at 18:04
The simple approach would be to NOT do that so it can never be an issue — RiggsFolly, Jan 07 '20 at 18:06

score 1 · Accepted Answer · answered Jan 07 '20 at 18:03

MySQL has a concept of keywords, and reserved keywords.

You can use keywords as identifiers for tables, columns, etc., without quoting them.

mysql> CREATE TABLE if not exists TABLE1 (
    -> id int PRIMARY KEY,
    -> text TEXT);
Query OK, 0 rows affected (0.02 sec)

You can use reserved keywords as identifiers as well, but you have to quote them.

mysql> CREATE TABLE if not exists TABLE1 (
    -> id int PRIMARY KEY,
    -> order TEXT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'order TEXT)'

Quote the reserved keyword and then you can do it:

mysql> CREATE TABLE if not exists TABLE1 (
    -> id int PRIMARY KEY,
    -> `order` TEXT);
Query OK, 0 rows affected (0.01 sec)

How do you know what's a keyword and what's a reserved keyword?

Use this reference documentation: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Thank you. Would I also be able to give fields the same name as a table? — Programmer S, Jan 07 '20 at 18:28

Is it ok for fields to have the same name as SQL types?

1 Answers1