Summer self-study day 11 | database (IV) – constraints

Time:2021-10-24

constraint

  • Limit the data of the table to ensure the correctness, effectiveness and integrity of the data.
  • Category: add after data name

Primary key constraint

  • Primary key, non empty and unique, is the unique identification of the data
  • Delete:ALTER table t_name DROP PRIMARY KEY;
  • Note that primary key deletion is different from non empty deletion. Modify is used for non empty deletion.
  • Self growthAUTO INCREMENT: the value of automatic growth is only related to the previous record

Non NULL constraint

  • Not null, value cannot be empty

Unique constraint

  • Unique, the value cannot be repeated
  • Delete:ALTER table t_name DROP INDEX id;

Foreign key constraint

  • In a master-slave table relationship, if the slave table data is deleted, the data of a column in the master table will also be affected due to the association relationship. By adding foreign key constraints, data cannot be easily deleted from the table.Let the relationship between tables exist to ensure the correctness of data.
  • Format:Foreign key column, constraint foreign key name foreign key reference main table name;
  • An example:
--One party main table, department table
cascade
  • When changing the foreign key ID, cascade can be used to update the foreign key values associated with the master-slave table.
  • Add cascade: on update cascade
    • ALTER TABLE employ ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCE department(id) ON UPDATE CASCADE;
  • Delete cascade: on delete cascade

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Recommended Today

SQL statement of three-level linkage of provinces, cities and counties

The first is the table creation statement Copy codeThe code is as follows: CREATE TABLE `t_address_province` ( `id` INT AUTO_ Increment primary key comment ‘primary key’,`Code ` char (6) not null comment ‘province code’,`Name ` varchar (40) not null comment ‘province name’)Engine = InnoDB default charset = utf8 comment = ‘province information table’; CREATE TABLE […]