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

创建时添加约束:

create table ‘表’(

id int primary key auto_increment,

name char not null ,

data_time datetime default current_timestamp,

);

修改约束：

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;

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

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

| id | int | NO | PRI | 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 |

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