sql主键_SQL主键

sql主键

In a world driven with data all over, it is very easy to get duplicate data. The nightmare of any database table designer is to create a table with the possibility of duplicate data insertion. To overcome the problem of duplicate rows, SQL primary key is used to uniquely identify each row.

在充满数据的世界中，获取重复数据非常容易。 任何数据库表设计人员的噩梦都是创建一个可能重复插入数据的表。 为了克服重复行的问题，SQL主键用于唯一地标识每一行。

SQL主键 (SQL Primary Key)

A Primary key is a column or set of columns that can uniquely identify a row. When we create a table we should specify the primary key. Also, primary key creation comes with a set of rules mentioned below.

主键是可以唯一标识一行的一列或一组列。 创建表时，应指定主键。 同样，主键创建带有下面提到的一组规则。

1. A primary key can not be null.
   主键不能为空。
2. A primary key must always contain unique values. If its a combination of multiple columns that the combination must be unique.
   主键必须始终包含唯一值。 如果是多个列的组合，则该组合必须是唯一的。
3. One table can have only one primary key.
   一个表只能有一个主键。

Let us now try to understand primary key creation for the below mentioned databases.

现在让我们尝试了解以下提到的数据库的主键创建。

1. MySQL
   MySQL
2. PostgreSQL
   PostgreSQL
3. SQL Server
   SQL服务器

MySQL主键 (MySQL Primary Key)

Syntax: –

句法： -

CREATE TABLE <table_name>(<column_name1> <data_type> NOT NULL,
<column_name2> <data_type> NOT NULL,
<column_name3> <data_type> NOT NULL,
PRIMARY KEY (column_name1));

In the syntax above, a PRIMARY KEY keyword is used to specify that the column will be used a the primary key for the table.

在上面的语法中，PRIMARY KEY关键字用于指定该列将用作表的主键。

Let’s create a customer table to understand this better.

让我们创建一个客户表来更好地理解这一点。

CREATE TABLE customer
(cust_id varchar(8) NOT NULL UNIQUE, 
cust_name varchar(50) NOT NULL, 
state varchar(25), 
country varchar(25),
PRIMARY KEY (cust_id) );

Post-execution of the above query at MySQL workbench. The following screen will appear.

在MySQL工作台上执行以上查询。 将出现以下屏幕。

On performing a table inspection following screen will appear.

进行桌子检查时，将出现以下屏幕。

Primary Key Column

主键列

The value for Unique column is “YES” which mean that the column will accept a unique value. Also, the key column has row value “PRIMARY” and the row value for columns column specifies the column that will be the primary key.

“唯一”列的值为“ YES”，表示该列将接受唯一值。 此外，键列的行值为“ PRIMARY”，而列的行值指定要作为主键的列。

PostgreSQL主键 (PostgreSQL Primary Key)

Syntax: –

句法： -

CREATE TABLE <table_name>(<column_name1> <data_type> NOT NULL,
<column_name2> <data_type> NOT NULL,
<column_name3> <data_type> NOT NULL,
PRIMARY KEY (column_name(n)));

In the syntax above, a PRIMARY KEY keyword is used to specify that the column or set of columns will be used a the primary key for the table.

在上面的语法中，PRIMARY KEY关键字用于指定将某个列或一组列用作表的主键。

Let’s create an Employee table to understand this better.

让我们创建一个Employee表以更好地理解这一点。

CREATE TABLE Employee(
employee_no integer PRIMARY KEY,
employee_name character(50),
employee_city character(35),
employee_phn numeric);

In the table above, we are creating primary key using just one column “employee_no”. Following is the query that should be used for the creation of a table with multiple columns.

在上表中，我们仅使用一列“ employee_no”来创建主键。 以下是用于创建具有多列的表的查询。

CREATE TABLE Employee(
employee_no integer,
employee_name character(50),
employee_city character(35),
employee_phn numeric,PRIMARY KEY (employee_no,employee_phn));

In the table above, we are creating primary key using two columns “employee_no and employee_phn”.

在上表中，我们使用两列“ employee_no和employee_phn”创建主键。

Post-execution of the above query at PGAdmin. The following screen will appear.

在PGAdmin上执行以上查询。 将出现以下屏幕。

On checking the properties of the table.

在检查表的属性时。

Primary Key Column PostgreSQL

主键列PostgreSQL

SQL Server主键 (SQL Server Primary Key)

Syntax: –

句法： -

CREATE TABLE <table_name>(<column_name1> <data_type> NOT NULL,
<column_name2> <data_type> NOT NULL,
<column_name3> <data_type> NOT NULL,
PRIMARY KEY (column_name(n)));

In the syntax above, a PRIMARY KEY keyword is used to specify that the column or set of columns will be used a the primary key for the table.

在上面的语法中，PRIMARY KEY关键字用于指定将某个列或一组列用作表的主键。

Let’s create an Employee table to understand this better.

让我们创建一个Employee表以更好地理解这一点。

CREATE TABLE Employee(
employee_no integer PRIMARY KEY,
employee_name character(50),
employee_city character(35),
employee_phn numeric);

Post-execution of the above query at SQL Server Management Studio. The following screen will appear.

在SQL Server Management Studio中执行上述查询。 将出现以下屏幕。

On expanding the table structure.

关于扩展表结构。

Primary Key Column SQLServer

主键列SQLServer

摘要 (Summary)

The primary key is the most important aspect of any database table. When you design the tables, use primary keys wisely. Make sure to understand the data that will be inserted in the table and then find the unique value to assign the primary key. If there are no unique values, you can add an extra column with auto-increment values for the primary key.

主键是任何数据库表中最重要的方面。 设计表时，请明智地使用主键。 确保了解将插入表中的数据，然后找到唯一值以分配主键。 如果没有唯一值，则可以为主键添加一个带有自动递增值的额外列。

翻译自: https://www.journaldev.com/29294/sql-primary-key

sql主键