MariaDB Enterprise Server provides DDL (Data Definition Language) syntax to create, alter, or drop constraints.

In the context of ISO 9075-2:2016, this page uses "DDL (Data Definition Language)" to refer to SQL statements in the standard's "SQL-schema statements" group.

When creating a table, you can define a column with a NOT NULL constraint to prevent null values. This constraint ensures data integrity by guaranteeing that a column must always contain a value. If an attempt is made to insert or update a row with a null value in a NOT NULL column, the operation will be rejected, thus maintaining the integrity of the database.

Overview

MariaDB Server supports NOT NULL constraints to ensure that a column's value is not set to NULL:

When a column is declared with a NOT NULL constraint, Enterprise Server rejects operations that would write a NULL value to the column

CREATE TABLE

CREATE TABLE and NOT NULL Constraints

With MariaDB Server, the statement can be used to create a new table with a NOT NULL constraint on one or more columns:

ALTER TABLE

ALTER TABLE .. MODIFY COLUMN .. NOT NULL

With MariaDB Server, the statement can be used to add the NOT NULL constraint to a column using the MODIFY COLUMN clause:

With MariaDB Server, the statement can be used to remove the NOT NULL constraint from a column using the MODIFY COLUMN clause:

The unique_checks system variable in MariaDB Server can be utilized to disable unique checks, which might be useful in specific scenarios where enforcing unique constraints temporarily is not required. This can enhance performance during bulk inserts, for instance.

Overview

MariaDB Server supports UNIQUE constraints to ensure that a column's value is unique within a table:

• MariaDB Server uses unique indexes to speed up query execution and enforce unique constraints

• MariaDB Server supports single column and composite (multi-column) unique indexes

• MariaDB Community Server can have up to 64 total indexes for a given table.

• MariaDB Server can have up to 128 total indexes for a given table.

• InnoDB stores unique indexes in the same tablespace file as the clustered index and data.

• Unique indexes are B+ trees, which are very efficient for searching for exact values, performing range scans, and checking uniqueness.

• If no primary key is defined for a table, then InnoDB will use the table's first NOT NULL unique index as the table's primary key.

• If no primary key or NOT NULL unique index is defined for a table, then InnoDB will automatically create a primary key called GEN_CLUST_INDEX, using an internal 48-bit DB_ROW_ID column as the key. Replication with such tables can be very slow, especially when is MIXED or ROW.

unique_checks System Variable

MariaDB Server provides the , which can be used to disable unique checks.

When unique checks are disabled, the InnoDB change buffer is used for inserts into unique indexes, and duplicate values will not be detected.

Disabling unique checks can speed up bulk data loads, but it is dangerous to do so.

Creating an InnoDB Table with a Single Column Unique Index

Let's create an InnoDB table with a single column unique index after confirming that the default storage engine is InnoDB:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table using the statement and specify the unique index with the UNIQUE INDEX() clause:

1. For a single column unique index, the unique index can also be specified with the UNIQUE column option:

Creating an InnoDB Table with a Composite Unique Index

Let's create an InnoDB table with a composite (multi-column) unique index after confirming that the default storage engine is InnoDB:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table using the statement and specify the unique index with the UNIQUE INDEX() clause:

Creating an InnoDB Table with a Unique Index on a Column Prefix

Let's create an InnoDB table with a unique index on a single column prefix after confirming that the default storage engine is InnoDB:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table using the statement and specify the unique index with the UNIQUE INDEX() clause:

The unique index is specified with the product_description(1000) prefix, so only the first 1000 characters of the product_description column for each row will be indexed and checked for uniqueness.

Adding a Unique Index to an InnoDB Table

Let's create an InnoDB table without a unique index, and then add a unique index to it:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table without a primary key using the statement:

1. Alter the table using the statement and specify the new unique index with the ADD UNIQUE INDEX() clause:

Dropping a Unique Index from an InnoDB Table

Let's drop the unique index from the table created in the section:

1. Obtain the name of the index by joining the , , and tables:

1. Alter the table using the statement and specify the DROP INDEX clause:

Rebuilding a Unique Index in an InnoDB Table

Let's rebuild the unique index in the table created in the section:

1. Obtain the name of the index by joining the , , and tables:

1. Alter the table using the statement and specify the DROP INDEX clause:

1. Alter the table using the statement and specify the unique index with the ADD UNIQUE INDEX() clause:

Using an AUTO_INCREMENT column as the primary key in MariaDB is beneficial for ensuring unique row identification and efficient data retrieval. This approach automatically generates a unique integer for each new row, simplifying primary key management. It is especially useful in tables where data insertion occurs frequently, as it facilitates quick indexing and minimizes storage overhead due to its numeric nature. This practice helps maintain optimal performance in database operations, particularly when combined with clustered indexing in InnoDB.

Overview

MariaDB Server supports PRIMARY KEY constraints to uniquely identify rows:

There can only be a single primary key for a given table.

In MariaDB Server, FOREIGN KEY constraints are key elements in defining relationships between InnoDB tables: they ensure referential integrity by maintaining valid associations between rows in parent and child tables. Changes in the parent table can either be restricted or propagated to maintain consistency with child tables.

Overview

MariaDB Server supports FOREIGN KEY constraints to define referential constraints between InnoDB tables:

• Referential constraints allow the database to automatically ensure that each row in the child table is associated with a valid row in the parent table

• If the row in the parent table changes, MariaDB Server can block the change to protect child rows, or propagate the change to the child rows

Term Definitions

Uses Cases for Foreign Keys

Foreign keys allow the database to handle certain types of referential constraint checks, so that the application does not have to handle them. As a consequence, application logic can be simplified.

Some example use cases are described below.

Managing Accounts Use Case

Many different applications have account management features. Foreign keys can be used to simplify certain aspects of managing accounts, such as:

• If an account is deleted, foreign keys can be used to automatically delete data associated with the account.

• If an account is deleted, foreign keys can be used to automatically disassociate its data, so that it is no longer associated with any account.

• If an account's user name or user ID is updated, foreign keys can be used to automatically associate its data with the new user name or user ID.

Managing Store Inventory Use Case

Foreign keys can also be used to simplify certain aspects of managing a store inventory, such as:

• If a product is deleted, foreign keys can be used to automatically delete data associated with the product, such as reviews and images.

• If a product's name is changed, foreign keys can be used to automatically associate the new name with the product's other data.

• If a customer purchases a product, foreign keys can be used to associate the order with the product.

• If a customer reviews a product, foreign keys can be used to associate the review with the product.

Operating on InnoDB Tables with Foreign Keys

When an InnoDB table has a foreign key or when it is referenced by a foreign key, InnoDB performs a referential constraint check when certain types of operations try to write to the table. The specific details depend on whether the table is a parent table or a child table.

Operating on a Parent Table

When an InnoDB table has a foreign key, it is known as a parent table. InnoDB performs referential constraint checks for the following operations on parent tables:

When InnoDB performs a referential constraint check, the outcome depends on several factors. The following table describes the details:

Operating on a Child Table

When an InnoDB table is referenced by a foreign key, it is known as a child table. InnoDB performs referential constraint checks for the following operations on child tables:

When InnoDB performs a referential constraint check, the outcome depends on several factors. The following table describes the details:

Creating InnoDB Tables with a Foreign Key Constraint

Let's create InnoDB tables with a foreign key constraint after confirming that the is InnoDB:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the sequence and table using the statement:

1. Create the parent table using the statement:

1. Create the child table using the statement:

1. Insert some rows into the parent table using the statement:

1. Insert a row into the child table for each row in the parent table using the statement:

1. Attempt to delete a row from the parent table that has a corresponding row in the child table using the DELETE statement:

This will fail with the error code as explained in the Operating on a Parent Table section:

Attempt to insert a row into the child table for a non-existent row in the parent table using the INSERT statement:

This will fail with the error code as explained in the Operating on a Child Table section:

Adding a Foreign Key Constraint to an InnoDB Table

Let's create InnoDB tables after confirming that the is InnoDB, and then let's add a foreign key constraint between them:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the sequence and table using the statement:

1. Create the parent table using the statement:

1. Create the child table using the statement:

1. Alter the child table to add the foreign key constraint using the statement:

1. Insert some rows into the parent table using the statement:

1. Insert a row into the child table for each row in the parent table using the statement:

1. Attempt to delete a row from the parent table that has a corresponding row in the child table using the statement:

This will fail with the error code as explained in the Operating on a Parent Table section:

1. Attempt to insert a row into the child table for a non-existent row in the parent table using the statement:

This will fail with the error code as explained in the Operating on a Child Table section:

Dropping a Foreign Key Constraint from an InnoDB Table

Let's drop the foreign key constraint from the child table created in the section:

1. Connect to the server using MariaDB Client:

1. Obtain the name of the foreign key constraint by querying the table:

1. Drop the foreign key constraint from the child table using the statement:

Temporarily Disabling Foreign Key Constraint Checks

When performing bulk data loads, dropping a table, or rebuilding a table, it can improve performance to temporarily disable foreign key constraint checks. Foreign key constraint checks can be temporarily disabled by setting the session value for the foreign_key_checks system variable. If foreign key constraint checks are temporarily disabled, then rows can be inserted that violate the constraint, so users must proceed with caution. This feature is most useful when you are loading a data set that is known to be valid.

Let's temporarily disable foreign key constraint checks, and then perform some tasks with the tables created in the section:

1. Connect to the server using MariaDB Client:

1. Temporarily disable foreign key constraint checks by setting the system variable with the statement:

1. If you want to test how to introduce inconsistencies, then attempt to delete a row from the parent table that has a corresponding row in the child table using the statement:

This operation would usually fail with the error code as explained in the Operating on a Parent Table section, but if foreign key constraint checks are disable, then it will succeed.

1. If you want to test how to introduce inconsistencies, then also attempt to insert a row into the child table for a non-existent row in the parent table using the statement:

This operation would usually fail with the error code as explained in the section, but if foreign key constraint checks are disable, then it will succeed.

1. Re-enable foreign key constraint checks by setting the system variable with the statement:

Special Cases

Foreign Key Constraint Namespace

When a foreign key constraint is created without a name, InnoDB implicitly gives it a name in the format:

If foreign key constraints are explicitly created with names in the same format, it is possible for collisions to occur during table renames. In that case, the would contain messages like the following:

Dependence on an Index

A foreign key constraint requires an index on the column. If a foreign key constraint is added to a column without an index, InnoDB will automatically create an index to enforce the foreign key constraint.

When the system variable is disabled, it is possible to drop the index used by a foreign key constraint. When the system variable is re-enabled, InnoDB will have no way to enforce the foreign key constraint, so all operations that could potentially violate the foreign key constraint will fail.

To define an AUTO_INCREMENT column, select an integer data type to accommodate the range of anticipated values. Common choices include INT, BIGINT, TINYINT, SMALLINT, and MEDIUMINT, depending on size requirements. Ensure the chosen type provides sufficient capacity to avoid overflow while optimizing storage efficiency.

Overview

MariaDB Enterprise Server supports AUTO_INCREMENT constraints:

• A column with an AUTO_INCREMENT constraint can act as a table's primary key when a natural primary key is not available

• Generated values for auto-increment columns are guaranteed to be unique and monotonically increasing

• Auto-increment columns provide compatibility with schemas designed for MariaDB Enterprise Server and MySQL

Alternatively, MariaDB Enterprise Server can use as the primary key instead of columns with AUTO_INCREMENT constraints. Sequences are compliant with the SQL standard, while AUTO_INCREMENT constraints are not, so sequences are the better option for applications that require standard-compliant features.

Choosing a Data Type for an AUTO_INCREMENT Column

When designing a schema, AUTO_INCREMENT columns should use integer data types. The following types can be used:

To determine which type to use, consider the following points:

1. Do you want to be able to manually insert negative values? If not, then specify the UNSIGNED attribute for the column.

InnoDB can't generate negative AUTO_INCREMENT values, so it is only beneficial to use a signed integer column if you want the option to manually insert negative values, which would bypass the AUTO_INCREMENT handling.

1. How large will your table grow?

If your AUTO_INCREMENT column is being used as the table's primary key, then the maximum value for the chosen data type should be considered the maximum number of rows that can fit in the table:

If you want to give your table the most room to grow, then it would be best to choose BIGINT UNSIGNED.

Creating an InnoDB Table with an AUTO_INCREMENT Column

Let's after confirming that the is InnoDB:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table using the statement:

Inserting a Row with an AUTO_INCREMENT Column

If a column is specified as AUTO_INCREMENT, then its value will be automatically generated. There are multiple ways to insert rows with these automatically generated values.

Omitting the Column

If the column is not specified, then InnoDB will automatically generate the value.

Let's insert a row into the table created in the section:

1. Connect to the server using MariaDB Client:

1. Insert a row with the statement, but do not specify the AUTO_INCREMENT column:

1. Select the same row with the statement to confirm that a value was automatically generated:

Specifying the Column's Value as 0

If the column's value is specified as 0, then InnoDB will automatically generate the value if the system variable does not contain NO_AUTO_VALUE_ON_ZERO.

Let's insert a row into the table created in the section:

1. Connect to the server using MariaDB Client:

1. Confirm that the session's value of the sql_mode system variable does not contain NO_AUTO_VALUE_ON_ZERO with the statement:

1. Insert a row with the statement, and specify the AUTO_INCREMENT column's value as 0:

1. Select the same row with the statement to confirm that a value was automatically generated:

Specifying the Column's Value as NULL

If the column's value is specified as NULL, then InnoDB will automatically generate the value if the column is defined as NOT NULL.

Let's insert a row into the table created in the section:

1. Connect to the server using MariaDB Client:

1. Insert a row with the statement, and specify the AUTO_INCREMENT column's value as NULL:

1. Select the same row with the statement to confirm that a value was automatically generated:

Getting the Last Inserted AUTO_INCREMENT Value

After InnoDB inserts an automatically generated value into an AUTO_INCREMENT column, the application sometimes needs to know what value it inserted. For example, the application may need to use the value to insert a foreign key column in a dependent table. The function can be used to get the lasted inserted value for an AUTO_INCREMENT column without re-reading the row from the table.

Let's insert a row into the table created in the section and then use the function:

1. Connect to the server using MariaDB Client:

1. Insert a row with the statement, but do not specify the AUTO_INCREMENT column:

1. Execute the function to get the AUTO_INCREMENT value for the new row:

1. Select the same row with the statement to confirm that the AUTO_INCREMENT column has the same value:

Choosing an AUTO_INCREMENT Lock Mode for InnoDB

When multiple rows are inserted into a table concurrently, InnoDB needs to be able to generate multiple values concurrently in a safe manner. It has several different modes that can be used to do this, and each mode has its own advantages and disadvantages.

InnoDB's AUTO_INCREMENT lock mode is configured with the system variable. Users can choose between 3 different values:

• In interleaved lock mode, InnoDB never holds a table-level lock while generating AUTO_INCREMENT values. • Interleaved lock mode is not safe to use if binlog_format is set to STATEMENT.|

Configuring the AUTO_INCREMENT Lock Mode for InnoDB

The system variable configures the AUTO_INCREMENT Lock Mode for InnoDB.

1. Choose a configuration file for custom changes to system variables and options.

It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

Ensure that your custom changes are read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Use the z- prefix in the file name to ensure that your custom configuration file is read last.

Some example configuration file paths for different distributions are shown in the following table:

1. Set the system variable in the configuration file. It needs to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].

For example:

1. Restart the server:

Setting a Table's Next AUTO_INCREMENT Value

A table's next AUTO_INCREMENT value can be set with the statement. The value is set using the AUTO_INCREMENT table option.

Let's alter the AUTO_INCREMENT value for the table created in the section and then insert a row into the table, so we can confirm that it uses the new value:

1. Connect to the server using MariaDB Client:

1. Alter the table's next AUTO_INCREMENT value with the statement:

1. Insert a row with the statement, but do not specify the AUTO_INCREMENT column:

1. Execute the function to get the AUTO_INCREMENT value for the new row:

1. Select the same row with the statement to confirm that the AUTO_INCREMENT column has the same value:

Configure the Offset and Increment Values

The offset and increment values can be configured by setting the system variables.

When Galera Cluster is used, the offset and increment values are managed automatically by default. They can be managed manually by disabling the system variable.