MySQL add a primary key or change the table's column as the primary key SQL statement

                                                                                                                    www.169it.com

Add a table field

ALTER TABLE table1 add transactor varchar (ten) not Null;

ALTER TABLE table1 add ID int unsigned not Null auto_increment primary key

Modify the field type of a table and specify empty or non-empty

ALTER TABLE name change field Name field Name field type [whether non-null allowed];

ALTER TABLE name modify field Name field type [allow non-null];

ALTER TABLE name modify field Name field type [allow non-null];

Modify the field name of a table and specify null or non-empty

ALTER TABLE name change field original Name field new Name field type [whether non-null is allowed

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 (one) not NULL auto_increment First, ADD PRIMARY KEY (' id ');

The SQL statement block that modifies the primary key is as follows:

12345678910111213141516171819202122

declare@defname varchar(100)declare@cmd varchar(500)declare@tablename varchar(100)declare@keyname varchar(100)Set@tablename=‘Temp1‘Set@keyname=‘id‘--需要設置的key,分隔select@defname= name   FROMsysobjects so    JOIN sysconstraints sc   ONso.id = sc.constid   WHEREobject_name(so.parent_obj) = @tablename   andxtype=‘PK‘if @defname isnotnullbeginselect@cmd=‘alter table ‘+ @tablename+ ‘ drop constraint ‘+ @defname--print @cmd   exec(@cmd) endelse set@defname=‘PK_‘[email protected]select@cmd=‘alter table ‘+ @tablename+ ‘ ADD constraint ‘+ @defname +‘ PRIMARY KEY CLUSTERED(‘[email protected]+‘)‘   exec(@cmd)

　How to take 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 ordinal, 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 Systypes T on C.xusertype=t.xusertype

where O.xtype = ' U ' and o.name= ' table name to query '

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

ORDER BY O.name,k.colid

The source of this article: MySQL add a primary key or change the table's column as the primary key SQL statement

MySQL add a primary key or change the table's column as the primary key SQL statement