上次那两语句我真是受教了。不过我发现自己一点变通能力都没有。这次是3张表关联,于是我模仿写成这样:(经检验这样写是错误的)
set @@foreign_key_checks=off;
drop table if exists PRODUCT
create table PRODUCT(
ID int(8) not null primary key auto_increment,
PRICE double,
DESCRIPTION varchar(1000)
);
drop table if exists ORDERS
create table ORDERS(
ID int(8) not null primary key auto_increment,
TOTAL double,
REALNAME varchar(20),
ADDRESS varchar(20),
POSTCODE varchar(20),
PHONE varchar(20)
);
drop table if exists ORDERITEM
create table ORDERITEM(
ORDER_ID int(8) not null,
PRODUCT_ID int(8) not null,
foreign key (ORDER_ID) references ORDERS (ID),
foreign key (PRODUCT_ID) references PRODUCT (ID)
);
set @@foreign_key_checks=on;
帮忙纠正一下
还有如果两张表共享主键又应该如何添加什么语句(在保留drop table if exists 表名的前提下)
drop table if exists USERS
create table USERS(
ID int(8) not null primary key auto_increment,
USERNAME varchar(40),
PASSWORD varchar(40)
);drop table if exists PROFILE
create table PROFILE(
ID int(8) not null primary key auto_increment,
EMAIL varchar(100),
ADDRESS varchar(200),
POSTCODE varchar(8),
MOBILE varchar(11),
PHONE varchar(16)
);
set @@foreign_key_checks=off;
drop table if exists PRODUCT
create table PRODUCT(
ID int(8) not null primary key auto_increment,
PRICE double,
DESCRIPTION varchar(1000)
);
drop table if exists ORDERS
create table ORDERS(
ID int(8) not null primary key auto_increment,
TOTAL double,
REALNAME varchar(20),
ADDRESS varchar(20),
POSTCODE varchar(20),
PHONE varchar(20)
);
drop table if exists ORDERITEM
create table ORDERITEM(
ORDER_ID int(8) not null,
PRODUCT_ID int(8) not null,
foreign key (ORDER_ID) references ORDERS (ID),
foreign key (PRODUCT_ID) references PRODUCT (ID)
);
set @@foreign_key_checks=on;
帮忙纠正一下
还有如果两张表共享主键又应该如何添加什么语句(在保留drop table if exists 表名的前提下)
drop table if exists USERS
create table USERS(
ID int(8) not null primary key auto_increment,
USERNAME varchar(40),
PASSWORD varchar(40)
);drop table if exists PROFILE
create table PROFILE(
ID int(8) not null primary key auto_increment,
EMAIL varchar(100),
ADDRESS varchar(200),
POSTCODE varchar(8),
MOBILE varchar(11),
PHONE varchar(16)
);
mysql> set @@foreign_key_checks=off;
Query OK, 0 rows affected (0.03 sec)mysql> drop table if exists PRODUCT;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create table PRODUCT(
-> ID int(8) not null primary key auto_increment,
-> PRICE double,
-> DESCRIPTION varchar(1000)
-> );
Query OK, 0 rows affected (0.06 sec)mysql> drop table if exists ORDERS;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create table ORDERS(
-> ID int(8) not null primary key auto_increment,
-> TOTAL double,
-> REALNAME varchar(20),
-> ADDRESS varchar(20),
-> POSTCODE varchar(20),
-> PHONE varchar(20)
-> );
Query OK, 0 rows affected (0.08 sec)mysql> drop table if exists ORDERITEM;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create table ORDERITEM(
-> ORDER_ID int(8) not null,
-> PRODUCT_ID int(8) not null,
-> foreign key (ORDER_ID) references ORDERS (ID),
-> foreign key (PRODUCT_ID) references PRODUCT (ID)
-> );
Query OK, 0 rows affected (0.08 sec)mysql> set @@foreign_key_checks=on;
Query OK, 0 rows affected (0.00 sec)mysql> set @@foreign_key_checks=off;
Query OK, 0 rows affected (0.00 sec)mysql> drop table if exists PRODUCT;
Query OK, 0 rows affected (0.06 sec)mysql> create table PRODUCT(
-> ID int(8) not null primary key auto_increment,
-> PRICE double,
-> DESCRIPTION varchar(1000)
-> );
Query OK, 0 rows affected (0.06 sec)mysql> drop table if exists ORDERS;
Query OK, 0 rows affected (0.03 sec)mysql> create table ORDERS(
-> ID int(8) not null primary key auto_increment,
-> TOTAL double,
-> REALNAME varchar(20),
-> ADDRESS varchar(20),
-> POSTCODE varchar(20),
-> PHONE varchar(20)
-> );
Query OK, 0 rows affected (0.06 sec)mysql> drop table if exists ORDERITEM;
Query OK, 0 rows affected (0.03 sec)mysql> create table ORDERITEM(
-> ORDER_ID int(8) not null,
-> PRODUCT_ID int(8) not null,
-> foreign key (ORDER_ID) references ORDERS (ID),
-> foreign key (PRODUCT_ID) references PRODUCT (ID)
-> );
Query OK, 0 rows affected (0.08 sec)mysql> set @@foreign_key_checks=on;
Query OK, 0 rows affected (0.00 sec)mysql>