无法删除主键!

不要迷恋_哥 2016-06-12 05:02:07

1,create table c(id int primary key);
2,alter table c drop primary key;
3,alter table c add unique key(id);
4,desc c;
问题是:id字段主键被删除,然后添加唯一约束unique, 在查看表结构,发现id 依然是PRI 没有变成UNI

插入数据验证了,不能插入null. 表中没有任何的数据.

请问一下,怎么会出现这种情况??

...全文

497 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

中国风 2016-06-13

打赏
举报

你用desc区分不出来用这个语句查看


SELECT  CONSTRAINT_CATALOG, 
 CONSTRAINT_SCHEMA, 
 CONSTRAINT_NAME, 
 TABLE_SCHEMA, 
 TABLE_NAME, 
 CONSTRAINT_TYPE  
 FROM 
 information_schema.TABLE_CONSTRAINTS 
 WHERE 
 TABLE_NAME='表名';

不要迷恋_哥 2016-06-13

打赏
举报

引用 6 楼 wmxcn2000 的回复:


-- 感觉这个就是一个 bug ，或者说是显示的错误 

mysql> create table c(id int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> desc c ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table c drop primary key;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table c add unique key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> alter table c add cc varchar(10) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| cc    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table c ;
+-------+--------------------------------------------------------------------
---------------------------------------------------------------------+
| Table | Create Table
                                                                     |
+-------+--------------------------------------------------------------------
---------------------------------------------------------------------+
| c     | CREATE TABLE `c` (
  `id` int(11) NOT NULL,
  `cc` varchar(10) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------
---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table c add primary key(cc) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | UNI | NULL    |       |
| cc    | varchar(10) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table c drop column cc ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

我也是这样想的.......但是不应该吧....

不要迷恋_哥 2016-06-13

打赏
举报

引用 8 楼 roy_88 的回复:

你用desc区分不出来用这个语句查看


SELECT  CONSTRAINT_CATALOG, 
 CONSTRAINT_SCHEMA, 
 CONSTRAINT_NAME, 
 TABLE_SCHEMA, 
 TABLE_NAME, 
 CONSTRAINT_TYPE  
 FROM 
 information_schema.TABLE_CONSTRAINTS 
 WHERE 
 TABLE_NAME='表名';

这个太麻烦了用 show index from 表名 ; //也可可以查出来..就是感觉desc是不是不可靠啊?

kingtiy 2016-06-12

打赏
举报

搞了半天原来是mysql.这个语法不是很了解.

卖水果的net 2016-06-12

打赏
举报


-- 感觉这个就是一个 bug ，或者说是显示的错误 

mysql> create table c(id int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> desc c ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table c drop primary key;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table c add unique key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> alter table c add cc varchar(10) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| cc    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table c ;
+-------+--------------------------------------------------------------------
---------------------------------------------------------------------+
| Table | Create Table
                                                                     |
+-------+--------------------------------------------------------------------
---------------------------------------------------------------------+
| c     | CREATE TABLE `c` (
  `id` int(11) NOT NULL,
  `cc` varchar(10) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------
---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table c add primary key(cc) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | UNI | NULL    |       |
| cc    | varchar(10) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table c drop column cc ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc c;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

不要迷恋_哥 2016-06-12

打赏
举报

引用 2 楼 kingtiy 的回复:

删除主键的语法不对. 主键是一种约束,应该使用drop constraint方式删除.

mysql中好像不能这么写啊> ...报错..

不要迷恋_哥 2016-06-12

打赏
举报

引用 1 楼 roy_88 的回复:


GO
CREATE TABLE Tab2(ID INT CONSTRAINT PK_Tab1 PRIMARY KEY);
GO

ALTER TABLE dbo.Tab2 DROP CONSTRAINT PK_Tab1;

在Mysql中好像不能这么写啊?

kingtiy 2016-06-12

打赏
举报

删除主键的语法不对. 主键是一种约束,应该使用drop constraint方式删除.

中国风 2016-06-12

打赏
举报

对象命名要规范，定义好主健约束名，不定义时会产生随机名选中表名，用alt+f1，查看主健名

中国风 2016-06-12

打赏
举报


GO
CREATE TABLE Tab2(ID INT CONSTRAINT PK_Tab1 PRIMARY KEY);
GO

ALTER TABLE dbo.Tab2 DROP CONSTRAINT PK_Tab1;

删除无主键重复语句删除无主键重复语句删除无主键重复语句删除无主键重复语句

oracle删除非主键重复类型的数据，oracle删除非主键重复类型的数据

删除主键约束( drop PRIMARY key)

PRIMARY KEY 约束标识数据库表中的每条记录。主键必须包含的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。主键约束操作包含了添加约束和删除约束，修改约束其实是添加约束或者删除约束。添加主键约束比较简单，但是删除一个没有约束名的主键约束则比较复杂，如果不是很不了解SQL Server的话则比很难实现该功能。　　主键约束操作基本语句　　如果是在新建表的时候添加主键约束比较简单，直接在列名后加入primary key即可，标准的添加主键约束的SQL脚本如下：　　create table 表名 ( 字段名1 int not null，

多主键删除(ssh自己写的并整理一共大家参考,不需此行

疑难问题

22,209

社区成员

121,730

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章