SQL - Composite Key

A Composite Key 关键在于表中所列两个或两个以上属性(栏目或领域)。 这也可以被描述为在多个栏目上形成的主要关键。 利用综合钥匙,我们可以在数据库表中独一无二地确定任何记录。 这些栏目加在一起,可以保证独一无二,但单列可能无法保证表中的独特性。

A KEY is a set of one or more attributes whose combined values are unique among all occurrences in the given table.

Say we have a CUSTOMERS table with multiple columns from which we will select two columns named Adharcard_Id and Mobile_number and will combine them to make the Composite key, and that combination can be used to fetch any customers records uniquely in a table.

让我们以适当的图表理解——

下面是Composite Key的重要要点。

A Composite Key may or may not be a part of the Foreign key.

A Composite Key can not be NULL.

A Composite Key also can be created by combining more than one Candidate Key.

It is also known as Compound key.

All the attributes in a compound keys are foreign keys.

Why do we need Composite Key?

当数据库表没有单列能够从表格中找到独一无二的浏览(或记录)时,我们可能需要两个或两个以上的领域/栏,以便从表格中获取独一无二的记录/记录。

Syntax

The syntax to estabpsh a COMPOSITE KEY 编制表格

CREATE TABLE TABLE_NAME(COLUMN1, COLUMN2, COLUMN3…., CONSTRAINT COMPOSITE_KEY_NAME PRIMARY KEY(COLUMN1, COLUMN2,..);

。 具体规定这一点是任择的。 当你想从表一栏中删除综合关键制约因素时,就显得多余。

Example

在以下例子中,我们正试图创建一个名为CUSTOMERS的表格,并设多个栏目,同时制作表格时将分两个栏目,即ADHARCARD_ID和MOBILE_NO至PRIMaire KEY (ADHARCARD_ID, MOBILE_NO),以在这些栏目上形成一个综合钥匙。

Following is the statement to create table in SQL −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADHARCARD_ID BIGINT,
   MOBILE_NO BIGINT,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),       
   CONSTRAINT CK_CUSTOMERS PRIMARY KEY (ADHARCARD_ID, MOBILE_NO)
);

Where the CK_CUSTOMERS is the name of a composite of this table.

Output

以上说明的产出如下:

(0 rows affected)

Verification

由于我们利用ADHARCARD_ID栏和MOBILE_NO为客户表格设立了一个Composite Key,这些栏目的价值不能重复。

First of all, lets insert a record into a customers table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO, ADDRESS,SALARY)
VALUES (1,  Ramesh , 32, 901234984567, 9021345687, Ahmedabad , 2000.00 );

现在,请在客户表格中增加一个记录,同时添加一个字塔——id和移动电话号码——

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,901234984567,9021345687,  Delhi , 1500.00 );

您可以注意到,第二份INSERT声明产生了一个错误的信息,即“Violation of PRIMaire KEY”。 如下所示:

Msg 2627, Level 14, State 1, Line 14
Violation of PRIMARY KEY constraint  CK_CUSTOMERS . Cannot insert duppcate key in object  dbo.CUSTOMERS . The duppcate key value is (901234984567, 9021345687).

Dropping the Composite Key

如果你为客户表格设定了Composite key,那么也必须有办法降低组合钥匙。 您可以通过使用ALTER TABLE......来做到这一点。 DROP 声明。

Syntax

The syntax to reduce the Composite key 表1

ALTER TABLE TABLE_NAME DROP COMPOISTE_KEY_NAME; (in SQL)
ALTER TABLE TABLE_NAME DROP PRIMARY KEY;(in MYSQL)

Example

我们可以使用以下的King发言,放弃Composite key。 表格中的制约因素——

ALTER TABLE CUSTOMERS DROP CK_CUSTOMERS;

CK_CUSTOMERS是表格综合关键名称。

Output

以上表格产生了以下产出:

(0 rows affected)

Verification

由于我们已从客户表中删除了该混合物,因此现在你可以插入adharcard_id 和mobile number的重复值。

请将以下两个记录插入adharcard_id和mobile number。

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO, ADDRESS,SALARY)
VALUES (1,  Ramesh , 32, 901234984567, 9021345687, Ahmedabad , 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,901234984567,9021345687,  Delhi , 1500.00 );

如果你核实下表的内容,客户的配卡号码和移动号码相同。

SELECT * FROM CUSTOMERS;

+----+--------+-----+--------------+------------+-----------+---------+
| ID | NAME   | AGE | ADHARCARD_ID | MOBILE_NO  | ADDRESS   | SALARY  |
+----+--------+-----+--------------+------------+-----------+---------+
|  1 | Ramesh |  32 | 901234984567 | 9021345687 | Ahmedabad | 2000.00 |
|  2 | Khilan |  25 | 901234984567 | 9021345687 | Delhi     | 1500.00 |
+----+--------+-----+--------------+------------+-----------+---------+