Naming Conventions in SQL

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. 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 itsself, like type of object, work of object, Name of Table (View) on which it will work.

Benefits

The following are the benefits of a good naming convention:

1. Provides a specific standard for naming all objects so they look the same or related to the same group.
   
2. Reduces the effort needed to read and understand source code after a long interval of time.
   
3. Makes clear the action name of any object that will it done.
   
4. Enhances the clarity in case of potential ambiguity.
   
5. Provides a better understating to new people reading all objects for the first time.
   
6. Sorts all the objects so objects that are similar remain in a sequence so 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

Tables are used for storing data in the database. The naming convention for a table name are as follows:

1. Each table name should have a “tbl” prefix.
2. The next word after the “tbl” prefix should be the table name.
3. The first letter of the table name should be capitalized.
4. The last word of the table name should be end with the character "s" (or "es") to indicate plural.
5. If a table name contains more than one word then the first letter of each word should be capitalized.

Examples

1. tblEmployees
2. tblProducts
3. tblStudents
4. tblEmployeeDetails

Views

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:

1. Each View name should follow the syntax "vw_<ResultSet>".
2. Each view name should have a “vw_” prefix.
3. The first letter of the result set should be capitalized.
4. The last word of the result set should be end with the character "s" (or "es") to indicate plural.
5. If a result set contains more than one word then the first letter of each word should be capitalized.

Examples

1. vw_ProductDetails
2. vw_EmployeeDetails
3. 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:

1. A Primary Key name should use the syntax "PK_<Tablename>".
2. Each Primary Key name should have a “PK_” prefix.
3. The first letter of a table name should be capitalized.
4. The last word of a table name should be end with the character "s" (or "es") to indicate plural.
5. If a table name contains more than one word then the first letter of each word should be capitalized.

Examples

1. PK_Employees
2. PK_OrderDetails
3. 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 referential integrity of the data.

Naming convention for Foreign Key

1. 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 table name that hold the Primary Key.
   
2. Each Foreign Key Name should have a “FK_” prefix.
   
3. The first letter of both table names should be capitalized.
   
4. Both table names should end with the character "s" (or "es") to indicate plural.

Examples

1. FK_Employees_Projects
2. FK_Students_ContactNumbers
3. 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:

1. Default constraint should use the syntax “DF_<TableName>_<ColumnName>.
2. Each Default Constraint name should have a “DF_” prefix.
3. The first letter of both TableName and ColumnName should be a capital.
4. The TableName should be end with the letter "s" (or "es") to indicate plural.

Example

1. DF_Employees_EmpName
2. DF_Employees_EmpSalary
3. 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:

1. The unique constraint should use the syntax “UQ_<TableName>_<ColumnName(s)>.
2. Each Unique Constraint name should have a “UQ_” prefix.
3. The first letter of both TableName and ColumnName should be capitalized.
4. TableName should be end with letter "s" (or "es") to indicate plural.
5. If the unique key contains more than one column then each column name is separated with an underscore (_).

Examples

1. UQ_Employees_EmpId_EmployeeName
2. 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:

1. The check constraint should use the syntax “CHK_<TableName>_<ColumnName>.
2. Each Check Constraint name should have a “CHK_” prefix.
3. The first letter of both TableName and ColumnName should be capitalized.
4. TableName should be end with character "s" (or "es") to indicate plural.

Examples

1. CHK_Employees_EmpSalary
2. CHK_Employees_Age
3. 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:

1. A user-defined function should use the syntax “fn_<ActionName>”.
2. Each user-defined function name has a “fn_” prefix.
3. The first letter of Action name should be capitalized.
4. If an action name contains more than a single word then the first character of each word should be capitalized.
   

Examples

1. fn_CalulateProfit
2. 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:

1. Each user-defined Stored Procedure should use the syntax “usp_<TableName><ActionName> “.
2. Each user-defined Stored Procedure name should have a “usp_” prefix.
3. Each action name and table name should start with a capital letter.
4. The table name should be end with the letter "s" (or "es") to indicate plural.

Example

1. usp_Employees_Insert
2. 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:

1. Each index name should be use the syntax IX_<TableName>_<Column(s)>.
2. Each index name should have a prefix “IX_”.
3. Each table name and column name should start with a capital letter.
4. The table name should be end with letter "s" (or "es") to indicate plural.
5. 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

1. IX_Employees_EmpId
2. IX_OrderDetails_OrderNo_OrderDate

Triggers

Triggers are a types of Stored Procedures that are invoked automatically (implicitly) when a DML command is executed. The naming convention for triggers is:

1. Each trigger name should use the syntax “TR_<TableName>_<ActionName>.
2. Each trigger name should have the prefix “TR_”.
3. Each table name and action name should start with a capital letter.
4. The table name should be 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 naming convention

1. Do not use predefined SQL keywords for any object name.
2. Ignore spaces between the names of any object using brackets ([ ]) if necessary.
3. The naming pattern of an object should be the same (don't use a different naming pattern for the same type of object).
4. Ignore the use of a numeric or special symbol in the naming, like (tbl_Employeee3 ,tbl_Employee@Master).