mysql_约束+外键

约束

唯一约束：unique

非空约束：not null

主键约束：primary key

添加数据自动创建：id int primary key auto_increment

初始化为当前时间：date_time datetime default current_timestamp

随改变 时间改变为当前时间：date_time datetime default current_timestamp on update current_timestamp

1. 创建时添加约束:

create table ‘表’(

id int primary key auto_increment,

name char not null ,

data_time datetime default current_timestamp,

);

1. 修改约束：

alter table ‘表’ modify name char not null;

--------------------------------------------------------------------------------------------------------------------------

外键约束：constraint fkseat foreign key(my_id) references ‘表’(id)

mysql> create table seats(id int primary key,name varchar(20) not null );

mysql> desc seats;

+-------+-------------+------+-----+---------+-------+

| Field | Type   | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id  | int     | NO  | PRI | NULL |    |

| name | varchar(20) | NO  |   | NULL |     |

+-------+-------------+------+-----+---------+-------+

mysql> insert into seats values(1,"一组"),(2,"二组"),(3,"三组"),(4,"四组");

mysql> select * from seats;

+----+------+

| id | name|

+----+------+

| 1 | 一组|

| 2 | 二组|

| 3 | 三组|

| 4 | 四组|

+----+------+

mysql> create table students(id int primary key,name varchar(20) not null,age int not null,seatid int,constraint fkseat foreign key(seatid) references seats(id));

mysql> insert into students values(1, "李琰", 18, "1"), (7, "秦晓光", 17, "2"),(8, "杨润国", 19, "2"), (13, "周雨辉", 16, "3");

mysql> select * from students;

+----+--------+-----+--------+

| id | name| age | seatid |

+----+--------+-----+--------+

| 1 | 李琰 | 18 |  1  |

| 7 |秦晓光| 17 |  2  |

| 8 |杨润国| 19 |  2  |

| 13 |周雨辉| 16 |  3  |

+----+--------+-----+--------+

mysql> insert into students values(20,"诸葛亮",20,"5");

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`day1`.`students`, CONSTRAINT `fkseat` FOREIGN KEY (`seatid`) REFERENCES `seats` (`id`))

mysql> insert into students values(20,"诸葛亮",20,"4");

mysql> select * from students;

+----+--------+-----+--------+

| id | name | age | seatid |

+----+--------+-----+--------+

| 1 | 李琰 | 18 |  1  |

| 7 |秦晓光| 17 |  2  |

| 8 |杨润国| 19 |  2  |

|13|周雨辉| 16 |  3  |

|20|诸葛亮| 20 |  4  |

+----+--------+-----+--------+