mysql

Create table with constraints MySQL query

In MySQL, a constraint is a rule that must be followed by data in a database table. Constraints are used to limit the type of data that can be stored in a column, to ensure data integrity, and to enforce business rules on the data.

CREATE TABLE table_name(
    Id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    Column1 char(40) DEFAULT NULL,
    Column2 varchar(100) DEFAULT '',
    Date timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(Id),
    UNIQUE (Column1)
)

We have created a table which has four columns. 'Id' column has primary key constraints and it can not be null. 'Column1' has a character data type and it is set to null by default, a UNIQUE constraint is also assigned to it. 'Column2' has varchar datatype and it is set to blank if no value is provided while insertion. 'Date' column has a datatype of timestamp and if you will not provide any value to it, it will insert current timestamp by default.

There are several different types of constraints that can be used in MySQL, including:

NOT NULL - ensures that a column cannot be left blank

UNIQUE - ensures that all values in a column are unique

PRIMARY KEY - a column that uniquely identifies each row in a table

FOREIGN KEY - a column that is a reference to a primary key in another table

CHECK - ensures that all values in a column meet a specific condition

DEFAULT - sets a default value for a column

Was this helpful?