mysql 创建 表 sql语句 带主键和外键

本文示例使用 Birt 自带的 Sample 数据源，用到的数据基于图 1 所示的实体关系模型。从这个模型中可以看出，文中示例用到三张数据库表分别为 customers，orders 和 orderdetails。其中 customers 表的 customernumber 字段作为外键出现在 orders 表中，而 orders 表的 ordernumber 字段做为外键出现在 orderdetails 表中。

　

create TABLE Customers(customerNumber integer not null,
                                      customerName varchar(60),
                                        phone varchar(50),
                                        contactLastName varchar(30),
                                        contactFirstName varchar(30),
                                        addressLine1 varchar(50),
                                        addressLine2 varchar(50),city varchar(50),
                                        state varchar(30),
                                        postalCode varchar(15),
                                        country varchar(30),
                                        salesRepEmployeeNumber integer,
                                        creditLimit decimal(10,4),
                                        primary key(customerNumber)
) type=InnoDB;

--drop table orders;

create table orders(orderNumber integer not null,orderDate date,
requireDate date,shippedDate date,status1 varchar(20),
comments varchar(255),
cusNumber integer,
index cus(cusNumber),
primary key(orderNumber),
foreign key (cusNumber) references Customers(customerNumber)
) type=InnoDB;

create table OrderDetails (orderNum integer,
                          productCode integer,
                          quantityOrdered integer,
                          priceEach decimal(10,4),
                          orderLineNumber smallint,
                          index orderN(orderNum),
                          foreign KEY (orderNum) references orders(orderNumber)
                          )type=InnoDB;