Introduction
The main purpose for adopting a naming convention for database objects is so that you and others can easily identify the type and purpose of all objects contained in the database.
Naming convention in SQL
In computer programming, a naming convention is a set of rules for choosing the character sequence to be used for identifiers that denote variables, types, functions, and other entities in source code and documentation.
A well-defined object name defines all the information about itself, like the type of object, the work of the object, and the Name of the Table (View) on which it will work.
Benefits of a good naming convention
The following are the benefits of a good naming convention:
- Provides a specific standard for naming all objects so they look the same or are related to the same group.
- Reduces the effort needed to read and understand source code after a long interval of time.
- Makes clear the action name of any object that will it did.
- Enhances clarity in case of potential ambiguity.
- Provides a better understating to new people reading all objects for the first time.
- Sorts all the objects so objects that are similar remain in a sequence it reduces the time to determine any specific object in a database.
In a programming language, we use many naming conventions, like PascalCase, camelCase, under_score, and so on. Which naming convention is used depends on each person and organization.
This article explains naming conventions for all objects that are mainly used in
SQL.
Table in SQL
Tables are used for storing data in the database. The naming convention for a table name is as follows:
- Each table name should have a “tbl” prefix.
- The next word after the “tbl” prefix should be the table name.
- The first letter of the table name should be capitalized.
- The last word of the table name should end with the character "s" (or "es") to indicate plural.
- If a table name contains more than one word then the first letter of each word should be capitalized.
Examples
- tblEmployees
- tblProducts
- tblStudents
- tblEmployeeDetails
Views in SQL
Views are like a virtual table based on the result set of SQL statements. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. The naming convention for the table name is as in the following:
- Each View name should follow the syntax "vw_<ResultSet>".
- Each view name should have a “vw_” prefix.
- The first letter of the result set should be capitalized.
- The last word of the result set should end with the character "s" (or "es") to indicate plural.
- If a result set contains more than one word then the first letter of each word should be capitalized.
Examples
- vw_ProductDetails
- vw_EmployeeDetails
- vw_OrderDetails
Primary Key Constraints
A primary key uniquely identifies each record in a table. The naming convention for a primary key is as in the following.
- A Primary Key name should use the syntax "PK_<Tablename>".
- Each Primary Key name should have a “PK_” prefix.
- The first letter of a table name should be capitalized.
- The last word of a table name should end with the character "s" (or "es") to indicate plural.
- If a table name contains more than one word then the first letter of each word should be capitalized.
Examples
- PK_Employees
- PK_OrderDetails
- PK_ProductDetails
Foreign Key Constraints
A Foreign Key is a field in the database table that is the primary key in another table. The purpose of the foreign key is to ensure the referential integrity of the data.
Naming convention for Foreign Key
- A Foreign Key name should use the syntax "FK_<TargetTable>_<SourceTable>".
Here: TargetTable is a table name that holds the Foreign Key and the Source Table is the table name that holds the Primary Key.
- Each Foreign Key Name should have an “FK_” prefix.
- The first letter of both table names should be capitalized.
- Both table names should end with the character "s" (or "es") to indicate plural.
Examples
- FK_Employees_Projects
- FK_Students_ContactNumbers
- FK_Orders_Details
Default Constraints
In the default constraint, a column has a default value when any value is not provided. The naming convention for the default constraint is:
- Default constraint should use the syntax “DF_<TableName>_<ColumnName>.
- Each Default Constraint name should have a “DF_” prefix.
- The first letter of both TableName and ColumnName should be a capital.
- The TableName should end with the letter "s" (or "es") to indicate plural.
Example
- DF_Employees_EmpName
- DF_Employees_EmpSalary
- DF_OrderDetails_OrderNumber
Unique Constraint
A Unique Constraint uniquely identifies each record in a database table. The unique constraint prevents two records from having identical values in a specific column. The naming convention for a unique constraint is:
- The unique constraint should use the syntax “UQ_<TableName>_<ColumnName(s)>.
- Each Unique Constraint name should have a “UQ_” prefix.
- The first letter of both TableName and ColumnName should be capitalized.
- TableName should end with the letter "s" (or "es") to indicate plural.
- If the unique key contains more than one column then each column name is separated with an underscore (_).
Examples
- UQ_Employees_EmpId_EmployeeName
- UQ_OrderDetails_OrderNumber
Check Constraint
A Check Constraints provides a condition for a column that must be followed by each row. The naming convention for a check constraint is:
- The check constraint should use the syntax “CHK_<TableName>_<ColumnName>.
- Each Check Constraint name should have a “CHK_” prefix.
- The first letter of both TableName and ColumnName should be capitalized.
- TableName should end with the character "s" (or "es") to indicate plural.
Examples
- CHK_Employees_EmpSalary
- CHK_Employees_Age
- CHK_OrderDetails_OrderPrice
User-defined functions
A user-defined function is a set of SQL statements that accept input, perform execution and return a result. The naming convention for a user-defined function is:
- A user-defined function should use the syntax “fn_<ActionName>”.
- Each user-defined function name has an “fn_” prefix.
- The first letter of the Action name should be capitalized.
- If an action name contains more than a single word then the first character of each word should be capitalized.
Examples
- fn_CalulateProfit
- fn_CalculateTotal
User-Defined Stored Procedure
A Stored Procedure is a precompiled set of Structured Query Language (SQL) statements that can be shared by a number of programs.
The naming convention for a user-defined Stored Procedure is
- Each user-defined Stored Procedure should use the syntax “usp_<TableName><ActionName> “.
- Each user-defined Stored Procedure name should have a “usp_” prefix.
- Each action name and table name should start with a capital letter.
- The table name should end with the letter "s" (or "es") to indicate plural.
Example
- usp_Employees_Insert
- usp_OrderDetails_Delete
Indexes
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. The naming convention for indexes is:
- Each index name should use the syntax IX_<TableName>_<Column(s)>.
- Each index name should have the prefix “IX_”.
- Each table name and column name should start with a capital letter.
- The table name should end with the letter "s" (or "es") to indicate plural.
- If the index name contains more than one word then the first character of each word should be a capital and separated with an underscore (_).
Example
- IX_Employees_EmpId
- IX_OrderDetails_OrderNo_OrderDate
Triggers in SQL Server
Triggers are types of Stored Procedures that are invoked automatically (implicitly) when a DML command is executed. The naming convention for triggers is:
- Each trigger name should use the syntax “TR_<TableName>_<ActionName>.
- Each trigger name should have the prefix “TR_”.
- Each table name and action name should start with a capital letter.
- The table name should end with the letter "s" (or "es") to indicate plural.
Examples
1. TR_Employees_ AfterInsert
2. TR_OrderDetails_AfterUpdate
3. TR_Employees_InstedOfDelete
Please ignore the following during the naming convention
- Do not use predefined SQL keywords for any object name.
- Ignore spaces between the names of any object using brackets ([ ]) if necessary.
- The naming pattern of an object should be the same (don't use a different naming pattern for the same type of object).
- Ignore the use of a numeric or special symbol in the naming, like (tbl_Employeee3, tbl_Employee@Master).
Conclusion
In this article, we learned about Naming Conventions with examples in SQL.
Shatrudhan KumarPosted Oct 19, 2019, 9:32 AM
Nice explanation
Sivakumar KonetiPosted Jul 11, 2019, 3:19 AM
Good Article
Anoop APosted Jun 1, 2019, 12:41 AM
This is so outdated! Table should be prefixed by `tbl` is nonsense. A table should be named Singular and not plural. What is the need of `s` at the end of every thing. People should look for naming conventions at the site of the Language or it's manufacturer.
Jaipal ReddyPosted Jun 22, 2015, 12:37 AM
good one
Debasis SahaPosted Jun 11, 2015, 3:56 PM
Good one
Saroj Kumar SahuPosted Jun 11, 2015, 8:04 AM
Good one..
Gakenh01Posted Jun 11, 2015, 7:51 AM
Nice, I agree with everything except 1 thing and that is Table. I have never seen anyone use the tbl suffix for a table in a professional environment, only academia and low level books. IMHO, you should never use tbl for naming conventions of a table because everyone already knows it is a table. It is a quick way to have a contract end.
Rahul PrajapatPosted Jun 10, 2015, 10:09 PM
thanks @Pankaj sir for this helpful information
Santhakumar MunuswamyPosted Jun 10, 2015, 3:09 PM
Thanks for sharing
Miguel Angel Ruiz AlvarezPosted Jun 10, 2015, 9:55 AM
Very helpful tips, thanks for sharing Pankaj Kumar Choudhary!
RakeshPosted Jun 10, 2015, 12:53 AM
Good information
Amol SarkatePosted Jun 10, 2015, 12:46 AM
Nice at the time of development or implement project We Need to follow these stranded its mandatory