MySQL constraints

Time:2021-1-16
  • Concept: limit the data in the table to ensure the correctness, validity and integrity of the data.
    • Classification:

      1. Primary key constraint: primary key
      2. Non NULL constraint: not null
      3. Unique constraint: unique
      4. Foreign key constraint: foreign key
    • Non NULL constraint: not null, value cannot be null

      1. Add constraints when creating tables
        CREATE TABLE stu(
        id INT,
        Name varchar (20) not null — name is not null
        );

      2. After creating the table, add a non empty constraint
        ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

      3. Delete non NULL constraint for name
        ALTER TABLE stu MODIFY NAME VARCHAR(20);

    • Unique constraint: unique, value cannot be repeated

      1. When creating a table, add a unique constraint
        CREATE TABLE stu(
        id INT,
        phone_ Number varchar (20) unique — unique constraint added

        );

        • Note that in mysql, the value of the column restricted by the unique constraint can have multiple nulls
      2. Delete unique constraint

        ALTER TABLE stu DROP INDEX phone_number;

      3. After creating the table, add a unique constraint
        ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

    • Primary key constraint: primary key.

      1. be careful:

        1. Meaning: not empty and unique
        2. A table can only have one field as the primary key
        3. A primary key is the unique identifier of a record in a table
      2. When creating a table, add a primary key constraint
        create table stu(
        Id int primary key, — add primary key constraint to ID
        name varchar(20)
        );

      3. Delete primary key
        –Error alter table stu modify id int;
        ALTER TABLE stu DROP PRIMARY KEY;

      4. After creating the table, add the primary key
        ALTER TABLE stu MODIFY id INT PRIMARY KEY;

      5. Automatic growth:

        1. Concept: if a column is numeric, use auto_ Increment can be used to achieve automatic growth

        2. When creating a table, the primary key constraint is added and the primary key self growth is completed
          create table stu(
          id int primary key auto_ Increment, – add primary key constraint to ID
          name varchar(20)
          );

        3. Delete auto growth
          ALTER TABLE stu MODIFY id INT;

        4. Add auto growth
          ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

    • Foreign key constraint: foreign key, which enables tables to have relations with tables, so as to ensure the correctness of data.

      1. When you create a table, you can add foreign keys

        • Grammar:
          Create table name(
          ….
          Foreign key column
          Constraint foreign key name foreign key references main table name
          );
      2. Delete foreign key
        Alter table name DROP foreign key name;

      3. After creating the table, add the foreign key
        Alter table name add constraint foreign key name foreign key references main table name;

      4. Cascade operation

        1. Add cascade operation
          Syntax: alter table name add constraint foreign key name
          Foreign key (foreign key field name) references main table name (main table column name) on update cascade on delete cascade;
        2. Classification:
          1. Cascade update: on update cascade
          2. Cascade delete: on delete cascade

Recommended Today

020_CSS3

catalog How to learn CSS What is CSS History of development quick get start Advantages of CSS Three ways to import CSS Expansion: two ways of writing external style selector Basic selector Hierarchy selector Structure pseudo class selector attribute selectors Beautify web page elements Why beautify web pages Span label: for the text that needs […]