数据库——主外键实例

要求：
每个表的主外键 
客户的姓名不能为空值
邮箱不能重复
客户的性别(男，女)

商品goods(商品编号goods_ id，商品名goods_ name, 单价unitprice, 商品类别category,  供应商provider) 
create table goods(
goods_id tinyint primary key auto_increment ,
goos_name varchar(100) not null default ' ',
unitprice decimal (10,2) not null default 0,
category smallint not null default 0,
provider varchar(30) not null default ' '
);
 desc goods;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| goods_id  | int(10) unsigned | NO   | PRI | NULL    |       |
| goos_name | varchar(100)     | NO   |     |         |       |
| unitprice | decimal(10,2)    | NO   |     | 0.00    |       |
| category  | smallint(6)      | NO   |     | 0       |       |
| provider  | varchar(30)      | NO   |     |         |       |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

客户customer(客户号customer_ id,姓名name,住址address,邮箱email,性别sex，身份证card_id) 
create table customer(
customer_id int unsigned primary key auto_increment,
name varchar(100) not null default ' ',
address varchar(100) not null default ' ',
email varchar(30) not null unique,
sex char(2) not null default '男',
card_id varchar(20) not null unique
);

 desc custome;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| customer_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100)     | NO   |     |         |                |
| address     | varchar(100)     | NO   |     |         |                |
| email       | varchar(30)      | NO   | UNI | NULL    |                |
| sex         | char(2)          | NO   |     | 男      |                |
| card_id     | varchar(20)      | NO   | UNI | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
6 rows in set (0.08 sec)

购买purchase(购买订单号order_ id,客户号customer_ id,商品号goods_ id,购买数量nums)
create table purchase(
order_id varchar(30) not null primary key,
customer_id int unsigned,
goods_id int unsigned,
num int not null default 0,
foreign key(customer_id) references customer(customer_id),
foreign key(goods_id) references goods(goods_id)
);
desc purchase;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| order_id    | varchar(30)      | NO   | PRI | NULL    |       |
| customer_id | int(10) unsigned | YES  | MUL | NULL    |       |
| goods_id    | int(10) unsigned | YES  | MUL | NULL    |       |
| num         | int(11)          | NO   |     | 0       |       |
+-------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)