SQL

SQL is a standard language for accessing databases. Where a difference occurs between MySQL and Oracle (PL/SQL) syntax, you will find both examples.

SQL Standards

Schema objects – All non-table schema objects will be prefixed by their type and all index names will begin with idx, and all constraint names will begin with cons.
Referential Integrity conventions – All tables in development databases will have full RI, including PK constraints, FK constraints and check constraints. The default for foreign key constraints will be “On Delete Restrict”, unless otherwise specified. This means that no parent record can be deleted if there are corresponding child records. For performance reasons, a project may elect to drop RI constraints in test and production environments as long as sufficient testing proves the code can not generate bad data.
Primary keys – Oracle Sequences will be used to generate unique row identifiers and all sequence numbers generally will start at one and increment by one.
Check Constraints – Lists of valid values will be used in all cases to restrict column values and validity.

All table names will be plural (e.g. users vs. user).
Full table names will be used whenever possible.
If a table name should exceed 30 characters, reduce the size of the table name in this order:
- From the left of the table name, remove vowels from each word in the table name except for the first vowel of each word.
- If the table name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.

Column names should be spelled out whenever possible.
If a column name should exceed 30 characters, reduce the size of the column name in this order:
- From the left of the column name, remove vowels from each word in the table name except for the first vowel of each word.
- If the column name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.