How to Give Acceptable Values While Creating a Table in MySQL

Описание: Discover how to set up `acceptable values` for a column in MySQL when creating a table, with practical tips and step-by-step instructions!
---
This video is based on the question https://stackoverflow.com/q/67546611/ asked by the user 'rider366' ( https://stackoverflow.com/u/15934284/ ) and on the answer https://stackoverflow.com/a/67546632/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to give acceptable values while creating a table in mysql?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Give Acceptable Values While Creating a Table in MySQL

Creating a table in MySQL often involves defining various columns, each with its own data type and constraints. One common challenge developers encounter is how to restrict a column to only accept certain predefined values. For example, you might want a category column that only allows the entries 'S', 'M', 'C', or 'D'. In this blog, we’ll explore how to implement these constraints effectively using MySQL's check constraints.

Understanding the Problem

Let's consider a scenario where you want to create an ItemCategory table. The requirements for this table are simple:

The ItemCategory column must be a primary key.

It should only accept specific values: 'S', 'M', 'C', and 'D'.

There should also be a second column, CategoryName.

Without enforcing these constraints, you risk the integrity of your data, allowing unwanted entries that could lead to confusion and errors in your applications.

Solution: Using CHECK Constraints

Starting from recent versions of MySQL, you can use a CHECK constraint to enforce acceptable values for a specific column while creating or altering a table. This is how you can do it:

Step-by-Step Instructions to Create the Table

Create the Table: First, define your table and its columns. Ensure you include your primary key.

Apply the CHECK Constraint: Use the CHECK constraint to allow only specific values for the given column.

Here’s how you can achieve that in SQL code:

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Code

CREATE TABLE ItemCategory (...): This line initiates the creation of the ItemCategory table.

ItemCategory CHAR(1) PRIMARY KEY: This line defines a column called ItemCategory, which will store a single character and serve as the primary key.

CategoryName VARCHAR(50): This column will store the names of the categories.

CHECK (ItemCategory IN ('S', 'M', 'C', 'D')): This line ensures that the values inserted into the ItemCategory column must be one of the specified characters (S, M, C, D) only.

Altering an Existing Table

If you already have the ItemCategory table created and wish to add the CHECK constraint afterward, you can utilize the following SQL command:

[[See Video to Reveal this Text or Code Snippet]]

ALTER TABLE ItemCategory: Indicates that we want to modify the existing table.

ADD CONSTRAINT check_category: This part specifies that we are adding a new CHECK constraint and giving it an identifiable name (for future reference).

CHECK (ItemCategory IN ('S', 'M', 'C', 'D')): States what the constraint is enforcing.

Conclusion

Implementing constraints in your MySQL tables is critical for maintaining data integrity. By utilizing CHECK constraints, you can limit the values allowed in a column in a straightforward and effective manner. This not only simplifies data management but also reduces the chances of errors in your database applications.

By following the steps outlined in this post, you can ensure your ItemCategory table only accepts the values intended, ultimately leading to a better structured and more reliable database.

Feel free to reach out if you have any questions or need further clarification on using MySQL constraints!