From 8f6be9b570ef752c3c5749796f923bfaa4102420 Mon Sep 17 00:00:00 2001
From: Kitzunu <24550914+Kitzunu@users.noreply.github.com>
Date: Fri, 17 Mar 2023 12:46:33 +0100
Subject: Update sql-standards.md

---
 docs/sql-standards.md | 49 +++++++++++++++++++++++++++++++++++++------------
 1 file changed, 37 insertions(+), 12 deletions(-)

(limited to 'docs')

diff --git a/docs/sql-standards.md b/docs/sql-standards.md
index 388a17e..7604cf7 100644
--- a/docs/sql-standards.md
+++ b/docs/sql-standards.md
@@ -205,12 +205,13 @@ We do not define the width of an integer when we create new columns. (Width is d
 TINYINT(M)   -> TINYINT
 SMALLINT(M)  -> SMALLINT
 INT(M)       -> INT
-MEDIUMINT(M) -> MEDIUMINT
 BIGINT(M)    -> BIGINT
 
 BOOL         -> Never used, synonym for TINYINT. 0 = false <>0 = true
 ```
 
+We never use MEDIUMINT. Use INT instead!
+
 ### Float, Double, Decimal
 
 These data types cannot be UNSIGNED and therefore we use CHECK CONSTRAINTS instead. (UNSIGNED Float, Double, Decimal is deprecated in later versions of MySQL 8)
@@ -219,17 +220,6 @@ These data types cannot be UNSIGNED and therefore we use CHECK CONSTRAINTS inste
 FLOAT UNSIGNED -> CHECK (`column`>=0)
 ```
 
-### Character Encoding
-
-We use UTF8MB4 where you would previously use UTF8 or UTF8MB3. (utf8 is an alias and utf8mb3 is deprecated in later versions of MySQL 8)
-
-```
-utf8    -> utf8mb4
-utf8mb3 -> utf8mb4
-```
-
-To be able to support MariaDB alongside MySQL we are limited to using `utf8mb4_general_ci` as coallation.
-
 ### Check Constraints
 
 You can see [here](https://github.com/Azerothcore/azerothcore-wotlk/blob/master/data/sql/base/db_auth/realmlist.sql) how check contraints are made.
@@ -244,6 +234,41 @@ SELECT * FROM information_schema.CHECK_CONSTRAINTS;
 SELECT * FROM information_schema.TABLE_CONSTRAINTS;
 ```
 
+### Charset
+
+We always use `utf8mb4` as charset
+
+### Collation
+
+We always use `utf8mb4_unicode_ci` as collation
+
+### Engine
+
+We always use `InnoDB` as the engine
+
+### Row Format
+
+We always use `DEFAULT` as row format
+
+### Dummy script for table
+
+```sql
+
+DROP TABLE IF EXISTS `our_table_name`;
+CREATE TABLE `our_table_name` (
+  `aColumnName` INT UNSIGNED NOT NULL DEFAULT '69' COMMENT 'ColumnComment',
+  `aSecondName` VARCHAR(100) NOT NULL DEFAULT '',
+  PRIMARY KEY (`aColumnName`),
+  CONSTRAINT `our_table_name_chk_1` CHECK (`aColumnName` >= 0)
+)
+COMMENT = 'TableComment'
+CHARSET = utf8mb4
+COLLATE = utf8mb4_unicode_ci
+ENGINE = InnoDB
+ROW_FORMAT = DEFAULT
+;
+```
+
 ## Note for SQL reviewer
 
 When we work with GUID's, make sure that we use as low entries as possible to fill out the gaps in the database. This can easily be done with tools like [Unused GUID Searcher](https://github.com/azerothcore/unused-guid-search).
-- 
cgit