SQLServe联合主键、联合索引、唯一索引，聚集索引，和非聚集索引、主键、唯一约束和外键约束、索引运算总结

我不是女神ヾ 2024-02-23 06:35 160阅读 0赞

#### 目录 ####

*  联合主键
 *   *  SQL server 中给表增加联合主键的两种方法
     *   *  第一种方法，新建表时增加联合主键：
         *  第二种方法，给已有表增加联合主键：
     *  SQL Server联合主键的影响与利用
 *  联合索引
 *   *  创建联合索引
     *  使用联合索引
     *  联合索引的注意事项
 *  索引（聚集索引，和非聚集索引）
 *   *  索引的创建
     *  聚簇索引和非聚簇索引区别
     *  聚集索引和主键
 *  主键
 *   *  创建主键
     *  设置主键并置为非空
     *  更改已经建好的表中主键列的字符类型
     *  查找主键名
     *  删除主键
 *  唯一索引
 *   *  创建包含一列的唯一约束
     *  创建包含多列的唯一约束
     *  SQL Server唯一索引与NULL
 *  创建、删除索引
 *  约束
 *   *  SQL Server 创建唯一约束和外键约束
     *  唯一约束
     *  外键约束
 *  索引运算总结
 *  参考文章

## 联合主键 ##

### SQL server 中给表增加联合主键的两种方法 ###

#### 第一种方法，新建表时增加联合主键： ####

create table t_students(
    	id int not null,
    	name varchar(10) not null Primary Key (id, name),
    	age int,
    	dept_id int
    )

注：联合主键的列需要限制非空约束。

#### 第二种方法，给已有表增加联合主键： ####

create table t_dept(
    	dept_id int,
    	dept_name varchar(20),
    	stamp varchar(100)
    )
    --先设置非空约束
    Alter table t_dept alter column dept_id int not null 
    Alter table t_dept alter column dept_name varchar(20) not null 
    --增加主键
    Alter table t_dept add constraint pk_t_dept primary key( dept_id, dept_name)

### SQL Server联合主键的影响与利用 ###

SQL Server联合主键是数据库管理系统中各种表中最为重要的设计元素。它是包括多个字段的一组，用于标识数据表中的唯一记录，并保护数据库完全性和完全性。联合主键可以在SQL Server中以区别的方式实现，具体的SQL查询可用于在数据库系统中创建或更新联合主键。

SQL Server联合主键的使用有以下影响：

1.  保护完全性：联合主键的最重要的功能之一是用于保护表中的记录的完全性。例如，可以将表中的信息链接起来，以避免违背业务规则。
2.  提高查询性能：SQL Server中，联合主键可以帮助优化数据库查询，使复杂或那些特定字段频繁查询的查询更有效，从而提高查询性能。
3.  增强安全性：使用联合主键可以禁止未经授权的访问，并避免意外地更改或删除数据库中的信息。

在SQL Server中，可使用以下语句创建联合主键：

CREATE TABLE TableName (
       [Fields...] 
       CONSTRAINT PK_TableName PRIMARY KEY (Field1,Field2,.....FieldN)
    );

上述查询会在表中创建一个联合主键，其中包括Field1、Field2等字段。

SQL Server的联合主键可以为表和数据库系统提供许多实用的功能，但**过度使用联合主键也可能带来一些缺点，例如性能问题和设计问题。因此，使用联合主键时要特别谨慎，确保能够从中取得最大的好处。**

同一个表中联合主键和唯一主键只能设置其中之一，不能即存在联合主键又存在唯一主键

## 联合索引 ##

在数据库中，索引是一种提高查询性能的重要机制。SQL Server提供了多种类型的索引，其中联合索引是其中一种非常常用的索引类型。下面将介绍SQL Server联合索引的定义、使用方式以及使用联合索引的注意事项。  
**联合索引，也被称为复合索引或多列索引，是一种包含多个列的索引**。与单列索引相比，联合索引可以根据多个列的值进行排序和搜索，从而提高查询性能。联合索引遵循**最左匹配原则**，即索引的第一个列将被视为最重要的列，其次是第二个列，以此类推。

### 创建联合索引 ###

在SQL Server中，可以使用CREATE INDEX语句来创建联合索引。下面是一个创建联合索引的示例：

CREATE INDEX idx_name ON table_name (column1, column2, column3);

在上述示例中，idx\_name是索引的名称，table\_name是表的名称，column1, column2, column3是需要包含在索引中的列名。可以根据实际需要定义包含的列数。

### 使用联合索引 ###

**联合索引在查询时可以提供更高的性能。当查询条件中涉及到联合索引的列时，数据库引擎可以直接使用索引来查找匹配的行，而无需扫描整个表。**

下面是一个使用联合索引的查询示例：

SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;

在上述示例中，假设column1和column2是联合索引的前两个列。数据库引擎可以直接使用联合索引来查找column1 = value1和column2 = value2的匹配行。

### 联合索引的注意事项 ###

使用联合索引时，需要注意以下几点：

列的顺序
    联合索引的列顺序非常重要。应将最常用的列放在最左侧，这样可以更好地利用索引提供的性能优势。如果查询条件只涉及到索引的前几个列，那么查询性能将会更好。
    
    列的选择
    虽然可以在联合索引中包含多个列，但并不是所有的列都适合创建索引。应选择那些经常用于查询条件的列来创建索引，以最大程度地提高查询性能。
    
    索引维护开销
    创建联合索引会增加数据库的维护开销。每当插入、更新或删除行时，数据库引擎都需要更新相关的索引。因此，在创建联合索引时，需要权衡索引的性能提升和维护开销之间的关系。

**类图**  
下面是一个简单的类图，展示了联合索引的概念：  
![在这里插入图片描述][c35f7502775b499ca303eb348c4fd84d.png]

在上述类图中，Table类表示数据库中的表，包含表的名称、列和索引。Index类表示索引，包含索引的名称和列。

**结论**  
联合索引是SQL Server中一种非常常用的索引类型，**可以提高查询的性能。通过合理地选择和使用联合索引，可以使数据库的查询操作更加高效。然而，在使用联合索引时需要注意列的顺序、选择以及索引维护开销等因素。**

## 索引（聚集索引，和非聚集索引） ##

**索引加快检索表中数据的方法，它对数据表中一个或者多个列的值进行结构排序，是数据库中一个非常有用的对象。**

### 索引的创建 ###

1.  使用企业管理器创建
    
    启动企业管理器–选择数据库------选在要创建索引的表------在表的下拉菜单中选择索引—在快捷菜单中选择新建索引–
    
    在新建索引对话框中单击“添加”按钮，弹出“从列表中选择列”，在该对话框中选择要添加到索引键的表列
    
    单击确定返回新建索引对话框，再点击确定完成索引的创建。
2.  使用T-sql语句创建索引。

create index语句为给定表或视图创建一个改变物理顺序的聚集索引，也可以创建一个具有查询功能的非聚集索引。语法格式如下：
    create [unique] [clustered] [nonclustered] index index_name
    on {
        tabel/view} (column[dese/asc][....n])
    ----------------------------------------------------------------------------------------
    CREATE INDEX命令创建索引各参数说明如下：
    
    UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。
    
    CLUSTERED：用于指定创建的索引为聚集索引。
    
    NONCLUSTERED：用于指定创建的索引为非聚集索引。
    
    index_name：用于指定所创建的索引的名称。
    
    table：用于指定创建索引的表的名称。
    
    view：用于指定创建索引的视图的名称。
    
    ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。
    
    Column：用于指定被索引的列。

注： \[unique\] \[clustered\] \[nonclustered\]表示要创建索引的类型，依此为唯一索引，聚集索引，和非聚集索引；  
当省略unique选项时，建立非唯一索引；  
当省略unique,nonclustered选项时.建立聚集索引；  
省略nonclustered选项时，建立唯一聚集索引。  
在不显示指定是否为聚集/非聚集索引的情况下创建的索引默认为非聚集索引

使用索引虽然可以提高系统的性能，增强数据检索速度，但它需要占用大量的物理存储空间，**建立索引的一般原则如下**：

（1）只有表的所有者可以在同一表中创建索引。

（2）每个表中只能创建一个聚集索引。

（3）每个表中最多可以创建249个非聚集索引。

（4）在经常查询的字段上建立索引。

（5）定义text,image,bit数据类型的列上不要建立索引。

（6）在外间上可以建立索引。

（7）在主键列上一定要建立索引。

（8）在那些重复的值比较多，查询较少的列上不要建立索引。

### 聚簇索引和非聚簇索引区别 ###

聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续聚集索引:物理存储按照索引排序；
    	
    	聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
    	
    	索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。优势与缺点：
    	
    	聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。

### 聚集索引和主键 ###

**一个表最多一个主键，最多一个聚集索引。**

`数据库在创建主键同时，会自动建立一个唯一索引。如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引。 我们默认都会在主键字段上建立聚集索引，理论上聚集索引可以创建在任何一列你想创建的字段上，也就是该字段不需要是唯一值，但实际情况并不能随便指定，否则在性能上会很糟糕。 虽然默认是在主键上建立聚集索引的，但主键就是聚集索引这是错误的想法。`

**所以在建立主键(PRIMARY KEY)与聚集索引(Clustered Index)的先后顺序就只有下面几种情况：**  
情况一、在没有任何索引的表上建主键，同时自动产生唯一的聚集索引。

情况二、在没有任何索引的表上先建聚集索引，再建主键。  
主键会被设为非聚集索引

情况三、在有非聚集索引表上，先建主键。  
主键默认是聚集索引

情况四，在有非聚集索引的表，先建聚集索引，再建主键。  
主键会被设为非聚集索引

**如果想看详细情况（附图）参考文章**：[https://blog.csdn.net/ghj1976/article/details/3943238][https_blog.csdn.net_ghj1976_article_details_3943238]

## 主键 ##

限制和局限  
一个表只能包含一个 PRIMARY KEY 约束。

在 PRIMARY KEY 约束中定义的所有列都必须定义为 NOT NULL。 如果没有指定为 Null 性，则加入 PRIMARY KEY 约束的所有列的为 Null 性都将设置为 NOT NULL。

创建主键会自动创建相应的唯一群集索引、聚集索引或非聚集索引（如果这样指定）

若要重新定义主键，则必须首先删除与现有主键之间的任何关系，然后才能创建新主键。 此时，将显示一条消息警告您：作为该过程的一部分，将自动删除现有关系。

如果主键由多个列组成，则其中一个列将允许重复值，但是主键中所有列的值的各种组合必须是唯一的。

在SQL Server中，主键是数据表中唯一标识每一行记录的列，如果一个表没有主键，表中的记录将不能被区分开。此外，主键还有助于提高查询性能，使用主键加快了表中记录的访问速度，因此，在使用SQL Server进行数据库设计时，主键的设计就显得尤为重要。

### 创建主键 ###

**首先，在数据库中创建好数据表后，可以使用以下T-SQL代码语句**

1.  使用 T-SQL 命令，执行完命令后，主键就会被成功设置

ALTER TABLE 表名
    ADD CONSTRAINT PK_主键名称 PRIMARY KEY (列名)
     
    --其中，表名是要设置主键的表名，主键名称是主键约束的名称（可以自己定义），列名是要设置为主键的列名
     
    ALTER TABLE sys_menu
    ADD CONSTRAINT PK_menu_id PRIMARY KEY (menu_id)
     
     
     
    ALTER TABLE student
    ADD CONSTRAINT PK_student PRIMARY KEY (StuID) --将表中的StuID设为主键
     
     
    ALTER TABLE student
    DROP CONSTRAINT PK_student			  --将表中的主键约束PK_studnet删除

**除此之外，在SQL Server中，还可以使用下列代码定义表的主键**

CREATE TABLE table_name
     
    (
     
    column_name datatype PRIMARY KEY
     
    )
     
    CREATE TABLE student                                           --表名为student
    (
    	  StuID int NOT NULL PRIMARY KEY,                           --学生学号
    	  StuName varchar(15) NOT NULL,                             --学生姓名
    	  Sex char(2) NULL,                                         --性别
    	  Major varchar(20) NULL,                                   --所选专业
    )

primary key(列名1,列名2)

此外，在SQL Server中，还可以使用如下代码定义联合主键，也就是具有多个字段的主键。例如，要同时定义列customer\_id和 order\_id两个字段为一个联合主键，可以使用以下T-SQL代码

CREATE TABLE orders
     
    (
     
    customer_id INT,
     
    order_id INT,
     
    PRIMARY KEY(customer_id, order_id)
     
    )

alter table 表名 add constraint pk\_name primary key (列名1,列名2)

因此，在SQL Server中，定义表的主键非常容易，无论是单一主键还是联合主键，只需要几行代码，就可以完成定义。

### 设置主键并置为非空 ###

要先确定你要设置主键的的那列不为空，如果你的列为空，以下代码可以帮助你设为空并且添加主键

/*Table_Member是数据表的名字，StudentID 是要设为主键的列，bigint 是主键列的数据类型*/
    ALTER TABLE Table_Member ALTER COLUMN StudentID bigint NOT NULL;
     
    /*Table_Member是数据表的名字,StudentID 是要设为主键的列*/
    ALTER TABLE Table_Member WITH NOCHECK
    ADD CONSTRAINT PK_Table_Member PRIMARY KEY (StudentID)

### 更改已经建好的表中主键列的字符类型 ###

同样的，这个操作也要分为两步，在SQL server 的设计理念中，主键列不可更改数据类型，如果要将不是主键的列更改字符类型，直接跳到第二步。

ALTER TABLE [表名] DROP CONSTRAINT [主键约束名]
     
    ALTER TABLE [表名] ALTER COLUMN [列名] [数据类型]

### 查找主键名 ###

SELECT name
    FROM sys.key_constraints
    WHERE type = 'PK'
       AND OBJECT_NAME(parent_object_id) = 'YourTableName';

### 删除主键 ###

ALTER TABLE YourTableName
    DROP CONSTRAINT YourPrimaryKeyName;

## 唯一索引 ##

唯一索引确保索引键列不包含重复值。（不指定为聚集索引的，默认为非聚集索引）

唯一索引可以由一列或多列组成。如果唯一索引有一列，则此列中的值将是唯一的。如果唯一索引有多个列，则这些列中的值组合是唯一的。

任何试图向唯一索引键列中插入或更新数据而导致重复的行为都会报错。

唯一索引可以是聚集索引或非聚集索引。

要创建唯一索引，可以使用CREATE UNIQUE INDEX语句，如下所示：

CREATE UNIQUE INDEX index_name
    ON table_name(column_list);

### 创建包含一列的唯一约束 ###

通过邮件caren.stephens@msn.com查找客户：

SELECT
        customer_id, 
        email 
    FROM
        sales.customers
    WHERE 
        email = 'caren.stephens@msn.com';

查询优化器必须扫描整个聚集索引才能找到该行。

为了加快查询速度，可以在电子邮件（email）列添加非聚集索引。

但是，假设每个客户都有唯一的电子邮件，您可以为电子邮件列创建唯一索引。

由于sales.customers表已经有数据了，所以要先检查电子邮件列中的重复值：

SELECT 
        email, 
        COUNT(email)
    FROM 
        sales.customers
    GROUP BY 
        email
    HAVING 
        COUNT(email) > 1;

查询返回一个空结果集。说明电子邮件列中没有重复的值。  
因此，可以直接为sales.customers的电子邮件列创建唯一索引：

CREATE UNIQUE INDEX ix_cust_email 
    ON sales.customers(email);

现在，查询优化器将走ix\_cust\_email索引，并使用索引查找（Index Seek）方法搜索行。

### 创建包含多列的唯一约束 ###

首先，创建一个名为t1的表，其中有两列：

CREATE TABLE t1 (
        a INT, 
        b INT
    );

接下来，创建一个包含a列和b列的唯一索引：

CREATE UNIQUE INDEX ix_uniq_ab 
    ON t1(a, b);

然后，在t1表中插入新行：

INSERT INTO t1(a,b) VALUES(1,1);

然后，在t1表中插入另一行。请注意，值1在a列中重复，但a列和b列中的值组合不重复：

登录后复制

INSERT INTO t1(a,b) VALUES(1,2);

正常插入，没有问题

最后，将已经存在的行插入t1表中：

INSERT INTO t1(a,b) VALUES(1,2);

SQL Server报错如下：

Cannot insert duplicate key row in object 'dbo.t1' with unique index 'ix_ab'. The duplicate key value is (1, 2).

### SQL Server唯一索引与NULL ###

NULL是特殊的。是一个标记，表示没有数据或者数据不适用。

NULL甚至不等于它本身。但是，当涉及到唯一索引时，SQLServer会对NULL值进行相同的处理。这意味着如果在一个可为NULL的列上创建唯一索引，则此列中只能有一个NULL值。

以下创建名为t2的新表，并在a列上定义唯一索引:

CREATE TABLE t2(
        a INT
    );
    
    CREATE UNIQUE INDEX a_uniq_t2
    ON t2(a);

在a列中插入NULL：

INSERT INTO t2(a) VALUES(NULL);

但是，如果再次执行上述查询，SQL Server会因重复的NULL值而报错：

INSERT INTO t2(a) VALUES(NULL);

唯一索引（Unique index）与唯一约束（UNIQUE constraint）
    唯一索引和唯一约束都强制一列或多列中值的唯一性。
    SQL Server以相同的方式验证唯一索引和唯一约束的重复项。
    
    创建唯一约束时，SQL Server会在幕后创建与此约束关联的唯一索引。
    
    但是，对列创建唯一约束可以明确唯一索引的作用。

## 创建、删除索引 ##

1，创建索引  
语法：

create index 索引名 on 表名（字段1，字段2，，，，，）

create index idx_table_cxp on table(code,xh,price)

2，删除索引  
语法：

drop index 索引名 on 表名

drop index idx_table_cxp on table

## 约束 ##

### SQL Server 创建唯一约束和外键约束 ###

在关系型数据库中，约束是一种用于保证数据完整性的重要机制。SQL Server 提供了多种约束类型，其中包括唯一约束和外键约束。本文将介绍如何在 SQL Server 中创建唯一约束和外键约束，并提供相应的代码示例。

### 唯一约束 ###

唯一约束用于确保某列或一组列的值是唯一的，即不重复的。在 SQL Server 中，可以通过以下代码创建唯一约束：

-- 创建唯一约束
    ALTER TABLE 表名
    ADD CONSTRAINT 约束名 UNIQUE (列名);

例如，我们有一个名为 Students 的表，其中有一个列为 StudentID，我们希望保证该列的值是唯一的。可以使用以下代码创建一个名为 PK\_Students 的唯一约束：

-- 创建唯一约束
    ALTER TABLE Students
    ADD CONSTRAINT PK_Students UNIQUE (StudentID);

如果插入或更新的数据违反了唯一约束，则 SQL Server 将返回一个错误。

### 外键约束 ###

外键约束用于确保两个相关表之间的关系完整性。在 SQL Server 中，可以通过以下代码创建外键约束：

-- 创建外键约束
    ALTER TABLE 子表名
    ADD CONSTRAINT 约束名
    FOREIGN KEY (子表列名) REFERENCES 主表名(主表列名);

例如，我们有两个表分别为 Orders 和 Customers，其中 Orders 表包含一个 CustomerID 列，该列与 Customers 表中的 CustomerID 列建立关系。我们可以使用以下代码创建一个名为 FK\_Orders\_Customers 的外键约束：

-- 创建外键约束
    ALTER TABLE Orders
    ADD CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

当插入或更新数据时，如果违反了外键约束，则 SQL Server 将返回一个错误。

**示例**  
以下是一个完整的示例，演示如何创建唯一约束和外键约束：

-- 创建 Students 表
    CREATE TABLE Students
    (
        StudentID INT,
        StudentName VARCHAR(50),
        PRIMARY KEY (StudentID)
    )
    
    -- 创建 Orders 表
    CREATE TABLE Orders
    (
        OrderID INT,
        OrderName VARCHAR(50),
        CustomerID INT,
        PRIMARY KEY (OrderID),
        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    )
    
    -- 创建唯一约束
    ALTER TABLE Students
    ADD CONSTRAINT PK_Students UNIQUE (StudentID);
    
    -- 创建外键约束
    ALTER TABLE Orders
    ADD CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

以上代码创建了一个名为 Students 的表，并为 StudentID 列创建了唯一约束。同时，还创建了一个名为 Orders 的表，并为 CustomerID 列创建了外键约束，确保它与 Customers 表中的 CustomerID 列保持关联。

**唯一约束用于确保某列或一组列的值是唯一的，而外键约束用于确保两个相关表之间的关系完整性。合理使用这些约束可以确保数据的完整性和一致性，提高数据库的质量和性能。**

## 索引运算总结 ##

很有必要一看  
[https://blog.csdn.net/zyypjc/article/details/127357952][https_blog.csdn.net_zyypjc_article_details_127357952]

## 参考文章 ##

https://blog.csdn.net/lanxingbudui/article/details/98848094

https://www.wanzijz.com/view/18649.html

https://blog.51cto.com/u\_16213363/7232315

https://blog.csdn.net/zyypjc/article/details/127357952

https://blog.51cto.com/u\_16099204/6884211

https://blog.csdn.net/zgscwxd/article/details/132462165

https://blog.51cto.com/u\_16099238/6522775

https://blog.csdn.net/qq\_36636312/article/details/110136943

https://blog.51cto.com/u\_16175427/6597697

[c35f7502775b499ca303eb348c4fd84d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/23/130ebc86b4414ec389d5cbd6efa866ed.png
[https_blog.csdn.net_ghj1976_article_details_3943238]: https://blog.csdn.net/ghj1976/article/details/3943238
[https_blog.csdn.net_zyypjc_article_details_127357952]: https://blog.csdn.net/zyypjc/article/details/127357952