MySQL の 6 つの一般的な制約タイプの詳細な分析-mysql チュートリアル-php.cn

MySQL の 6 つの一般的な制約タイプの詳細な分析

制約の文字通りの意味は、何かを行う方法を規定または制限することです。MySQL では、制約はデータテーブル内のデータのルールを指定することです。データの信頼性を確保するためです。たとえば、特定の列に Null 値を含めることはできません。実際には、次のような制約が発生します。

NOT NULL: 列に NULL 値を含めることができないことを確認します。
CHECK: 列の値が特定の条件を満たしていることを確認します。
UNIQUE: 列内のすべての値が異なることを確認します。
PRIMARY KEY: NOT NULL および UNIQUE の組み合わせ。テーブル内の各行を一意に識別します。
FOREIGN KEY: 外部キー制約
DEFAULT: 値がない場合が指定されている場合、デフォルトは列 Value

[関連する推奨事項: mysql ビデオチュートリアル ]

Constraint

に設定されます。

##1.NULL

MySQL では、列に Null 値が表示されないように

NOT NULL を使用します。テーブルを作成するときの形式は

mysql> create table user(name varchar(255)not null);
Query OK, 0 rows affected (0.06 sec)

ログイン後にコピー

null 値を挿入しようとすると、例外がスローされます。

mysql> insert user values(null);
ERROR 1048 (23000): Column &#39;name&#39; cannot be null

ログイン後にコピー

または、既存のテーブルに新しい

NOT NULL 制約を追加します。

mysql> alter table user modify name varchar(255) not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

ログイン後にコピー

削除

NOT NULL制約。

mysql> alter table user modify name varchar(255)  null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

ログイン後にコピー

2.CHECK

列に条件付き制約を定義する場合は、次のような CHECK を使用して、年齢フィールドを強制的に設定できます。 18 より大きく 80 未満である場合、エラーが報告されます。

mysql> create table user(age int(11) check(age>18 and age <80));
Query OK, 0 rows affected, 1 warning (0.06 sec)

ログイン後にコピー

挿入テストでは、9 と 81 が挿入時に例外をスローすることがわかります。

mysql> insert user values(9);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.

mysql> insert user values(19);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(81);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql>

ログイン後にコピー

年齢は 18 歳以上、都市は中国でなければならないなど、複数列の制約を実行することもできます。

mysql> create table user(age int(11),city varchar(255) ,check(age>18 and city=&#39;中国&#39;));
Query OK, 0 rows affected, 1 warning (0.05 sec)

ログイン後にコピー

テストを挿入します。

mysql> insert user values(81,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(8,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(20,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(20,&#39;中国&#39;);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(20,&#39;中国1&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(85,&#39;中国&#39;);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(9,&#39;中国&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.

ログイン後にコピー

また、性別が男性、女性、不明、女装のセットに含まれている必要があるなど、列の値が指定されたセットに含まれている必要があることを指定することもできます。

mysql> create table user(sex varchar(255) check (sex in (&#39;男&#39;,&#39;女&#39;,&#39;未知&#39;,&#39;人妖&#39;)));
Query OK, 0 rows affected (0.05 sec)

ログイン後にコピー

テストを挿入します。

mysql> insert user values("男");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("男男");
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values("女");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values("人妖");
Query OK, 1 row affected (0.00 sec)

ログイン後にコピー

制約に名前を付けて、制約を削除します。

mysql> create table user (age int(11) ,constraint CHK_AGE check(age>18));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values(5);
ERROR 3819 (HY000): Check constraint &#39;CHK_AGE&#39; is violated.

mysql> alter table user drop check CHK_AGE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert user values(5);
Query OK, 1 row affected (0.01 sec)

ログイン後にコピー

しかし、この書き方を見たことがありますか?

次の内容を推測してください。

これは実際には条件判断の場合で、>=18、または 0 ～ 10 の数値しか挿入できません。

CREATE TABLE `user` (`age` int(11) CHECK 
(((case when (`age` >=18) then 1 
else 
(case when age<10 and age >0 then 1 else 2 end) end) =1)));

ログイン後にコピー

3.UNIQUE

UNIQUE 制約は、列 UNIQUE に重複する値がないことを保証します。および PRIMARY KEY 制約は列値の一意性を保証しますが、UNIQUE は各テーブルに複数回出現できますが、PRIMARY KEY は 1 回しか出現できません。

以下の名前フィールドを繰り返すことはできません。

mysql> create table user (name varchar(255),unique(name));
Query OK, 0 rows affected (0.07 sec)

ログイン後にコピー

テストを挿入します。

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry &#39;张三&#39; for key &#39;user.name&#39;mysql>

ログイン後にコピー

この制約に名前を付けて削除します。

mysql> create table user (name varchar(255),constraint name_un unique(name));
Query OK, 0 rows affected (0.07 sec)

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry &#39;张三&#39; for key &#39;user.name_un&#39;
mysql> alter table user drop index name_un;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

ログイン後にコピー

挿入後、次のステートメントを使用して作成ステートメントを表示できます。

mysql> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  UNIQUE KEY `name_un` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ログイン後にコピー

UNIQUE 制約を削除するには、DROP INDEX または ALTER TABLE ステートメントを使用できます。既存のテーブルのアドオン。

mysql> DROP INDEX name_un ON user;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ログイン後にコピー

4.PRIMARY KEY

通常、各テーブルには各行を一意に識別する値が含まれており、この列は PRIMARY KEY と呼ばれます。

mysql> alter table user add constraint name_un unique(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

ログイン後にコピー

5.FOREIGN KEY

##FOREIGN KEY は、テーブル内のフィールドが別のテーブル値のフィールドに存在する必要があることを制約するために使用されます。ただし、別のテーブルでは、この列は必ずしも主キーである必要はなく、一意のインデックスである必要があります。そうでない場合、作成は失敗します。

たとえば、orders テーブルの userId は、user テーブルの id を参照する必要があります。挿入された userId が user テーブルに存在しない場合、挿入することはできません。 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:js;toolbar:false;">mysql> create table user (id int(11) ,age int(11),primary key (id)); Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> insert user values(1,2); Query OK, 1 row affected (0.02 sec) mysql> insert user values(1,2); ERROR 1062 (23000): Duplicate entry &#39;1&#39; for key &#39;user.PRIMARY&#39;mysql></pre><div class="contentsignin">ログイン後にコピー</div></div>しかし、問題があります。メインテーブル (ユーザー) のレコードが削除または更新された場合、注文内のレコードはどうなるのでしょうか? , 以下の例のように、エラーが直接報告されていることがわかります。

mysql> create table orders (id int(11) primary key ,userId int(11) ,  FOREIGN KEY (userId) REFERENCES user(id) );
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert orders values(1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))

mysql> insert orders values(1,1);
Query OK, 1 row affected (0.01 sec)

ログイン後にコピー

MySQL には、この種の問題を解決するためにいくつかの制約が用意されています。たとえば、ユーザーテーブルが更新されると、注文も次々に更新されます。

RESTRICT: 子テーブルにレコードがある場合、親テーブルのレコードの更新または削除を拒否します。

CASCADE: 親テーブルのレコードを更新または削除するときに、子テーブルのレコードも自動的に更新または削除します。
SET NULL: 親テーブルのレコードを更新または削除する場合、子テーブルのフィールドの値を null に設定します。
デフォルトではRESTRICTが使用されていることが分かりますので、更新時はRESTRICTも更新され、削除時はnullが設定されるように修正しましょう。
```
mysql> update user set id =2 where id =1;

Cannot delete or update a parent row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
```
ログイン後にコピー

mysql> alter table orders add constraint orders_ibfk_1  FOREIGN KEY (`userId`) REFERENCES `user` (`id`) on update cascade on
delete set null;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

ログイン後にコピー

テストの削除。

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

mysql> insert orders values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> update user set id =2 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |      2 |
+----+--------+
1 row in set (0.01 sec)

ログイン後にコピー

6.DEFAULT

DEFAULT 制約は、列のデフォルト値を設定するために使用されます。値がフィールドに割り当てられていない場合を指定すると、システムによってこのフィールドにデフォルト値が自動的に挿入されます。割り当てがない場合は、データの挿入時にこのフィールドが指定されていないことを意味します。NULL 値が指定された場合は、NULL 値が最後に格納されます。

mysql> delete from user where id =2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |   NULL |
+----+--------+
1 row in set (0.00 sec)

ログイン後にコピー

元のアドレス: https://juejin.cn/post/7000352993572814885

著者: i Tingfeng Passing Night