Constraints are used to set a rule to allow or add restrictions on database tables in MYSQL. That decides what values should be stored in columns.
- Constraints are used to identify unique records in the database table.
- Constraints help to set limit the value which should be entered into the column.
Constraints in MYSQL
- Primary Key constraint
- Foreign Key constraint
- Unique Key constraint
- check constraints
Primary key Constraint in MYSQL
It is used to define, each record in a table, and its primary condition is that it should have a unique value.
CREATE TABLE roles( role_id INT AUTO_INCREMENT, role_name VARCHAR(50), PRIMARY KEY(role_id) );
Foreign key Constraint in MYSQL
Foreign key is used in the table which points the primary key to another table.
CREATE TABLE products( prd_id int not null auto_increment primary key, prd_name varchar(355) not null, prd_price decimal, cat_id int not null, FOREIGN KEY fk_cat(cat_id) REFERENCES categories(cat_id) ON UPDATE CASCADE ON DELETE RESTRICT )ENGINE=InnoDB;
Unique key Constraint in MYSQL
It is used when you want to identify unique records in the database table.
ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);
Check Constraint in MYSQL
When you want to limit the value which should be entered into the column, MySQL check constraints can be used.
ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL CHECK (AGE >= 18 );