Syntax knowledge of DDL in MySQL learning 2 (common constraint types)

Article directory

Common [constraints]

Classification: Six major constraints
 ①NOT NULL: non-null, used to ensure that the value of the field cannot be empty
  such as: name, student number

 ②DEFAULT: default, used to ensure the default value of the field
  such as: gender

 ③PRIMARY KEY: [primary key] , used to ensure The value of this field is unique and non-empty,
  such as student number, employee number, etc.

 ④ [UNIQUE] : Unique, used to ensure that the value of this field is unique, and can be empty,
  such as seat number

 ⑤CHECK: [Check constraint] [mysql does not support]
  such as Age, gender

 ⑥ FOREIGN: foreign key, used to limit the relationship between the two tables, used to ensure that the value of this field must come from the value of the associated column of the main
      table Add a foreign key divisor in the slave table, used to refer to a certain table in the main table The value of the column is
  such as the professional ID of the student table, the department ID of the employee table, and the job ID of the employee table

The difference between primary key and unique key

   only is empty Can there be more than one in a table whether to allow combinations
primary key × × √, but not recommended
unique key √, but not recommended

foreign key

Foreign key:
1. It is required to set a foreign key relationship in the slave table
. 2. The type of the foreign key column of the slave table and the associated column type of the master table are required to be consistent or compatible, and the name is not required
. 3. The associated column in the master table must be a key (Usually primary key or unique)
4. When inserting data, insert the main table first, then insert the slave table
5. When deleting data, delete the slave table first, and then delete the main table

When and how to add constraints

When to add constraints:
 1. When creating a table
 2. When modifying a table

Addition of constraints:
 Column-level constraints
  are supported by all six major constraints, but foreign key divisors are useless
 . Table-level constraints
  are non-null. By default, all others are supported.

Sample

CREATE DATABASE IF NOT EXISTS students;
USING students;

CREATE TABLE stuinof(
    id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    gender CHAR(1),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorid INT
);

CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR ( 20 )
);

#2. Add table level constraints 
/*
Syntax: at the bottom of each field
[constraint constraint name] Constraint type (field name)

*/
DROP TABLE IF EXISTS stuinof;

CREATE TABLE stuinof(
    id INT,
    stuName VARCHAR(20) NOT NULL,
    gender CHAR(1),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorid INT,

    CONSTRAINT pk PRIMARY KEY(id),
    CONSTRAINT uq UNIQUE (seat),
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid)REFERENCES major(id)
);

SHOW INDEX FROM stuinof;

Add Constraints When Modifying Tables

/*
1. Add column-level constraints
alter table indicates modify column field name field type new constraint
2. Add table-level constraints
alter table indicates add [constarint constraint name] constraint type (field name) [foreign key reference]
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuName VARCHAR(20),
    gender CHAR(1),
    seat INT ,
    age INT ,
    majorid INT
);
DESC stuinfo;
#1. Add non-null constraint 
ALTER  TABLE stuinfo MODIFY  COLUMN stuName VARCHAR ( 20 ) NOT  NULL ;
 #2. Add default constraint 
ALTER  TABLE stuinfo MODIFY  COLUMN age INT  DEFAULT  18 ;
 #3. Add primary key 
#①Column level constraint 
ALTER  TABLE stuinfo MODIFY  COLUMN  id  INT PRIMARY KEY ;
 #②Table-level constraints 
ALTER  TABLE stuinfo ADD PRIMARY KEY ( id );

#4. Add unique pieces 
#①Column-level constraints 
ALTER  TABLE stuinfo MODIFY  COLUMN  id  INT  UNIQUE ;
 #②Table-level constraints 
ALTER  TABLE stuinfo ADD  UNIQUE ( id );

#5. Add foreign key 
ALTER  TABLE stuinfo ADD  CONSTRAINT kk FOREIGN  KEY (majorid) REFERENCES major( id );

Drop constraint when modifying table

#1. Delete the non-null constraint 
ALTER  TABLE stuinfo MODIFY  COLUMN stuName VARCHAR ( 20 ) NULL ;

#2. Delete the default constraint 
ALTER  TABLE stuinfo MODIFY  COLUMN age INT ;

#3. Delete the primary key 
ALTER  TABLE stuinfo DROP PRIMARY KEY ;

#4. DROP UNIQUE 
ALTER  TABLE stuinfo DROP  INDEX seat;

#4. Delete foreign key 
ALTER  TABLE stuinfo DROP  FOREIGN  KEY kk;

SHOW INDEX FROM stuinfo;

Leave a Comment

Your email address will not be published. Required fields are marked *