sql的外键约束和主键约束_SQL约束

sql的外键约束和主键约束

SQL | 约束条件 (SQL | Constraints)

Constraints are the guidelines implemented on the information sections of a table. These are utilized to restrict the kind of information that can go into a table. This guarantees the precision and unwavering quality of the information in the database.

约束是在表的信息部分上实施的准则。 这些用于限制可以进入表的信息的种类。 这保证了数据库中信息的准确性和坚定不移的质量。

Constraints could be either on a segment level or a table level. The segment level constraints are applied uniquely to one segment, though the table level Constraints are applied to the entire table.

约束可以在段级别或表级别。 段级别约束唯一地应用于一个段，尽管表级别约束应用于整个表。

The following are probably the most usually utilized constraints accessible in SQL.

以下可能是在SQL中最常使用的约束。

1. NOT NULL - Guarantees that a section can't have NULL worth.

   NOT NULL-保证节中的值不能为NULL。

2. DEFAULT - Gives a default incentive to segment when none is determined.

   默认 -默认情况下，如果未确定细分原因，则会进行细分。

3. PRIMARY KEY - Interestingly distinguishes each column/record in a database table.

   主键 -有趣地区分数据库表中的每个列/记录。

4. UNIQUE - Guarantees that all qualities in a section are unique.

   唯一 -保证部分中的所有品质都是唯一的。

5. FOREIGN KEY - Extraordinarily distinguishes a column/record in any of the given database tables.

   FOREIGN KEY-特别区分任何给定数据库表中的列/记录。

6. CHECK - The CHECK requirement guarantees that all the qualities in a section fulfill certain conditions.

   检查 -检查要求可确保部分中的所有质量均满足某些条件。

7. INDEX - Used to make and recover information from the database rapidly.

   索引 -用于快速制作和从数据库中恢复信息。

Constraints can be only be used either while creating a table (CREATE TABLE) or while altering any table (ALTER TABLE).

约束只能在创建表( CREATE TABLE )或更改任何表( ALTER TABLE )时使用。

Syntax:

句法：

    CREATE TABLE table_name 
    (
	    column1 datatype Constraint,
	    column2 datatype Constraint,
	    column3 datatype Constraint,
       ....
    );

放下约束 (Dropping Constraints)

Any constraint defined by the programmer can be dropped using the DROP CONSTRAINT option using the ALTER TABLE command.

可以使用ALTER TABLE命令使用DROP CONSTRAINT选项删除程序员定义的任何约束。

Syntax:

句法：

    ALTER TABLE <table_name> DROP CONSTRAINT <column_name>

诚信约束 (Integrity Constraints)

Integrity constraints are utilized to guarantee the precision and consistency of the information in a social database. Information integrity is taken care of in a social database through the idea of referential integrity.

完整性约束用于保证社交数据库中信息的准确性和一致性。 信息完整性是通过参照完整性的概念在社交数据库中处理的。

There are numerous sorts of integrity constraints that assume a job in Referential Integrity (RI). These constraints incorporate Primary Key, Foreign Key, Unique Constraints and different constraints which are referenced previously.

有许多类型的完整性约束假定参照完整性(RI)中的工作 。 这些约束包括主键，外键，唯一约束和先前引用的不同约束。

Now let us see all constraints in detail with examples.

现在，让我们通过示例详细查看所有约束。

1)非空 (1) NOT NULL)

In the event that we determine a field in a table to be NOT NULL. At that point the field will never acknowledge invalid worth. That is, you will be not permitted to embed another column in the table without indicating any an incentive to this field.

如果我们确定表中的字段为NOT NULL 。 届时，该领域将永远不会承认无效价值。 就是说，您必须在未对该字段指示任何动机的情况下不允许在表格中嵌入另一列。

Example:

例：

CREATE TABLE Emp
(
id int NOT NULL,
name varchar(10) NOT NULL,
address varchar(20)
);

A table with named "Emp" will be created,

将创建一个名为“ Emp”的表，

ID	NAME	ADDRESS

ID	名称	地址

2)独特 (2) UNIQUE)

This constraint serves to exceptionally recognize each line in the table. For example for a specific segment, all the lines ought to have extraordinary qualities. We can have more than one UNIQUE section in a table.

此约束用于异常识别表中的每一行。 例如，对于特定的细分市场，所有生产线都应具有非凡的品质。 一个表中可以有多个UNIQUE节。

Example:

例：

CREATE TABLE emp
(
ID int NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
INSERT INTO emp(ID,NAME,ADDRESS)
VALUES(1123,'Hari','S colony');
INSERT INTO emp(ID,NAME,ADDRESS)
VALUES(1123,'Sunny','S colony');

This command will give an error as the ID constrains needs to be unique instead, we have kept it same as the other.

该命令将产生错误，因为ID约束需要唯一，我们将其保持不变。

3)主键 (3) PRIMARY KEY)

Primary Key is a field that remarkably recognizes each line in the table. On the off chance that a field in a table as Primary key, at that point the field won't have the option to contain NULL qualities just as all the lines ought to have one of a kind qualities for this field. In this way, as such, we can say this is a blend of NOT NULL and UNIQUE limitations.

主键是一个可以明显识别表中每一行的字段。 如果将表中的某个字段用作主键的可能性很小，那么此时该字段将不能选择包含NULL品质，就像该行的所有行都应具有一种品质一样。 这样，可以说这是NOT NULL和UNIQUE限制的混合。

Example:

例：

CREATE TABLE emp
(
ID int NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);

"emp" table will be created. We have taken some of the records in it to use the table in further constraints.

将创建“ emp”表。 我们已将其中的一些记录用于进一步限制的表中。

Table: emp

表：emp

ID	NAME	ADDRESS
112	Hari	S colony
3211	Sunny	J vihar
443	Mona	T Apartments
3212	Kamini	F plot

ID	名称	地址
112	哈里	殖民地
3211	阳光明媚	维哈尔
443	莫娜	T公寓
3212	卡米尼	F图

Here we can clearly see that each ID is NOT NULL value and is unique. Also, id is a primary key.

在这里我们可以清楚地看到每个ID都不是NULL值，并且是唯一的。 另外， id是主键。

4)外键 (4) FOREIGN KEY)

Foreign Key is a field in a table which exceptionally distinguishes each column of another table. That is, this field focuses to the primary key of another table. This normally makes a sort of connection between the tables.

外键是表中的一个字段，用于区别其他表的每一列。 即，该字段集中于另一个表的主键。 这通常在表之间建立某种连接。

Table: emp1

表：emp1

ID	NAME	AGE
112	Hari	45
3211	Sunny	26
443	Mona	33
3212	Kamini	45

ID	名称	年龄
112	哈里	45
3211	阳光明媚	26
443	莫娜	33
3212	卡米尼	45

Table: emp2

表：emp2

AGE	DESIGNATION	SALARY
45	Clerk	35000
26	Manager	45000
33	Accountant	35000
45	Receptionist	25000

年龄	指定	薪水
45	文员	35000
26	经理	45000
33	会计	35000
45	接待员	25000

Query:

查询：

CREATE TABLE emp1
(
ID int NOT NULL,
NAME int NOT NULL,
AGE int,
PRIMARY KEY (ID),
FOREIGN KEY (AGE) REFERENCES emp2(AGE)
);

5)检查 (5) CHECK)

Utilizing the CHECK requirement we can indicate a condition for a field, which ought to be fulfilled at the hour of entering esteems for this field.

利用CHECK要求，我们可以指示一个字段的条件，应该在输入该字段的评价时满足该条件。

For instance, the underneath inquiry makes a table emp1 and determines the condition for the field AGE as (AGE >= 18) That is, the client won't be permitted to enter any record in the table with AGE < 18.

例如，下面的查询创建一个表emp1并将字段AGE的条件确定为(AGE> = 18)。也就是说，不允许客户在AGE <18的表中输入任何记录。

Table: emp1

表：emp1

ID	NAME	AGE
112	Hari	45
3211	Sunny	26
443	Mona	33
3212	Kamini	45

ID	名称	年龄
112	哈里	45
3211	阳光明媚	26
443	莫娜	33
3212	卡米尼	45

Query:

查询：

CREATE TABLE emp1
(
id int NOT NULL,
name varchar(10) NOT NULL,
age int NOT NULL CHECK (AGE >= 18)
);

6)默认 (6) DEFAULT)

This requirement is utilized to give a default an incentive to the fields. That is, if at the hour of entering new records in the table in the event that the client doesn't indicate any an incentive for these fields, at that point the default worth will be doled out to them.

该要求用于默认激励字段。 也就是说，如果在客户未指示对这些字段进行任何激励的情况下在表中输入新记录的时间，则届时将向其分配默认值。

For instance, the underneath question will make a table named emp1 and determine the default an incentive for the field AGE as 18.

例如，下面的问题将创建一个名为emp1的表，并确定字段AGE的默认激励为18。

CREATE TABLE emp1
(
ID int NOT NULL,
NAME varchar(10) NOT NULL,
AGE int DEFAULT 18
);

翻译自: https://www.includehelp.com/sql/constraints.aspx

sql的外键约束和主键约束