上次那两语句我真是受教了。不过我发现自己一点变通能力都没有。这次是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中按照年份分组,月份的销售额来查找
- Mysql数据库乱码问题?
- PHP time() 生成的时间存储在数据库中,如何在MySQL中得到他的年月日?month 和year都得不到,怎么解决?
- mysql创建自定义函数有个问题请教
- 遇到一mysql触发器的离奇现象,求解!
- mysql中SCOPE_IDENTITY 是否可用?
- 急寻上海的Soho/兼职软件开发人员做个小东西,进来看看需求
- MYSQL库中有一日期型字段RQ,怎么取RQ中的年、月、日?
- 两表间的统计查询_mysql
- 分页显示记录按照权重显示问题。
- 关于InnoDB_buffer_pool什么时候写入到文件中
- 重启发现死活找不到 mysql manager
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>