How to set non-null constraint in MySQL

MySQL is a popular relational database management system that supports multiple data types and complete data management functions, including optionally setting data columns to "not null". This option allows developers to limit the data columns in the data table to ensure data integrity and consistency and avoid potential errors.

In MySQL, not null is an important constraint. It is usually used to restrict the data in the field from being empty when creating the table. After we set the not null constraint, if we try to add a piece of data with only some non-null columns, MySQL will give back an error and reject it. This behavior effectively avoids some common errors such as undefined values ​​and empty tags.

How to set non-empty constraints in MySQL? Below, we will introduce two common methods.

Method 1: Use the Create Table statement

When creating a data table, you can use the CREATE TABLE statement to set non-null constraints at the same time. For example:

CREATE TABLE example_table (
   id INT(11) NOT NULL,
   username VARCHAR(50) NOT NULL,
   email VARCHAR(100) NOT NULL,
   age INT(3) NOT NULL
);

In the above example, four data columns are marked as not null, which means that inserting any null value will be rejected by MySQL. If you try to insert a null value, for example:

INSERT INTO example_table (id, username, email, age) VALUES (1, 'Alice', 'alice@example.com', null);

MySQL will report an error:

ERROR 1048 (23000): Column 'age' cannot be null

Method 2: Use the ALTER TABLE statement

If we need to insert an existing data To set a non-null constraint on a column, you can use the ALTER TABLE statement. For example, for the table in the previous example, we can use the following statement:

ALTER TABLE example_table MODIFY id INT(11) NOT NULL;

This will add a non-null constraint to the id column and will not allow null values ​​to be inserted into this column.

Summary

Setting non-null constraints in MySQL is a basic database management skill. By setting not null correctly, we can effectively avoid potential errors and ensure data integrity and consistency in the database.

The above is the detailed content of How to set non-null constraint in MySQL. For more information, please follow other related articles on the PHP Chinese website!