データベース:整合性制約

71825 ワード

文書ディレクトリ

1.整合性制約テスト

1.1整合性制約条件

1.1.1プライマリ・キー

1.1.2自己成長

1.1.3テスト非空NOT NULL

1.1.4テストデフォルトDEFAULT

1.1.5テストユニークUNIQUE KEY

1.1.6総括

1.整合性制約試験

1.1整合性制約条件

PRIMARY KEY AUTO_INCREMENT FOREIGN KEY NOT NULL UNIQUE KEY DEFAULT

1.1.1プライマリ・キー

≪プライマリ・キー｜Primary Key｜ldap≫:一意の識別子.ID番号と個人の関係は、プライマリ・キーとレコードの関係に相当します.一般的には、無意味なフィールドに追加されます.たとえば、番号フィールド定義プライマリ・キーフィールドの要件:重複できない、プライマリ・キーとしてマークされたフィールドは、単一フィールド・プライマリ・キーとマルチフィールド・プライマリ・キーに自動的に非空に分割されます.

-- 
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
username VARCHAR(20)
);
Query OK, 0 rows affected (0.09 sec)

DESC user1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
-- 
SHOW CREATE TABLE user1;
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
| Table | Create Table
                                                         |
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
  `id` int(11) NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)
-- 
INSERT user1 VALUES(2,'QUEEN');
Query OK, 1 row affected (0.01 sec)
INSERT user1 VALUES(1,'KING');
Query OK, 1 row affected (0.00 sec)
INSERT user1 VALUES(1,'KING');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
-- 
SELECT * FROM user1;
+----+----------+
| id | username |
+----+----------+
|  1 | KING     |
|  2 | QUEEN    |
+----+----------+
2 rows in set (0.00 sec)
-- id 1 
SELECT * FROM user1 WHERE id=1;
+----+----------+
| id | username |
+----+----------+
|  1 | KING     |
+----+----------+
1 row in set (0.01 sec)
-- 
mysql> CREATE TABLE IF NOT EXISTS userb(
    -> id INT,
    -> username VARCHAR(20),
    -> card CHAR(18),
    -> PRIMARY KEY (id,card)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC userb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | 0       |       |
| username | varchar(20) | YES  |     | NULL    |       |
| card     | char(18)    | NO   | PRI |         |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
-- （ ， ）
mysql> INSERT userb VALUES(1,'KING','111');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT userb VALUES(1,'QUEEN','112');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT userb VALUES(1,'PRINCESS','112');
ERROR 1062 (23000): Duplicate entry '1-112' for key 'PRIMARY'
mysql> SELECT * FROM userb;
+----+----------+------+
| id | username | card |
+----+----------+------+
|  1 | KING     | 111  |
|  1 | QUEEN    | 112  |
+----+----------+------+
2 rows in set (0.00 sec)

1.1.2自己成長

1つのテーブルに1つの自己成長フィールドしか存在せず、プライマリ・キーと組み合わせて使用するには、自己成長として識別されるフィールドはプライマリ・キーである必要がありますが、プライマリ・キーが自己成長としてマークされるとは限りません.整数の整数列にのみ効果があります.文字列のようにデフォルトは1から1を追加するたびに意味がありません.

mysql> CREATE TABLE IF NOT EXISTS userc(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)

mysql> DESC userc;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
--- 1
mysql> INSERT userc VALUES(1,'KING');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT userc(username) VALUES('QUEEN');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM userc;
+----+----------+
| id | username |
+----+----------+
|  1 | KING     |
|  2 | QUEEN    |
+----+----------+
2 rows in set (0.00 sec)

-- id 
mysql> INSERT userc VALUES(111,'KING1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id  | username |
+-----+----------+
|   1 | KING     |
|   2 | QUEEN    |
| 111 | KING1    |
+-----+----------+
3 rows in set (0.00 sec)
-- 
mysql> INSERT userc(username) VALUES('QUEEN1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id  | username |
+-----+----------+
|   1 | KING     |
|   2 | QUEEN    |
| 111 | KING1    |
| 112 | QUEEN1   |
+-----+----------+
4 rows in set (0.00 sec)
-- 
mysql> SHOW CREATE TABLE userc;
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| Table | Create Table
                                                                                               |
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| userc | CREATE TABLE `userc` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- NULL   DEFAULT  
mysql> INSERT userc VALUES(NULL,'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userc VALUES(DEFAULT,'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userc;
+-----+----------+
| id  | username |
+-----+----------+
|   1 | KING     |
|   2 | QUEEN    |
| 111 | KING1    |
| 112 | QUEEN1   |
| 113 | ABC      |
| 114 | ABC      |
+-----+----------+
6 rows in set (0.00 sec)

--- 
mysql> CREATE TABLE IF NOT EXISTS userd(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE userd;
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| Table | Create Table
                                                                                               |
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| userd | CREATE TABLE `userd` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT userd(username) VALUES('QUEEN1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM userd;
+-----+----------+
| id  | username |
+-----+----------+
| 100 | QUEEN1   |
+-----+----------+
1 row in set (0.00 sec)

-- 
ALTER TABLE userd AUTO_INCREMENT =500;

1.1.3テスト非空NOT NULL

-- （ ）
mysql> CREATE TABLE IF NOT EXISTS userf(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password CHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT userf(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT userf(username,password,age) VALUES('KING1','KING1',12);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *FROM userf;
+----+----------+----------+------+
| id | username | password | age  |
+----+----------+----------+------+
|  1 | KING     | KING     | NULL |
|  2 | KING1    | KING1    |   12 |
+----+----------+----------+------+
2 rows in set (0.00 sec)
-- 
mysql> INSERT userf(username,password) VALUES(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null

1.1.4デフォルトDEFAULTのテスト

アイコンはデフォルトの画像を設定できます

CREATE TABLE IF NOT EXISTS userg(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT ' ',
sex ENUM(' ',' ',' ') NOT NULL DEFAULT ' '
); 
INSERT userg(username,password) VALUES('KING','KING');


mysql> SELECT * FROM userg;
+----+----------+----------+------+------+-----+
| id | username | password | age  | addr | sex |
+----+----------+----------+------+------+-----+
|  1 | KING     | KING     |   18 |       |      |
+----+----------+----------+------+------+-----+

-- 

INSERT userg VALUES(3,'QUEEN','QUEEN',29,' ',' ');
INSERT userg VALUES(4,'QUEEN','QUEEN',DEFAULT,DEFAULT,' ');

1.1.5唯一UNIQUE KEYをテストする

フィールドが重複できない場合に使用

CREATE TABLE IF NOT EXISTS userj(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);
mysql> DESC userj;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | YES  | UNI | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> SHOW CREATE TABLE userj;
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
| Table | Create Table

                                                                                |
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
| userj | CREATE TABLE `userj` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> INSERT userj(username) VALUES('A');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT userj(username) VALUES('A1');
Query OK, 1 row affected (0.00 sec)
--NULL 
mysql> SELECT * FROM userj;
+----+----------+------+
| id | username | card |
+----+----------+------+
|  1 | A        | NULL |
|  2 | A1       | NULL |
+----+----------+------+
2 rows in set (0.00 sec)
-- username 
mysql> INSERT userj(username) VALUES('A1');
ERROR 1062 (23000): Duplicate entry 'A1' for key 'username'
mysql> INSERT user9(username,card) VALUES('B','111');
Query OK, 1 row affected (0.00 sec)
--NULL 
mysql> INSERT userj(username,card) VALUES('B1',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT userj(username,card) VALUES('B2',NULL);
Query OK, 1 row affected (0.00 sec)

1.1.6総括

CREATE TABLE [IF NOT EXISTS] tbl_name(
    [UNSIGNED|ZEROFILL] [NULL|NOTNULL] [DEFAULT ] [[PRIMARY] KEY| UNIQUE[KEY]] [AUTO_INCREMENT ]  
)ENGINE=INNODB, CHARSET=utf8, AUTO_INCREMENT=100;

テストはどの程度書けば十分ですか?

[SQL] The Blunder