SQL Coding Convention

Contents

• Objective
• Naming Conventions
  • Databases
  • Tables
  • Columns
  • Variables
  • Hungarian Notation and Allowed Prefixes
• Query Syntax
• References

Objective

The primary objective of this wiki page is to set up a common SQL Coding Convention for WHAT Project database scripts. The usage of Coding Convention is crucial for one's productivity and convenience whilst debugging or re-writing some code.

Naming Conventions

Databases

Use PascalCase for database names.

❌ librarycatalog

❌ library_catalog

❌ libraryCatalog

✔️ LibraryCatalog

Tables

Use PascalCase for table names.

❌ booksauthors

❌ books_authors

❌ booksAuthors

✔️ BooksAuthors

Columns

Use PascalCase for column names.

❌ dateadded

❌ date_added

❌ dateAdded

✔️ DateAdded

Variables

Use PascalCase for variable names.

❌ maincounter

❌ main_counter

❌ mainCounter

✔️ MainCounter

Hungarian Notation and Allowed Prefixes

Although using Hungarian notation is usually seen as a bad practice, some prefixes are still allowed for use as long as the code is consistent. The rest of the name (after prefix) should be put in PascalCase. These prefixes are as follows:

PK_ (primary key)

PK_BookID

FK_ (foreign key)

FK_AuthorID

CH_ (check constraint)

CH_Dates

UQ_ (unique constraint)

UQ_Login

TR_ (trigger)

TR_LogBookInsert

VW_ (view)

VW_BookInfo

SP_ (stored procedure)

SP_AddBook

IX_ (index)

IX_BookTitle

Query Syntax

All keywords must be put in uppercase. Do not use abbreviated keywords.

Try to keep only one meaningful part of SQL expression per line and line up code with spaces:

-- Table `AttachmentsOfHomeworksFromStudents`

DROP TABLE IF EXISTS `AttachmentsOfHomeworksFromStudents`;

CREATE TABLE IF NOT EXISTS `AttachmentsOfHomeworksFromStudents` (
    `ID`                       BIGINT UNSIGNED    NOT NULL     AUTO_INCREMENT,
    `AttachmentID`             BIGINT UNSIGNED    NOT NULL,
    `HomeworkFromStudentID`    BIGINT UNSIGNED    NOT NULL,

    CONSTRAINT    `PK_AttachmentOfHomeworkFromStudent`    PRIMARY KEY (`ID`),
    CONSTRAINT    `FK_Attachment`                         FOREIGN KEY (`AttachmentID`)             REFERENCES `Attachments` (`ID`),
    CONSTRAINT    `FK_HomeworkFromStudent`                FOREIGN KEY (`HomeworkFromStudentID`)    REFERENCES `HomeworksFromStudents` (`ID`),

    INDEX    `IX_HomeworkFromStudent`    (`HomeworkFromStudentID` ASC),
    INDEX    `IX_Attachment`             (`AttachmentID` ASC)
);

References

• https://www.sqlstyle.guide/
• https://oracle.readthedocs.io/en/latest/sql/basics/style-guide.html
• https://raw.githubusercontent.com/microsoft/sql-server-samples/master/samples/databases/northwind-pubs/instnwnd.sql
• Joe Celko's SQL Programming Style