SoFunction
Updated on 2025-03-09

Detailed explanation of Mysql method of adding, deleting, and primary key (foreign key)

Primary key

The primary key will automatically add a unique index, so the primary key column does not need to add an index

Set the primary key when creating a table

Method 1.

create table student(
	id int primary key
);

Method 2.

create table student(
	id int,
	primary key (id)
);

Set the primary key separately

alter table student add primary key(id)

Delete the primary key

alter table student drop primary key;

Foreign keys

A foreign key refers to referring to one or more columns of data in another table. The referenced column should have a primary key constraint or a unique constraint (simply put, the foreign key is the primary key or unique constraint of another table). Foreign keys can have duplicates, can be null values, used to establish and strengthen the connection between two table data.

Use of foreign keys

The use of foreign keys requires the following conditions:

1. Both tables must be InnoDB tables, and they do not have temporary tables.

2. The corresponding columns that establish a foreign key relationship must have similar InnoDB internal data types.

3. The corresponding columns that establish foreign key relationships must be indexed.

There are two ways to create foreign keys:

Method 1: Add when creating a table

Method 2: The table has been created, continue to modify the table structure to add foreign keys.

Add foreign keys when creating tables

Method 1.

create table teacher(
	student_id int not null,
	foreign key(student_id) references student(id)
);

alter table teacher add constraint teacher_ref_student(foreignKeyName)
 foreign key(student_id) references student(id);

Method 2.

Table 1

create table student(	
		id int primary key,   --Student's student number
)comment 'Student Table';

Table 2

create table score(
studentID	int,
coureseID int,		
scoreID int primary key,	
foreign	key(studentID) references student(id)  --Add a foreign key 
)comment 'Grade Table'

Add foreign keys separately

Syntax: alter table table name add foreign key (column name) references associated table name (column name);

eg1:

alter table teacher add constraint teacher_ref_student(foreignKeyName)
 foreign key(student_id) references student(id);

eg2:

alter table student	add foreign key (id) references score(studentID);

Delete foreign keys

Syntax: alter table table name drop foreign key foreign key name;

eg1:

alter table score drop foreign key score_1;

eg2:

alter table tableName1 drop foreign key foreignKeyName;

Unique key

Unique keys will automatically add unique indexes, so unique key columns do not need to add indexes

Normal index

Create a table

create table member(id int(10) ,name varchar(10) ,age int(18) ,phone int(11) ,address varchar(50),);
 
insert into member values (1,'a','19','111','Ganzhou');
insert into member values (2,'b','24','222','Yudu');
insert into member values (3,'c','20','333','Jiujiang');
insert into member values (4,'d','18','444','Nanchang');
insert into member values (5,'e','16','555','Jian');

Method 1: Create an index directly

 CREATE INDEX Index name ON Table name (List name(length));
  • (Column name (length)): length is optional, the same below. If the value of length is ignored, the value of the entire column is used as the index. If specified, use the first length characters of the column to create the index, which is helpful to reduce the size of the index file. Without loss of accuracy, the shorter the length, the better.
  • The index name is recommended to end with "index".
 create index name_index on member(name);  bynameFields create normal index ​
 create index cardid_index on member(cardid(4));  SpecifycardidBefore the field value4The value of a character that is used as a normal index

Method 2: Modify the table method to create an index

ALTER TABLE Table name ADD INDEX Index name (column name);

ALTER TABLE member ADD INDEX phone_index(phone);   byphoneFields create normal index

Method 3: Specify the index when creating a table

Generally, not adding indexes when creating tables will slow down the insertion of data.

  • CREATE TABLE table name (field 1 data type, field 2 data type [,...], INDEX index name (column name));

create table member(id int(10) ,name varchar(10) ,cardid int(18), phone int(11) ,address varchar(50),
INDEX name_index(name));

Delete the index:

 drop index name_index on member;      --Delete the index directly​
 drop index cardid_index on member;    --Delete the index directly​
 alter table member DROP index phone_index;   --Delete indexes by modifying tables

Unique index (create a unique key, create a unique index)


Unique index: Similar to normal indexes, but the difference is that each value of a unique index column is unique. Unique index Allows null values ​​(note that they are different from the primary key). If created with a combination index, the combination of column values ​​must be unique. Adding a unique key will automatically create a unique index.

Creating a unique key or creating a unique index can be achieved.

Method 1: Create a unique index directly

CREATE UNIQUE INDEX Index name ON Table name (field name);

create unique index address_index on member (address);

Method 2: Create by modifying the table

ALTER TABLE table name ADD UNIQUE index name (field name);

alter table member add unique phone_index(phone);

Method 3: Specify the index when creating a table

CREATE TABLE table name (field 1 data type, field 2 data type [...], UNIQUE index name (field name));

create table member(id int(10) ,name varchar(10) ,cardid int(18) ,phone int(11) ,address varchar(50),unique phone_index(phone));

Primary key index (same as creating primary keys)

A primary key index is a special unique index that must be specified as "PRIMARY KEY". A table can only have one primary key and no null values ​​are allowed. Adding a primary key will automatically create a primary key index.

Method 1: Add the primary key when creating the table

create table table name (field 1 XXX, field 2 XXX, ...primary key(field));
create table table name (field 1 XXX primary key, ...);   Take the primary key as the attribute of field 1

create table student(id int(10),name varchar(10),primary key(id));
 ​
create table student(id int(10) primary key,name varchar(10));

Method 2: Add primary key to the existing table

ALTER TABLE table name add primary key (field name);

ALTER TABLE member add primary key(id);    --WillidAdd fields as primary key

Combined index (single column index and multiple column index)

Combined index (single-column index and multiple-column index): can be an index created on a single column or an index created on multiple columns.

The leftmost principle needs to be met, because the where conditions of the select statement are executed from left to right in sequence, so when querying using the select statement, the field order used by the where conditions must be consistent with the sort in the combined index, otherwise the index will not take effect.

Method 1: Create an index directly

CREATE INDEX Index name on table name (field 1, field 2, field 3);

create index name_cardid_phone_index on member3(name,cardid,phone);   use3Create a composite index for each field
alter table member3 drop index name_cardid_phone_index;   Delete the combined index
Follow the leftmost principle

Method 2: Create index by modifying the table

alter table table name add index index index name (field 1, field 2, ..., field n);

 alter table member add index phone_name_cardid_index(phone,name,cardid);

Method 3: Specify the index when creating the table

CREATE TABLE table name (column name 1 data type, column name 2 data type, column name 3 data type, INDEX index name (field 1, field 2, field 3));

create table member(id int(10),name varchar(10),phone int(11),index name_phone_index(name,phone));

Summarize

This is the article about Mysql adding, deleting, and primary key (foreign key) methods. For more related content related to Mysql adding, deleting primary key and foreign key content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!