Example statements for indexes and constraints in MySQL

Foreign Keys

Query which tables the primary key of a table is the foreign key of

SELECT
	TABLE_NAME,
	COLUMN_NAME,
	CONSTRAINT_NAME,
	REFERENCED_TABLE_NAME,
	REFERENCED_COLUMN_NAME
FROM
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
	TABLE_SCHEMA = 'mydbname'
	AND REFERENCED_TABLE_NAME = 'Table name';

Export all foreign key statements

SELECT
	CONCAT('ALTER TABLE ', TABLE_NAME, ' ADD CONSTRAINT ', CONSTRAINT_NAME, ' FOREIGN KEY (', COLUMN_NAME, ') REFERENCES ', REFERENCED_TABLE_NAME, '(', REFERENCED_COLUMN_NAME, ') ON DELETE CASCADE ON UPDATE CASCADE;')
FROM
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
	TABLE_SCHEMA = 'mydbname'
	AND REFERENCED_TABLE_NAME IS NOT NULL;

Delete all foreign key statements

SELECT
	CONCAT('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
FROM
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
	TABLE_SCHEMA = 'mydbname'
	AND REFERENCED_TABLE_NAME IS NOT NULL;

Auto-increment

Export the statement for creating an auto-increment field

SELECT
	CONCAT( 'ALTER TABLE `', TABLE_NAME, '` ', 'MODIFY COLUMN `', COLUMN_NAME, '` ', UPPER( COLUMN_TYPE ), ' NOT NULL AUTO_INCREMENT COMMENT "',COLUMN_COMMENT,'";' ) as 'ADD_AUTO_INCREMENT'
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = 'mydbname' 
	AND EXTRA = UPPER( 'AUTO_INCREMENT' ) 
ORDER BY
	TABLE_NAME ASC;

Create and delete all auto-increment fields

SELECT
	CONCAT( 'ALTER TABLE `', TABLE_NAME, '` ', 'MODIFY COLUMN `', COLUMN_NAME, '` ', UPPER( COLUMN_TYPE ), ' NOT NULL;' ) as 'DELETE_AUTO_INCREMENT'
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = 'mydbname' 
	AND EXTRA = UPPER( 'AUTO_INCREMENT' ) 
ORDER BY
	TABLE_NAME ASC;

index

Export all indexes

SELECT
	CONCAT(
		'ALTER TABLE `',
		TABLE_NAME,
		'` ',
		'ADD ',
	IF
		(
			NON_UNIQUE = 1,
		CASE
				UPPER( INDEX_TYPE ) 
				WHEN 'FULLTEXT' THEN
				'FULLTEXT INDEX' 
				WHEN 'SPATIAL' THEN
				'SPATIAL INDEX' ELSE CONCAT( 'INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE ) 
			END,
		IF
			(
				UPPER( INDEX_NAME ) = 'PRIMARY',
				CONCAT( 'PRIMARY KEY USING ', INDEX_TYPE ),
			CONCAT( 'UNIQUE INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE ))),
		CONCAT( '(`', COLUMN_NAME, '`)' ),
		';' 
) AS 'ADD_ALL_INDEX' 
FROM
	information_schema.STATISTICS 
WHERE
	TABLE_SCHEMA = 'mydbname' 
ORDER BY
	TABLE_NAME ASC,
	INDEX_NAME ASC;

Delete all indexes

SELECT
	CONCAT( 'ALTER TABLE `', TABLE_NAME, '` ', CONCAT( 'DROP ', IF ( UPPER( INDEX_NAME ) = 'PRIMARY', 'PRIMARY KEY', CONCAT( 'INDEX `', INDEX_NAME, '`' ))), ';' ) AS 'DELETE_ALL_INDEX' 
FROM
	information_schema.STATISTICS 
WHERE
	TABLE_SCHEMA = 'mydbname' 
ORDER BY
	TABLE_NAME ASC;

Data Merge

When migrating and merging data, the more difficult thing is that the primary keys of different databases are repeated, so we have to modify the primary key values in batches. In order to avoid duplication, we can change the auto-increment number to a string

The steps are basically as follows

Cancel primary key auto-increment
Drop all foreign keys
Change the primary key field to varchar
Add all foreign keys
Modify the value of a primary key
Merge data

Be careful when modifying primary key values

If the value cannot be modified directly in the case of self-associations such as id and pid, you need to delete the constraint first and then add it.

for example

Deleting Self Constraints

ALTER TABLE `t_director` DROP FOREIGN KEY `fk_directorpid`;

Modify value

update t_director set directorid=directorid+100000000;
update t_director set directorid=CONV(directorid,10,36);

update t_director set directorpid=directorpid+100000000 WHERE directorpid is not null;
update t_director set directorpid=CONV(directorpid,10,36) WHERE directorpid is not null;

Adding Self Constraints

ALTER TABLE t_director ADD CONSTRAINT fk_directorpid FOREIGN KEY (directorpid) REFERENCES t_director(directorid) ON DELETE CASCADE ON UPDATE CASCADE;

Notice

The last two parameters of CONV(directorpid,10,36) are the original digital base and the base to be converted.
The first parameter can be converted even if the type is varchar as long as the content is a number.

The above is the details of the sample statements for indexes and constraints in Mysql. For more information about MySQL indexes and constraints, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: