Post
Ask Question

Naming Conventions in SQL

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:

  1. Provides a specific standard for naming all objects so they look the same or are 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 did.
  4. Enhances 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 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:
  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 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 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:

  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 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 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 the 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 the table name that holds the Primary Key.
  2. Each Foreign Key Name should have an “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 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 end with the 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 end with the 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 an “fn_” prefix.
  3. The first letter of the 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 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 use the syntax IX_<TableName>_<Column(s)>.
  2. Each index name should have the prefix “IX_”.
  3. Each table name and column name should start with a capital letter.
  4. The table name should end with the 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 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:

  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 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

  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).

Conclusion

In this article, we learned about Naming Conventions with examples in SQL.


Similar Articles