mysql创建带主键的表表_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;