In this Tutorial will learn about MySQL table and column Naming Guidelines for database Objects. This is also known as MySQL naming convention or MySQL naming standards. In my last post MySql Tutorial for Beginners Part 4 – CREATE Statement i wrote a little about Create statement and Naming standards for MySQL. One of the reader asked me if i can write little more about MySQL Naming Guidelines. So here we go…
MySQL Naming Guidelines -MySQL Tutorial Part 5
Here are some rules and guidelines to follow while coding MySQL for Standards and Naming Conventions:
1.At first we need to remember that we can’t start working with a database until we decide what king of data we will store in database. For example name, email address, password. And which one will be our Primary key or foreign key. Now You are thinking what is primary key and what is foreign key. here is a little details about that:
a. Primary Key:In a MySQL database table PRIMARY KEY constrain uniquely identifies each records.There are some rules to keep in mind before creating a primary key.Primary keys must have unique values. Primary key column cannot contain NULL values.
b. Foreign Key : In our first MySQL tutorial We talked about RDBMS (relational database systems). Foreign keys, lets you cross-reference related data across database tables. For example a foreign key is one database table point which cross-reference primary key in another table.
2. Each table should have a primary key, and each table can have only ONE primary key.
3. Before we create a Table we need to decide what type of data we will use in our columns. Mainly MySQL have three kind of data type ..
a. Numeric types: These types includes numeric data types Such as INTEGER, SMALLINT, DECIMAL ETC.
b. Date and time types: These type represents values that are DATE, TIME, YEAR. ETC.
c. String or character types: This data type represents character type data for database. such as CHAR, VARCHAR, BINARY,TEXT ETC.
4. Table names can only consist of numbers letters or underscores. Always treat your database or table as case-sensitive. This is not always the case however it is best practice to just assume that it is case-sensitive. If you maintain this practice you will not run into any confusion later for both naming tables and when you’re accessing these data.
5. Name your table or column according to the data it will store. For example a column named Last_Name will store Last name in that column.
6. One of the important rule in database cannot store tables with the same name and the table cannot have two columns with match with the existing keywords in MySQL. So you wouldn’t have a database called create or table called select.
7. Another rule is name cannot be longer than 64 characters.