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中最常使用的約束。

NOT NULL - Guarantees that a section can't have NULL worth.
NOT NULL-保證節中的值不能為NULL。
DEFAULT - Gives a default incentive to segment when none is determined.
默認 -默認情況下，如果未確定細分原因，則會進行細分。
PRIMARY KEY - Interestingly distinguishes each column/record in a database table.
主鍵 -有趣地區分數據庫表中的每個列/記錄。
UNIQUE - Guarantees that all qualities in a section are unique.
唯一 -保證部分中的所有品質都是唯一的。
FOREIGN KEY - Extraordinarily distinguishes a column/record in any of the given database tables.
FOREIGN KEY-特別區分任何給定數據庫表中的列/記錄。
CHECK - The CHECK requirement guarantees that all the qualities in a section fulfill certain conditions.
檢查 -檢查要求可確保部分中的所有質量均滿足某些條件。
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的外鍵約束和主鍵約束