SQLServer与MySQL约束/索引命名的一些差异总结

在SQL Server中的约束与索引:

另外,在MySQL中,对于一个Primary Key的列,MySQL已经自动对其建立了Unique Index,无需重复再在上面建立索引了。

对应的索引信息

一、key与primary key区别
CREATE TABLE wh_logrecord (
logrecord_id int(11) NOT NULL auto_increment,
user_name varchar(100) default NULL,
operation_time datetime default NULL,
logrecord_operation varchar(100) default NULL,
PRIMARY KEY (logrecord_id),
KEY wh_logrecord_user_name (user_name)
)
Key是索引约束,对表中字段进行约束索引的,都是通过primary foreign unique等创建的。常见有foreign key,外键关联用的。
KEY forum (status,type,displayorder) # 是多列索引(键)
KEY tid (tid) # 是单列索引(键)。
如建表时: KEY forum (status,type,displayorder)
select * from table group by status,type,displayorder 是否就自动用上了此索引,
而当 select * from table group by status 此索引有用吗?
key的用途:主要是用来加快查询速度的。

图片 1

 

约束是数据库完整性的保证,主要分为:主键/外键/唯一键/默认值/check等类别,
约束是一个逻辑概念,表示数据的某些特性(不能为空,唯一,必须满足某些条件等等),索引是一个逻辑与物理概念的结合,逻辑上是一种数据结构,物理上要占用实实在在的存储空间。
对于主键和唯一键约束,在sqlserver中会自动生成唯一索引,sqlserver中的约束和索引是两个不同的对象,约束就是约束,索引就是索引,主键/唯一约束通过主键/唯一索引实现。
在MySQL中更为直接,对于主键和唯一键,直接定义其primary key和unique key的索引属性即可
在SQL Server和MySQL中,约束与索引在生成的时候,有各自不同的规则和命名方式,以下简单介绍在两种数据库中的特点和差异,以及个人的建议。

 

 图片 2

  1. 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
  2. 如果Key是PRI, 那么该列是主键的组成部分
  3. 如果Key是UNI, 那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)
  4. 如果Key是MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL

1,系统会默认对主键/唯一约束创建主键/唯一索引,
  对于主键约束,不管是否显式给予命名,系统都会忽略这个主键名字,使用PRIMARY替代
  对于非主键约束,如果显式给予命名,系统会采用这个命名,否则使用字段名来当做索引的名字
2,MySQL中,索引的名字仅限于表级别不重复,库级别无要求,
  也就是说一个库中不同的表,可以使用相同的索引名称,当然这里并不是建议或者支持这种方式的使用
3,对于非空约束,系统都不会为NOT NULL约束生成约束的名字
4,对于复合索引(多个字段组成),如果没有显式命名,会采用第一个字段命名
  从这一点来看,未显式命名的情况下,无法做到见名知意,
  也就是说看到索引的名字能够大概知道是一个什么字段的索引,因此在MySQL中,对于非主键索引,建议使用显式命名的方式进行管理
5,删除存在约束的字段的时候,无需先删除约束,直接删除字段即可

 

  不管是在哪种数据库中,对于数据库中某些自定义的对象的命名方式,你不主动做选择就会被默认,被默认就意味着很有可能是被动的。
  既有默认值也有自定义的情况下,自动生成的默认的命名往往不利于维护或者无法做到见名知意,
  在符合规则的前提下,尽可能地去做到自定义(主动而不是被动),以便于后期的维护和管理。

下面是建表的语句:

1,系统会默认对主键/唯一约束创建主键/唯一索引,索引的名字会与约束的名字一致
2,对主键/唯一约束,如果没有指定约束的名字,按照某种规则+随机的方式生成索引名字
3,对于非空约束,不管是否指定了约束的名称,系统都不会为NOT NULL约束生成约束的名字
4,约束或者索引的名字,在数据库级别是唯一的,也就是说A表的约束的名字不能跟B表的约束采用同一个名字,对于索引,是表级别唯一的。
5,在sqlserver中,如果删除一个存在约束的字段,必须要先删除约束,否则报错,参考下图
      如果让约束随机命名,删除约束的时候会比较麻烦,所以建议对于约束要显式命名,sqlserver中,不管是对于约束或者索引,都强烈建议使用显式指定名字的方式命名,不使用系统默认生成的(随机)名字

Note that “primary” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity …)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).

对应的约束信息

 

CREATE TABLE TestTable1
(
  --1,系统会默认对主键/唯一约束创建主键/唯一索引,索引的名字会与约束的名字一致
  Id int identity(1,1) not null constraint pk_Id primary key (Id),
  Name varchar(100) constraint uq_testtable1_name unique,
  --2,对主键/唯一约束,如果没有指定约束的名字,按照某种规则+随机生成索引名字
  Alias varchar (100) unique,
  CreateDate datetime not null constraint df_createdate default getdate(),
  --3,对于非空约束,不管是否指定了约束的名称,系统都不会为NOT NULL约束生成约束的名字
  LastUpdate datetime constraint notnullconstraint not null
)

key的用途:主要是用来加快查询速度的。
[sql] view plain copy
CREATE TABLE `admin_role` (
`adminSet_id` varchar(32) NOT NULL,
`roleSet_id` varchar(32) NOT NULL,
PRIMARY KEY (`adminSet_id`,`roleSet_id`),
KEY `FK9FC63FA6DAED032` (`adminSet_id`),
KEY `FK9FC63FA6C7B24C48` (`roleSet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

总结

CREATE TABLE `phpcolor_ad` (
`id` mediumint(8) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`type` mediumint(1) NOT NULL,
`code` text,
PRIMARY KEY (`id`),
KEY `type` (`type`)
);

图片 3

 

 

primary key与unique的区别
定义了 UNIQUE 约束的字段中不能包含重复值,可以为一个或多个字段定义 UNIQUE 约束。因此,UNIQUE 即可以在字段级也可以在表级定义, 在 UNIQUED 约束的字段上可以包含空值。ORACLE自动会为具有 PRIMARY KEY 约束的字段(主码字段)建立一个唯一索引和一个NOT NULL约束,定义PRIMARY KEY约束时可以为它的索引;
UNIQUED 可空,可以在一个表里的一个或多个字段定义;PRIMARY KEY 不可空不可重复,在一个表里可以定义联合主键;
简单的说,primary key = unique + not null
Primary Key和Unique Key有很多相似之处。但还是有以下区别:
一、作为Primary Key的域/域组不能为null,而Unique Key可以。
二、在一个表中只能有一个Primary Key,而多个Unique Key可以同时存在。
更大的区别在逻辑设计上。Primary Key一般在逻辑设计中用作记录标识,这也是设置Primary Key的本来用意,而Unique Key只是为了保证域/域组的唯一性。
oracle的constraint中有两种约束,都是对列的唯一性限制――unique与primary key,但其中是有区别的:
1、unique key要求列唯一,但不包括null字段,也就是约束的列可以为空且仅要求列中的值除null之外不重复即可;
2、primary key也要求列唯一,同时又限制字段的值不能为null,相当于Primary Key=unique + not null。
创建一个primary key和unique key都会相应的创建一个unique index。
primary key的语法:alter table table name add constraint key name primary key( columns);
unique key的语法:alter table table name add constraint key name unique( columns);
一个表只能有一个主键,但是可以有好多个UNIQUE,而且UNIQUE可以为NULL值,如员工的电话号码一般就用UNIQUE,因为电话号码肯定是唯一的,但是有的员工可能没有电话。
主键肯定是唯一的,但唯一的不一定是主键;
不要总把UNIQUE索引和UNIQUE约束混为一谈
1、primary key = unique + not null
2、唯一约束和主键一样都是约束的范畴,而且都可以作为外键的参考,不同的是,一张表只能有一个主键
3、主键和唯一约束的创建需要依靠索引,如果在创建主键或唯一约束的时候没有已经建好的索引可以使用的话,Oracle会自动建立一个唯一的索引。

CREATE TABLE TestTable1
(
  Id int auto_increment not null ,
  Name varchar(100) ,
  Alias varchar (100) ,
  CreateDate datetime not null default now(),
  LastUpdate datetime,   

 

 

 

  primary key (Id),
  unique key uq_TestTable1_name(Name),
  unique key (Alias),
  key (Name,CreateDate)
 );

如果需要命名 UNIQUE 约束,以及为多个列定义 UNIQUE 约束,请使用下面的 SQL 语法:
[sql] view plain copy
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

图片 4

 

图片 5

如果对于一个列的定义,同时满足上述4种情况的多种,比如一个列既是PRI,又是UNI
那么"desc 表名"的时候,显示的Key值按照优先级来显示 PRI->UNI->MUL
那么此时,显示PRI

在MySQL中的约束与索引

 

四、使用UNIQUE KEY
[sql] view plain copy
CREATE TABLE `secure_vulnerability_warning` (
`id` int(10) NOT NULL auto_increment,
`date` date NOT NULL,
`type` varchar(100) NOT NULL,
`sub_type` varchar(100) NOT NULL,
`domain_name` varchar(128) NOT NULL,
`url` text NOT NULL,
`parameters` text NOT NULL,
`hash` varchar(100) NOT NULL,
`deal` int(1) NOT NULL,
`deal_date` date default NULL,
`remark` text,
`last_push_time` datetime default NULL,
`push_times` int(11) default '1',
`first_set_ok_time` datetime default NULL,
`last_set_ok_time` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `date` (`date`,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

索引用于:

 

 

总结:
Key是索引约束,对表中字段进行约束索引的,都是通过primary foreign unique等创建的。常见有foreign key,外键关联用的。
KEY forum (status,type,displayorder) # 是多列索引(键)
KEY tid (tid) # 是单列索引(键)。

 

三、mysql中UNIQUE KEY和PRIMARY KEY有什么区别

3,撤销 UNIQUE 约束
如需撤销 UNIQUE 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID

 

于是乎,在设计表的时候,Key只是要处于模型层面的,而当需要进行查询优化,则对相关列建立索引即可。

 

MySQL 中Index 与Key 的区别
Key即键值,是关系模型理论中的一部份,比如有主键(Primary Key),外键(Foreign Key)等,用于数据完整性检否与唯一性约束等。
而Index则处于实现层面,比如可以对表的任意列建立索引,那么当建立索引的列处于SQL语句中的Where条件中时,就可以得到快速的数据定位,从而快速检索。
至于Unique Index,则只是属于Index中的一种而已,建立了Unique Index表示此列数据不可重复,猜想MySQL对Unique Index类型的索引可以做进一步特殊优化吧。
于是乎,在设计表的时候,Key只是要处于模型层面的,而当需要进行查询优化,则对相关列建立索引即可。
另外,在MySQL中,对于一个Primary Key的列,MySQL已经自动对其建立了Unique Index,无需重复再在上面建立索引了。
搜索到的一段解释:
Note that “primary” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity …)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).

 

mysql中key 、primary key 、unique key 与index区别
索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。
表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。
如果一个表有1000行,这比顺序读取至少快100倍。注意你需要存取几乎所有1000行,它较快的顺序读取,因为此时我们避免磁盘寻道。

本文由金沙官网线上发布于数据库,转载请注明出处:SQLServer与MySQL约束/索引命名的一些差异总结

您可能还会对下面的文章感兴趣: