{"id":97823,"date":"2023-09-07T16:54:22","date_gmt":"2023-09-07T16:54:22","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97823"},"modified":"2024-02-04T05:15:01","modified_gmt":"2024-02-04T05:15:01","slug":"working-with-mysql-constraints","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/working-with-mysql-constraints\/","title":{"rendered":"MySQL Constraints Guide: PRIMARY KEY, FOREIGN KEY, NOT NULL, DEFAULT, and CHECK Constraints with Examples"},"content":{"rendered":"

This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, click here<\/a>.<\/strong><\/p>\n\n

MySQL provides a set of constraints that you can include in your table definitions to help ensure the integrity of the data. The constraints let you better control the types of data that can be added to the database. For example, you can use constraints to set a column\u2019s uniqueness or nullability, specify a default value for a column, or verify that the data falls within a certain range of acceptable values.<\/p>\n

MySQL supports six basic types of constraints for ensuring data integrity: PRIMARY<\/code> KEY<\/code>, NOT<\/code> NULL<\/code>, DEFAULT<\/code>, CHECK<\/code>, UNIQUE<\/code>, and FOREIGN<\/code> KEY<\/code>. In this article, I introduce you to each constraint type and provide examples for how they work. The examples include a series of CREATE<\/code> TABLE<\/code> statements that demonstrate different ways to incorporate constraints into your table definitions. If you\u2019re not familiar with the CREATE<\/code> TABLE<\/code> statement or how to create tables in a MySQL database, refer to an earlier article<\/a> in this series that introduces you to the statement.<\/p>\n

Some resources also consider data types to be constraints because they limit the types of data that can be inserted into a table\u2019s columns. For example, a column configured with an integer data type will take whole numbers but not decimals or string values. I do not cover data types in this article and instead focus only the six other constraint types. For information about data types, see the MySQL article Data Types<\/a>.<\/p>\n

Note: The examples in this article are based on a local instance of MySQL that hosts a very simple database. The last section of the article\u2014\u201cAppendix: Preparing your MySQL environment\u201d\u2014provides information about how I set up my environment and includes a SQL script for creating the database I used when building these examples.<\/p>\n

PRIMARY<\/strong> KEY<\/strong> constraints<\/h2>\n

A PRIMARY<\/code> KEY<\/code> constraint provides a mechanism for uniquely identifying each row in a table. It is defined on one or more of a table\u2019s columns (the key columns) to ensure the row\u2019s uniqueness. When you add a PRIMARY<\/code> KEY<\/code> constraint to a table, MySQL also creates a unique index on the key columns to enforce their uniqueness.<\/p>\n

A table can have only one primary key, and the key columns must be defined as NOT<\/code> NULL<\/code>. If they are not explicitly defined as NOT<\/code> NULL<\/code>, MySQL implicitly declares them as such to ensure that each row in the table has an associated primary key value. This is important because a unique index on its own can contain multiple NULL<\/code> values, so NOT<\/code> NULL<\/code> is needed to guarantee that a unique identifier is associated with each row.<\/p>\n

You can add a primary key to a table when you create the table. The easiest way to add a single-column primary key is to include it in the column definition, as in the following example:<\/p>\n

DROP TABLE IF EXISTS airplanes; \r\n\r\nCREATE TABLE airplanes (\r\n  plane_id INT UNSIGNED PRIMARY KEY,\r\n  plane VARCHAR(50),\r\n  engine_type VARCHAR(50),\r\n  engine_count TINYINT);<\/pre>\n
The statement creates a primary key on the plane_id<\/code> column. The column\u2019s definition includes the PRIMARY<\/code> KEY<\/code> keywords, which tells MySQL to create a PRIMARY<\/code> KEY<\/code> constraint on that column when creating the airplanes<\/code> table. MySQL creates the unique index at the same time.<\/p>\n
That\u2019s all you need to do to create a primary key. MySQL does the rest. If you want to create a primary key on multiple columns, you must define it separately from the column definition, which I\u2019ll be discussing shortly.<\/p>\n
Note: The <\/em>CREATE<\/em><\/code> TABLE<\/em><\/code> statement in this example is preceded by a <\/em>DROP<\/em><\/code> TABLE<\/em><\/code> statement that includes the <\/em>IF<\/em><\/code> EXISTS<\/em><\/code> option. Because the examples in this article re-create the <\/em>airplanes<\/em><\/code> table, you should precede each example with the <\/em>DROP<\/em><\/code> TABLE<\/em><\/code> statement if trying out the examples for yourself. Although I won\u2019t be repeating the <\/em>DROP<\/em><\/code> TABLE<\/em><\/code> statement going forward, this is the approach I used when I created and tested the examples for this article.<\/em><\/p>\n
MySQL tracks all constraints defined on all the tables in a database. You can view the constraints by querying the TABLE_CONSTRAINTS<\/code> table in the INFORMATION_SCHEMA<\/code>, which tracks all database metadata. The following SELECT<\/code> statement retrieves information about existing constraints in the travel<\/code> database:<\/p>\n
SELECT table_name, constraint_name, constraint_type\r\nFROM information_schema.table_constraints\r\nWHERE constraint_schema = 'travel';<\/pre>\n
The statement returns the results shown in the following figure. The results indicate that PRIMARY<\/code> KEY<\/code> constraints have been defined on the airplanes<\/code> table and manufacturers<\/code> table, which was created as part of the setup for this article.<\/p>\n
<\/p>\n
MySQL automatically assigns the name PRIMARY<\/code> to each primary key constraint and to its associated unique index. Unlike other types of constraints, you cannot change these names. In addition, you should not use the name PRIMARY<\/code> for any other indexes you create.<\/p>\n
You can verify the PRIMARY<\/code> KEY<\/code> constraint in the airplanes<\/code> table by running the following INSERT<\/code> statement twice in a row:<\/p>\n
INSERT INTO airplanes \r\n  (plane_id, plane, engine_type, engine_count)\r\nVALUES (1001,'A340-600','Jet',4);<\/pre>\n
The first time you run the statement, MySQL inserts the data with no problem, but when you run the same statement a second time, MySQL returns the following error because you violated the PRIMARY<\/code> KEY<\/code> constraint:<\/p>\n
Error Code: 1062. Duplicate entry '1001' for key 'airplanes.PRIMARY'<\/pre>\n
Another method you can use to define a PRIMARY<\/code> KEY<\/code> constraint is to add a separate constraint definition after the column definitions, as in the following example:<\/p>\n
CREATE TABLE airplanes (\r\n  plane_id INT UNSIGNED,\r\n  plane VARCHAR(50),\r\n  engine_type VARCHAR(50),\r\n  engine_count TINYINT,\r\n  PRIMARY KEY (plane_id));<\/pre>\n
This CREATE<\/code> TABLE<\/code> statement achieves the same results as the previous CREATE<\/code> TABLE<\/code> statement. In this case, however, you must specify the column on which the primary key will be created. MySQL will then create a PRIMARY<\/code> KEY<\/code> constraint on plane_id<\/code> column, naming the constraint and index PRIMARY<\/code>.<\/p>\n
In some cases, you might want to create a PRIMARY<\/code> KEY<\/code> constraint on multiple columns. For example, you might have two columns in a table that do no uniquely identify each row individually, but together they do. In the following example, the CREATE<\/code> TABLE<\/code> statement defines a primary key on the plane_id<\/code> and alt_id<\/code> columns:<\/p>\n
CREATE TABLE airplanes (\r\n  plane_id INT UNSIGNED,\r\n  alt_id INT UNSIGNED,\r\n  plane VARCHAR(50),\r\n  engine_type VARCHAR(50),\r\n  engine_count TINYINT,\r\n  PRIMARY KEY (plane_id, alt_id));<\/pre>\n
A primary key made up of multiple columns is sometimes referred to as a composite<\/em> primary key. The need to use composite primary keys depends on the nature of your data. In this case, the plane_id<\/code> and alt_id<\/code> columns are used together to create a unique identifier for each row, making it possible for them to serve as the primary key. To test how this work, start by running the following INSERT<\/code> statement:<\/p>\n
INSERT INTO airplanes \r\n  (plane_id, alt_id, plane, engine_type, engine_count)\r\nVALUES (1001,173,'A340-600','Jet',4);<\/pre>\n
The statement should insert the data with no problem because there are no conflicting primary key values. Now run the next INSERT<\/code> statement, which specifies the same plane_id<\/code> value but a different alt_id<\/code> value:<\/p>\n
INSERT INTO airplanes \r\n  (plane_id, alt_id, plane, engine_type, engine_count)\r\nVALUES (1001,174,'A340-600','Jet',4);<\/pre>\n
Once again, the INSERT<\/code> statement should run without problem because together the values in the two columns are still unique. You can verify that the airplanes<\/code> table now contains the two rows of data by running the following SELECT<\/code> statement:<\/p>\n
SELECT * FROM airplanes;<\/pre>\n
The statement returns the results shown in the following figure, which indicates that the information in both rows is the same except for the alt_id<\/code> values.<\/p>\n
<\/p>\n
If you were to rerun the previous INSERT<\/code> statement (or use the same plane_id<\/code> and alt_id<\/code> values in a different INSERT<\/code> statement), MySQL would instead return the following error:<\/p>\n
Error Code: 1062. Duplicate entry '1001-174' for key 'airplanes.PRIMARY'<\/pre>\n
When defining primary keys on InnoDB<\/code> tables, try to keep the key columns as short as possible to minimize storage overhead, such as using integers rather than 20-character strings. Each secondary index defined on an InnoDB<\/code> table contains a copy of the primary key column for the corresponding rows, and the extra data can add up. Shorter key columns can also result in better query performance, depending on the type of queries.<\/p>\n
Note that you do not have to drop the entire table to change the constraint. Using the ALTER TABLE<\/code> statement, you can drop the primary key constraint, and then recreate it.<\/p>\n
--Remove the existing constraint\r\nALTER TABLE airplanes\r\n   DROP PRIMARY KEY;\r\n\r\n--Add the new PRIMARY KEY constraint\r\nALTER TABLE airplanes\r\n   ADD PRIMARY KEY (plane_id, alt_id);<\/pre>\n
If you are new to MySQL, this syntax for altering a primary key may not be what you expect, but the PRIMARY KEY constraint has a set name, unlike in some other RDBMS types.<\/p>\n
NOT<\/strong> NULL<\/strong> constraints<\/h2>\n
When defining a column in a CREATE<\/code> TABLE<\/code> statement, you can specify the column\u2019s nullability, which determines whether the column accepts NULL<\/code> values. A NULL<\/code> value typically means that there is no data or that the column\u2019s value is not known. This is different from a value of 0<\/code> or an empty string, although NULL<\/code> is sometimes confused with these values. (A fair amount of confusion and debate continue to surround NULL<\/code>, but this is a discussion well outside the scope of this article.)<\/p>\n
You can specify a column\u2019s nullability by including the keywords NULL<\/code> or NOT<\/code> NULL<\/code> in the column definition. By default, MySQL permits NULL<\/code> values, so if you don\u2019t specify a nullability option, MySQL will assume NULL<\/code>, unless the column is a primary key. If you do not want to permit NULL<\/code> values, you must add the NOT<\/code> NULL<\/code> keywords to your column definitions, as in the following example:<\/p>\n
CREATE TABLE airplanes (\r\n  plane_id INT UNSIGNED NOT NULL,\r\n  alt_id INT UNSIGNED NOT NULL,\r\n  plane VARCHAR(50) NOT NULL,\r\n  engine_type VARCHAR(50) NOT NULL,\r\n  engine_count TINYINT NOT NULL,\r\n  PRIMARY KEY (plane_id));<\/pre>\n
The CREATE<\/code> TABLE<\/code> statement is the same as in the previous example, only now each column definition includes NOT<\/code> NULL<\/code>. You can test a column\u2019s nullability by running a few INSERT<\/code> statements, starting with the following:<\/p>\n
INSERT INTO airplanes \r\n  (plane_id, alt_id, plane, engine_type, engine_count)\r\nVALUES (1001,173,'A340-600','Jet',4);<\/pre>\n
This statement should run with no problem because the statement provides a non-NULL<\/code> value for all the columns, but now try to run the following INSERT<\/code> statement, which specifies NULL<\/code> as the engine_type<\/code> value:<\/p>\n
INSERT INTO airplanes \r\n  (plane_id, alt_id, plane, engine_type, engine_count)\r\nVALUES (1002,174,'A350-800 XWB',NULL,2);<\/pre>\n
This time MySQL returns the following error, which states that the engine_type<\/code> value cannot be NULL<\/code>:<\/p>\n
Error Code: 1048. Column 'engine_type' cannot be null<\/pre>\n
Instead of trying to insert a NULL<\/code> value, you might try to insert the row without specifying an engine_type<\/code> value:<\/p>\n
INSERT INTO airplanes \r\n  (plane_id, alt_id, plane, engine_count)\r\nVALUES (1002,174,'A350-800 XWB',2);<\/pre>\n
This time you\u2019ll get a different error message because MySQL doesn\u2019t know what to do with the engine_type<\/code> column:<\/p>\n
Error Code: 1364. Field 'engine_type' doesn't have a default value<\/pre>\n
If a default value is assigned to the column (a topic I\u2019ll be discussing shortly), MySQL will insert that value into the column if no value is provided. Without a default value, MySQL returns an error.<\/p>\n
However, this is true only if strict mode is enabled on your MySQL server, which is the default setting. If strict mode is disabled, MySQL will implicitly insert the data type\u2019s default value, which in this case, is an empty string. This is because the data type for the engine_type<\/code> column is VARCHAR<\/code>, and MySQL uses an empty string as the implicit default value for all string types except ENUM<\/code>.<\/p>\n
Note: A discussion about strict mode and how to disable and enable it is beyond the scope of this article. For information about strict mode, see the MySQL topic Server SQL Modes<\/a>.<\/em><\/p>\n
At times, you might want to define a column to permit NULL<\/code> values, in which case, you can specify NULL<\/code> in the column definition, or you can omit the nullability option. (Many database teams prefer to include default settings in schema definitions as part of best practices.) The following CREATE<\/code> TABLE<\/code> statement sets the engine_type<\/code> column to NULL<\/code>:<\/p>\n
CREATE TABLE airplanes (\r\n  plane_id INT UNSIGNED NOT NULL,\r\n  alt_id INT UNSIGNED NOT NULL,\r\n  plane VARCHAR(50) NOT NULL,\r\n  engine_type VARCHAR(50) NULL,\r\n  engine_count TINYINT NOT NULL,\r\n  PRIMARY KEY (plane_id));<\/pre>\n
Now try to insert a row into the table without specifying the engine_type<\/code> value:<\/p>\n
INSERT INTO airplanes \r\n  (plane_id, alt_id, plane, engine_count)\r\nVALUES (1001,173,'A340-600',4);<\/pre>\n
This time, MySQL will insert NULL<\/code> for the column\u2019s value, which you can confirm by querying the airplanes<\/code> table:<\/p>\n
SELECT * FROM airplanes;<\/pre>\n
The following figure shows the results returned by the SELECT<\/code> statement. As you can see, the engine_type<\/code> value is set to NULL<\/code>.<\/p>\n
<\/p>\n
The debate over whether to support NULL<\/code> values in a relational database has been going on for years (along with the debate about what NULL<\/code> means). The extent to which you use NULL<\/code> values will depend on the policies that your team has adopted. If you\u2019re trying to limit the use of NULL<\/code>, you can sometimes accommodate unknown values by adding DEFAULT<\/code> constraints to your column definitions.<\/p>\n
DEFAULT<\/strong> constraints<\/h2>\n
When creating or updating a table, you can add DEFAULT<\/code> constraints to your column definitions. A DEFAULT<\/code> constraint specifies the value to use for a column when an INSERT<\/code> statement does not provide the value.<\/p>\n
The default value can be a literal constant or a scalar expression. If you specify an expression, it must adhere to the following rules:<\/p>\n