关于外键解决方法

关于外键
帮忙看一个关于外键的问题，
大致意思如下：
四张表：A、B、C、D
A、B、C中都有一个id,
在D中需要包含A、B、C中的ID。。。外键约束怎么写？
我写的报错。。。


我的代码如下：

SQL code

mysql>mysql>mysql> create table if not exists c_type_tab(    -> c_type_id int primary key check (c_type_id > 0),    -> c_type_name varchar(30) not null unique,    -> c_type_description varchar(300)    -> )CHARACTER SET utf8 COLLATE utf8_general_ci comment = '客户类型表';Query OK, 0 rows affected (0.05 sec)mysql>mysql> insert into c_type_tab values (1,'金牌客户','');Query OK, 1 row affected (0.03 sec)mysql> insert into c_type_tab values (2,'银牌客户','');Query OK, 1 row affected (0.02 sec)mysql> insert into c_type_tab values (3,'铜牌客户','');Query OK, 1 row affected (0.01 sec)mysql> insert into c_type_tab values (4,'大客户','');Query OK, 1 row affected (0.02 sec)mysql> insert into c_type_tab values (5,'普通客户','');Query OK, 1 row affected (0.01 sec)mysql>mysql> create table if not exists c_card_type_tab(    -> c_card_type_id int primary key check (c_card_type_id > 0),    -> c_card_type_name varchar(30) not null unique,    -> c_card_type_description varchar(300)    -> )CHARACTER SET utf8 COLLATE utf8_general_ci comment = '客户证件类型表';Query OK, 0 rows affected (0.05 sec)mysql>mysql>mysql> insert into c_card_type_tab values (1,'身份证','');Query OK, 1 row affected (0.00 sec)mysql> insert into c_card_type_tab values (2,'学生证','');Query OK, 1 row affected (0.02 sec)mysql> insert into c_card_type_tab values (3,'军官证','');Query OK, 1 row affected (0.01 sec)mysql> insert into c_card_type_tab values (4,'士兵证','');Query OK, 1 row affected (0.02 sec)mysql> insert into c_card_type_tab values (5,'其他','');Query OK, 1 row affected (0.02 sec)mysql>mysql>mysql> create table if not exists c_state_id(    -> c_state_id int primary key check (c_state_id > 0),    -> c_state_name varchar(30) not null unique,    -> c_state_description varchar(300)    -> )CHARACTER SET utf8 COLLATE utf8_general_ci comment = '客户状态表';Query OK, 0 rows affected (0.06 sec)mysql>mysql> insert into c_state_id values (1,'有效','');Query OK, 1 row affected (0.01 sec)mysql> insert into c_state_id values (2,'冻结','');Query OK, 1 row affected (0.02 sec)mysql> insert into c_state_id values (3,'过期','');Query OK, 1 row affected (0.02 sec)mysql>mysql>mysql> create table if not exists custom_tab(    -> c_id int primary key auto_increment,    -> c_name varchar(30) not null,    -> c_type_id int not null,    -> c_gender int not null,    -> c_card_type_id int not null,    -> c_card_id float(30,0) not null,    -> c_work varchar(30) default '未知',    -> c_company_address varchar(50)  default '未知',    -> c_family_address varchar(50) default '未知',    -> c_phone float(15) not null unique,    -> c_email varchar(30) not null unique,    -> c_state_id int not null,    -> c_adddate datetime not null,    -> c_description varchar(300) default '无',    -> constraint fk_01 foreign key (c_type_id) references c_type_tab(c_type_id),    -> constraint fk_02 foreign key (c_card_type_id) references c_card_type_tab(c_card_type_id),    -> constraint fk_03 foreign key (c_state_id) references c_state_tab(c_state_id)    -> )CHARACTER SET utf8 COLLATE utf8_general_ci comment = '客户信息表';ERROR 1005 (HY000): Can't create table 'crm.custom_tab' (errno: 150)mysql>

[解决办法]
先分别创建c_type_tab，c_card_type_tab，c_state_id表，不插入数据，然后创建custom_tab表，不插入数据。最后先往c_type_tab，c_card_type_tab，c_state_id表中插入数据，最后往custom_tab表插入数据。
[解决办法]
mysql> create table if not exists c_state_id(

-> c_state_id int primary key check (c_state_id > 0),
-> c_state_name varchar(30) not null unique,
-> c_state_description varchar(300)
-> )CHARACTER SET utf8 COLLATE utf8_general_ci comment = '客户状态表';

楼主表名写错了！ c_state_tab
[解决办法]
每天回帖即可获得10分可用分！

查看更多 下一篇