Learning objectives:

1. Modify field related information

2. Modify constraints

3. Modify other options

4. Modify table name

Learning content:

Create table

Syntax:

create table if not exists table name(

Field type constraints,

...

[constraint information]# last line information without comma

)engine=InnoDB[MyIASM] default charset=utf8;

For example:

1 CREATE TABLE IF NOT EXISTS student(
2 id INT PRIMARY KEY,
3 NAME CHAR(5) NOT NULL,
4 contact INT UNIQUE,
5 number INT UNIQUE AUTO_INCREMENT,
6 CONSTRAINT notn_contact UNIQUE (NAME)
7 );

Modify field

Add field: alter table name add field name data type [new constraint] [first | after field name]

For example:

ALTER TABLE student ADD gender ENUM('male','female') DEFAULT 'male' AFTER NAME;

Delete field: alter table name drop field name;

ALTER TABLE student DROP gender; 

Modify field name or data type:

alter table name change old field name new field name data type;

ALTER TABLE student CHANGE NAME s_name VARCHAR(5);

alter table name modify field name data type;

ALTER TABLE student MODIFY NAME CHAR(5);

Modify constraints

 1 CREATE TABLE teacher(
 2 id INT PRIMARY KEY,
 3 NAME CHAR(5) NOT NULL
 4 );
 5 DROP TABLE student;
 6 CREATE TABLE student(
 7 id INT,
 8 stu_id INT,
 9 NAME CHAR(5)
10 );

Add constraint: alter table name add [constraint constraint constraint name] constraint type (constrained field);

Delete constraint: alter table name drop constraint type constraint name;

Add primary key constraint:

alter table name add constraint constraint constraint name primary key (constrained field);

alter table table name add primary key (constrained field);

Delete primary key constraint:

alter table name drop primary key;

1 ALTER TABLE teacher DROP PRIMARY KEY;
2 ALTER TABLE teacher ADD CONSTRAINT pk_id  PRIMARY KEY (id);
3 ALTER TABLE student ADD PRIMARY KEY (id);
4 SHOW CREATE TABLE teacher;
5 SHOW  CREATE TABLE student;

Add unique constraint:

alter table name add constraint constraint constraint name unique (constrained field);

alter table table name add unique (constrained field);

Delete unique constraint:

alter table name drop index constraint name;

ALTER TABLE teacher ADD UNIQUE (NAME);
ALTER TABLE student ADD CONSTRAINT uk_name UNIQUE (NAME);
ALTER TABLE teacher DROP INDEX NAME;#UNIQUE or INDEX is not used when deleting, because the UNIQUE constraint in MySQL is implemented through INDEX;
ALTER TABLE student DROP INDEX uk_name;

Add foreign key constraint:

alter table name add constraint constraint constraint name foreign key (primary table constrained field) references sub table (primary key field);

alter table table name add foreign key (primary table constrained field) references sub table (primary key field);

To delete a foreign key constraint:

alter table name drop foreign key constraint name;

1 ALTER TABLE student ADD CONSTRAINT fk_stu_teacher FOREIGN KEY (stu_id) REFERENCES teacher (id);
2 ALTER TABLE student ADD FOREIGN KEY (stu_id) REFERENCES teacher (id);
3 SHOW  CREATE TABLE student;
4 ALTER TABLE student DROP FOREIGN KEY fk_stu_teacher;

Learning and Thinking:

1. What is the relationship between unique, key and index in MySQL?

Learning development: