帮忙看一个关于外键的问题,
大致意思如下:
四张表:A、B、C、D
A、B、C中都有一个id,
在D中需要包含A、B、C中的ID外键约束怎么写?
我写的报错
我的代码如下:
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>
大致意思如下:
四张表:A、B、C、D
A、B、C中都有一个id,
在D中需要包含A、B、C中的ID外键约束怎么写?
我写的报错
我的代码如下:
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_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