— tableofcontents: 1 redirect_from: /Dealing-with-SQL-files —
<!DOCTYPE markdown> # SQL Standards
## Queries
### General standards
We always use backticks \` around the table- and column names. \`creature_loot_template\`.
We always use single quotes around string values ' ' but NEVER around an integer.
We never include the database name in queries.
### INSERT & DELETE
We always DELETE before an INSERT to ensure we always put fields in the query and that no errors occur.
Your DELETE statement should always include at least one column with the PRIMARY key.
Wrong:
```sql INSERT INTO `creature_loot_template` (3, 884, 0, 40, 1, 1, 0, 1, 1, 'Comment');
REPLACE INTO `creature_loot_template` (3, 884, 0, 40, 1, 1, 0, 1, 1, 'Comment'); ```
Correct:
```sql DELETE FROM `creature_loot_template` WHERE `entry` = 3 AND `item` = 884; INSERT INTO `creature_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES (3, 884, 0, 40, 1, 1, 0, 1, 1, 'Comment'); ``` —
When performing DELETE queries, always specify more than one field. In particular, specify at least one primary key present in the table and another accompanying field.
Wrong:
```sql DELETE FROM `creature_loot_template` WHERE `item` = 884; ```
Correct:
```sql DELETE FROM `creature_loot_template` WHERE `entry` = 3 AND `item` = 884; ```
### UPDATE
Make sure your queries are precise so you avoid changing something you do not want to edit.
Rule of thumb, always include all primary keys in your WHERE clause.
Wrong:
```sql UPDATE `creature_loot_template` SET `Chance` = 100 WHERE `item` = 884; ```
Correct:
```sql UPDATE `creature_loot_template` SET `Chance` = 100 WHERE `entry` = 3 AND `item` = 884; ```
### Variables
Variables can be good when you change the same entry in several places to avoid mistakes.
Before:
```sql UPDATE `creature_template` SET `AIName` = 'SmartAI' WHERE `entry` = 7727;
DELETE FROM `smart_scripts` WHERE `entryorguid` = 7727 AND `source_type` = 0;
INSERT INTO `smart_scripts` (`entryorguid`, `source_type`, `id`, `link`, `event_type`, `event_phase_mask`, `event_chance`, `event_flags`, `event_param1`, `event_param2`, `event_param3`, `event_param4`, `event_param5`, `action_type`, `action_param1`, `action_param2`, `action_param3`, `action_param4`, `action_param5`, `action_param6`, `target_type`, `target_param1`, `target_param2`, `target_param3`, `target_param4`, `target_x`, `target_y`, `target_z`, `target_o`, `comment`) VALUES
(7727, 0, 0, 0, 0, 0, 100, 0, 2000, 4000, 2000, 4000, 0, 11, 930, 64, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 'Grimtotem Shaman - In Combat - Cast \'Chain Lightning\),
(7727, 0, 1, 0, 2, 0, 100, 1, 0, 50, 0, 0, 0, 11, 8499, 2, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 'Grimtotem Shaman - Between 0-50% Health - Cast \'Fire Nova\' (No Repeat)'),
(7727, 0, 2, 0, 2, 0, 100, 0, 0, 30, 0, 0, 0, 11, 8005, 64, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 'Grimtotem Shaman - Between 0-30% Health - Cast \'Healing Wave\);
```
After:
```sql – Grimtotem Shaman SAI SET @ENTRY := 7727; SET @SPELL1 := 930; SET @SPELL2 := 8499; SET @SPELL3 := 8005;
UPDATE `creature_template` SET `AIName` = 'SmartAI' WHERE `entry` = @ENTRY;
DELETE FROM `smart_scripts` WHERE `entryorguid` = @ENTRY AND `source_type` = 0;
INSERT INTO `smart_scripts` (`entryorguid`, `source_type`, `id`, `link`, `event_type`, `event_phase_mask`, `event_chance`, `event_flags`, `event_param1`, `event_param2`, `event_param3`, `event_param4`, `event_param5`, `action_type`, `action_param1`, `action_param2`, `action_param3`, `action_param4`, `action_param5`, `action_param6`, `target_type`, `target_param1`, `target_param2`, `target_param3`, `target_param4`, `target_x`, `target_y`, `target_z`, `target_o`, `comment`) VALUES
(@ENTRY, 0, 0, 0, 0, 0, 100, 0, 2000, 4000, 2000, 4000, 0, 11, @SPELL1, 64, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 'Grimtotem Shaman - In Combat - Cast \'Chain Lightning\),
(@ENTRY, 0, 1, 0, 2, 0, 100, 1, 0, 50, 0, 0, 0, 11, @SPELL2, 2, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 'Grimtotem Shaman - Between 0-50% Health - Cast \'Fire Nova\' (No Repeat)'),
(@ENTRY, 0, 2, 0, 2, 0, 100, 0, 0, 30, 0, 0, 0, 11, @SPELL3, 64, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 'Grimtotem Shaman - Between 0-30% Health - Cast \'Healing Wave\);
```
### Compact queries
We always keep the code as compact as possible to limit the size of the files and decrease the number of queries needed to run.
Wrong:
```sql DELETE FROM `table_1` WHERE `entry` = 1000; DELETE FROM `table_1` WHERE `entry` = 2000; DELETE FROM `table_1` WHERE `entry` = 3000;
INSERT INTO `table_1` VALUES (1000, …); INSERT INTO `table_1` VALUES (2000, …); INSERT INTO `table_1` VALUES (3000, …);
UPDATE `table_1` SET `field_1` = 'someValue' WHERE `entry` = 1000; UPDATE `table_1` SET `field_1` = 'someValue' WHERE `entry` = 2000; UPDATE `table_1` SET `field_1` = 'someValue' WHERE `entry` = 3000; ```
Correct:
```sql DELETE FROM `table_1` WHERE `entry` IN (1000, 2000, 3000);
INSERT INTO `table_1` VALUES (1000, …), (2000, …), (3000, …);
UPDATE `table_1` SET `field_1` = 'someValue' WHERE `entry` IN (1000, 2000, 3000); ```
### Flags & Bits
For fields in the database where we work with flags, it is always preferred that we add or remove flags instead of overriding them.
Wrong:
```sql UPDATE `creature_template` SET `mechanic_immune_mask` = 617299803 WHERE `entry` = 7727; ```
Correct:
```sql – Adding flags UPDATE `creature_template` SET `mechanic_immune_mask`=`mechanic_immune_mask`|64|256|1024 WHERE `entry` = 7727;
– Removing flags UPDATE `creature_template` SET `mechanic_immune_mask`=`mechanic_immune_mask`&~(64|256|1024) WHERE `entry` = 7727; ```
## Tables and Columns
### Naming of tables and columns
For the most part, tables should be written in snake case, and columns should be written in upper camel case.
Tables:
``` broadcast_text creature_loot_template points_of_interest ```
Columns:
``` MaleText QuestRequired PositionX ```
In the case of column names with acronyms (e.g. GUID, ID, NPC, etc) the letters in the acronym should be uppercase.
Wrong:
``` Itemguid DisplayId RequiredNpcOrGoCount ```
Correct:
``` ItemGUID DisplayID RequiredNPCOrGOCount ```
### Integers
We do not define the width of an integer when we create new columns. (Width is deprecated in later versions of MySQL 8)
``` TINYINT(M) → TINYINT SMALLINT(M) → SMALLINT INT(M) → INT BIGINT(M) → BIGINT
BOOL → Never used, a 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)
``` FLOAT UNSIGNED → CHECK (`column`>=0) ```
### Check Constraints
You can see [here](https://github.com/Azerothcore/azerothcore-wotlk/blob/master/data/sql/base/db_auth/realmlist.sql) how check constraints are made.
All active Check Constraints can be found by using this query:
```sql – MySQL 8.0 and newer SELECT * FROM information_schema.CHECK_CONSTRAINTS;
– MySQL 5.7 SELECT * FROM information_schema.TABLE_CONSTRAINTS; ```
### Charset
We always use `utf8mb4` as the charset
### Collation
We always use `utf8mb4_unicode_ci` as the collation
The only exception is where we use a character name, here we need to use `utf8mb4_bin` to allow the name to be accent-sensitive.
### Engine
We always use `InnoDB` as the engine
### Row Format
We always use `DEFAULT` as the 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 GUIDs, 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).