Collection of SQL statements related to the Mysql primary key

Add the table field altertabletable1addtransactorvarchar (10) notNull; modify the field type of a table and specify the field type as null or non-empty altertable table name change field Name field type [whether to allow

Add the table field alter table table1 add transactor varchar (10) not Null; alter table table1 add id int unsigned not Null auto_increment primary key modify the field type of a table and specify an empty or non-empty alter table name change field Name field type [whether to allow

Add Table Fields

Alter table table1 add transactor varchar (10) not Null;

Alter table table1 add id int unsigned not Null auto_increment primary key

Modify the field type of a table and specify it as null or not

Alter table name change field Name field type [whether to allow non-null];

Alter table name modify Field Name field type [whether to allow non-null];

Alter table name modify Field Name field type [whether to allow non-null];

Modify the field name of a table and specify it as null or not

Alter table name change field Original Name field New Name field type [whether to allow non-null

Delete A Field

Alter table mytable DROP field name;

Add unique key

Alter table 'test2' add unique ('userid ')

Modify primary key

Alter table 'test2' drop primary key, add primary key ('id ')

Add Index

Alter table 'test2' add index ('id ')

Alter table 'category 'modify COLUMN 'id' int (11) not null AUTO_INCREMENT FIRST, add primary key ('id ');

The SQL statement block for modifying the primary key is as follows:

22 declare @ defname varchar (100)
Declare @ cmd varchar (500)
Declare @ tablename varchar (100)
Declare @ keyname varchar (100)
Set @ tablename = 'temp1'
Set @ keyname = 'id' -- key to be Set, separated
Select @ defname = name
FROM sysobjects so
JOIN sysconstraints SC
ON so. id = SC. constid
WHERE object_name (so. parent_obj) = @ tablename
And xtype = 'pk'
If @ defname is not null
Begin
Select @ cmd = 'alter table' + @ tablename + 'drop constraint' + @ defname
-- Print @ cmd
Exec (@ cmd)
End
Else
Set @ defname = 'pk _ '+ @ keyname
Select @ cmd = 'alter table' + @ tablename + 'add constraint' + @ defname + 'Primary KEY tertered ('+ @ keyname + ')'
Exec (@ cmd)

How to get the primary key field name and field type -- get the primary key field name

1:
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE TABLE_NAME <> 'dtproperties'

2:
EXEC sp_pkeys @ table_name = 'table name'

3:
Select o. name as table name, c. name as field name, k. colid as field serial number, k. keyno as index order, t. name as type

From sysindexes I

Join sysindexkeys k on I. id = k. id and I. indid = k. indid

Join sysobjects o on I. id = o. id

Join syscolumns c on I. id = c. id and k. colid = c. colid

Join policypes t on c. xusertype = t. xusertype

Where o. xtype = 'U' and o. name = 'name of the table to be queried'

And exists (select 1 from sysobjects where xtype = 'pk' and parent_obj = I. id and name = I. name)

Order by o. name, k. colid