Create Datasheet primary key foreign key

1. Create a data table

Stored as rows and columns, each row has a unique record, and each column represents a field or field in the record.

Format: the name of the table is case-insensitive and cannot use the SQL keyword; multiple columns are separated by commas.

Create table (field 1, data type [column constraint], field 2, data type [column constraint], field 3, data type [column constraint],. [table level constraints])

Test: table name test01

| | Field name | data type | |

| | name | varchar (30) |

| | id | int (11) | |

(1) Primary key constraint

Single field

Mysql > create table test02-> (id int (11) primary key,-> name varchar (30); Query OK, 0 rows affected (0.08 sec) mysql > create table test03-> (id int (11),-> name varchar (30),-> primary key (id)); Query OK, 0 rows affected (0.10 sec) mysql > desc test02 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | PRI | NULL | name | varchar (30) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > desc test03 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | PRI | 0 | name | varchar (30) | YES | | NULL | | +-+-+ 2 rows in set (0.01 sec)

Column implies a rowid field

The table will explicitly require id to be a recognizable flag.

Multiple field

Mysql > create table test05 (id int (11), name varchar (30), primary key (id,name)); Query OK, 0 rows affected (0.11 sec)

Delete primary key constraint

Mysql > alter table test0004 drop primary key; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql > show create table test0004\ G * * 1. Row * * Table: test0004 Create Table: CREATE TABLE 'test0004` (`username` varchar (10) NOT NULL, `pid` smallint (5) unsigned DEFAULT NULL, `id`smallint (5) unsigned NOT NULL DEFAULT '0mm, `age` tinyint (3) unsigned NOT NULL UNIQUE KEY `username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql > desc test0004 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -- + | username | varchar (10) | NO | PRI | NULL | pid | smallint (5) unsigned | YES | | NULL | id | smallint (5) unsigned | NO | | 0 | age | tinyint (3) unsigned | NO | | NULL | | +-+-- + 4 rows in set (0.00 sec)

How to add a primary key to an existing table?

For example:

Create a notesheet, and then add a field

Mysql > create table test0004 (username varchar (10) not null, pid smallint unsigned); Query OK, 0 rows affected (0.13 sec) mysql > desc test0004 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (10) | NO | | NULL | | pid | smallint (5) unsigned | YES | | NULL | | +-+-- + -+ 2 rows in set (0.00 sec) mysql > alter table test0004 add id smallint unsigned Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0

Add a primary key and verify

Mysql > alter table test0004 add constraint PK_test0004_id primary key (id); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test0004 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (10) | NO | | NULL | | pid | smallint (5) unsigned | YES | | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | +-+- -+ 3 rows in set (0.00 sec) mysql > show create table test0004\ G * * 1. Row * * Table: test0004Create Table: CREATE TABLE `test0004` (`username` varchar (10) NOT NULL, `pid` smallint (5) unsigned DEFAULT NULL, `id` smallint (5) unsigned NOT NULL DEFAULT '0mm, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Let's add a unique constraint to take a look.

Mysql > alter table test0004 add unique (username); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table test0004\ G * * 1. Row * * Table: test0004Create Table: CREATE TABLE `test0004` (`username` varchar (10) NOT NULL, `pid` smallint (5) unsigned DEFAULT NULL, `id`smallint (5) unsigned NOT NULL DEFAULT '0mm, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Next, we add a field to him and modify and delete the default value operation.

Mysql > alter table test0004 add age tinyint unsigned not null;Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test0004 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (10) | NO | UNI | NULL | pid | smallint (5) unsigned | YES | | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | age | tinyint (3) unsigned | NO | NULL | | | +-+-+ 4 rows in set (0.00 sec) mysql > alter table test0004 alter age set default 15 | Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test0004 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (10) | NO | UNI | NULL | pid | smallint (5) unsigned | YES | | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | age | tinyint (3) unsigned | NO | 15 | | | +-+-+ 4 rows in set (0.00 sec) mysql > alter table test0004 alter age drop default | Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

(2) Foreign bond bondage

A table can have one or more foreign keys; ensure the consistency and integrity of the data; after defining foreign keys, no

Allows you to delete records that have associated relationships in another table.

Primary table: a table with a primary key that is associated with two tables

Slave table: a table with a foreign key that is associated with two tables

Constraint foreign key references primary key column

Mysql > create table test06-> (id int (11) primary key,-> name varchar (30) not null); Query OK, 0 rows affected (0.16 sec) mysql > create table test07 (id int (11) primary key, name varchar (30), constraint test0607 foreign key (id) references test06 (id)); Query OK, 0 rows affected (0.19 sec)

Deepen:

First create a provin1 table

Mysql > create table provin1 (id smallint unsigned primary key auto_increment, pnaame varchar (20) not null); Query OK, 0 rows affected (0.09 sec) mysql > show create table provin1\ G * * 1. Row * * Table: provin1Create Table: CREATE TABLE `provin1` (`id` smallint (5) unsigned NOT NULL AUTO_INCREMENT, `pname` varchar (20) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Create another test0003 table

Mysql > create table test0003 (id smallint unsigned primary key auto_increment, ussername varchar (10) not null, pid smallint unsigned, foreign key (pid) referencess provin1 (id) on delete cascade); Query OK, 0 rows affected (0.08 sec) mysql > show create table test0003\ G * * 1. Row * * Table: test0003Create Table: CREATE TABLE `test0003` (`id`smallint (5) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (10) NOT NULL, `pid` smallint (5) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`pid`) CONSTRAINT `test0003_ibfk_ 1` FOREIGN KEY (`pid`) REFERENCES `provin1` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)

Insert data into provin1

Mysql > insert provin1 (pname) values ('A'); Query OK, 1 row affected (0.00 sec) mysql > insert provin1 (pname) values ('b'); Query OK, 1 row affected (0.00 sec) mysql > insert provin1 (pname) values ('C'); Query OK, 1 row affected (0.00 sec) mysql > select * from provin1 +-+ | id | pname | +-+-+ | 1 | A | 3 | b | | 5 | C | +-+-+ 3 rows in set (0.00 sec)

Insert data into test0003

Mysql > insert test0003 (username,pid) values ('tom',3); Query OK, 1 row affected (0.00 sec) mysql > insert test0003 (username,pid) values (' lichao',5); Query OK, 1 row affected (0.00 sec) mysql > insert test0003 (username,pid) values ('chenchen',1); Query OK, 1 row affected (0.00 sec) mysql > insert test0003 (username,pid) values (' cat',3) Query OK, 1 row affected (0.00 sec) mysql > insert test0003 (username,pid) values ('nihao',7); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`chenchen`.`test0003`, CONSTRAINT`test0003 _ ibfk_ 1` FOREIGN KEY (`pid`) REFERENCES `provin1` (`id`) ON DELETE CASCADE)

We see that there is an error when inserting pid = 7. The reason for the error is that there is no 7 id in the provin1 table corresponding to pid, so he will report an error. Let's take a look at the data in the test0003 table.

Mysql > select * from test0003;+----+ | id | username | pid | +-- + | 1 | tom | 3 | 3 | lichao | 5 | 5 | chenchen | 1 | 7 | cat | 3 | +-+ 4 rows in set (0.01sec)

So let's remove the field with an id of 3 in provin1 to see the changes in the test0003 table.

Mysql > delete from provin1 where id = 3bot query OK, 1 row affected (0.00 sec) mysql > select * from provin1;+----+-+ | id | pname | +-- +-+ | 1 | A | 5 | C | +-+-+ 2 rows in set (0.00 sec) mysql > select * from test0003 +-+ | id | username | pid | +-+ | 3 | lichao | 5 | 5 | chenchen | 1 | +-+ 2 rows in set (0.00 sec)

We see that the corresponding field with id 3 in the test0003 table disappears after removing the data with id 3 in provin1, which verifies the cascade function in the foreign key bondage.

Cascade: rows that are deleted or updated from the parent table and automatically deleted or matched to the new child table.

Below I will sort out other constraints and mysql table operation documents, one after another update, this article is forbidden to reprint, personal summary is not easy, please understand