SQL - Alternate key

表格的替代关键内容是目前尚未选定作为表格主要关键内容的候选人。这些是次要候选人钥匙,可用于在表格中独一无二地识别图(或记录)。

没有任何具体询问或提要在表格中确定备用钥匙。这只是一栏,是第二栏,可以选择为主要候选人。

A KEY plays an important role in a relational database. It is an attribute or set of attributes, that helps us to identify the unique row(or record) in a table. It also estabpshes the relationship among tables.

如果表仅包括一名候选人关键人选,作为表的主要要点处理,则表中没有其他关键因素。

Let us suppose we have a table named CUSTOMERS with various fields pke ID, Name, Mobile, address, etc. In this table ID and Mobile are treated as candidate keys. By using these two fields we can get the unique record from the Customer s table. Among them, one is treated as the primary key and another key is known as the alternate key of the Customer s table.

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

在上述客户表格中,“身份识别”栏和“流动”栏被称为候选人钥匙。因此,如果你把“ID”一栏视为主要钥匙,那么“Mobile_no”一栏即为客户表格的备用钥匙。

Even though alternate keys are not primary keys, they contain some important properties/features of their own. They are psted below.

The alternate key does not allow duppcate values.

A table can have more than one alternate keys.

The alternate key can contain NULL values unless the NOT NULL constraint is set exppcitly.

All alternate keys can be candidate keys, but all candidate keys can not be alternate keys. As a primary key, which is also a candidate key, can not be considered as an alternate key.

Keys in a table

下表所列关键人物名单如下:

Candidate key

Primary key

Alternate key

Foreign Key

Candidate Key

A 关键是用于独一无二地确定表格记录的超级钥匙。它可以是一个单一的领域或多个领域。主要关键人物、候补钥匙、表格中的外国关键人物都是候选人的关键。

Primary Key

A Primary Key is a main key that is used to retrieve records from a table. It is a single column or field in a table that uniquely identifies each record in a database table.

可使用中文本不适用。关键词,同时利用国家扫盲十年声明编制一个表格。

Syntax

CREATE TABLE COLUMN_NAME1, COLUMN_NAME2… PRIMARY KEY(COLUMN_NAME));

Alternate Key

。这可能是主要的关键,但不是。如同主要钥匙一样,它还独特地确定了在表格领域记录,以便从表格中检索浏览器。可以有一个单一或多个领域,在表格中确定为备用钥匙。

在数据库表上没有星号。

Foreign Key

表格的主要将在另一个表中填入Foreign key。在将这些表格插入价值的同时,主要领域的价值必须与外国关键领域的价值观相匹配;否则,外国关键一栏将不接受INSERT/b>。查询和投出错误。

在服务器中,在表格中建立一个外国关键领域的辛迪加——

CREATE TABLE TABLE_2(COLUMN_NAME FOREIGN KEY REFERENCES TABLE_1(COLUMN_NAME));

让我们看到一个例子,显示特定钥匙的使用情况,并表明可以被视为编制表格中一个替代钥匙的领域。

Example

在以下例子中,我们正在建立一个表格名称:CUSTOMERS,在“Q”号声明下显示的“Q”数据库中各个领域——

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

现在请在Customer s表格中添加一些记录,使用INSERT说明如下:

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, 911232495457,9021345687, Delhi , 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO,ADDRESS,SALARY) VALUES (3, Kaushik , 23, 912232425416, 9824434518, Kota , 2000.00);

现在请使用SlectT表显示表格数据如下:

SELECT * FROM CUSTOMERS;

下表将列出:

+----+---------+------+--------------+------------+-----------+---------+ | ID | NAME | AGE | ADHARCARD_ID | MOBILE_NO | ADDRESS | SALARY | +----+---------+------+--------------+------------+-----------+---------+ | 1 | Ramesh | 32 | 901234984567 | 9021345687 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | 911232495457 | 9021345687 | Delhi | 1500.00 | | 3 | Kaushik | 23 | 912232425416 | 9824434518 | Kota | 2000.00 | +----+---------+------+--------------+------------+-----------+---------+ 3 rows in set (0.00 sec)

In the above table, the keys are set as follows −

但是,为了显示Foreign key,我们需要两个表格。下面是关于创建另一个表格ORDERS的问询,该表是作为CUSTOMER_ID的外国钥匙。

CREATE TABLE ORDERS ( OID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT FOREIGN KEY REFERENCES CUSTOMERS(ID), AMOUNT DECIMAL, PRIMARY KEY (ID) );

采用INSERT声明,在本表中插入以下数值:

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (102, 2009-10-08 00:00:00 , 3, 3000.00); INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (101, 2009-11-20 00:00:00 , 2, 1560.00); INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT) VALUES (100, 2009-10-08 00:00:00 , 1, 1500.00);

To display the ORDERS table, we can use the following query −

SELECT * FROM ORDERS;

该表将显示如下:

+-----+---------------------+-------------+---------+ | OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+---------+ | 100 | 2009-10-08 00:00:00 | 1 | 1500.00 | | 101 | 2009-11-20 00:00:00 | 2 | 1560.00 | | 102 | 2009-10-08 00:00:00 | 3 | 3000.00 | +-----+---------------------+-------------+---------+

The foreign key will be referenced to the ID column in the Customers table above.

我们可以看到Foreign key field of ORDERS。表格将符合CUSTOMERS表主要领域的数值。

Rules to be followed for alternate keys

下面列出在将记录列入表格时应当遵循的候补钥匙规则清单。

Alternate key values should be unique.

Alternate key can not be NULL.

Previous Page Print Page Next Page Advertisements

SQL - Alternate key

Keys in a table

Candidate Key

Primary Key

Syntax

Alternate Key

Foreign Key

Example

Rules to be followed for alternate keys

友情链接