使用复合主键创建表

I am trying to create a table (ORDERS). The table will have customer (CNO), product(PNO), employee (ENO), and order_number(ONO) as Primary Key. I am having problems creating the Orders table.

我正在尝试创建一个表(ORDERS)。该表将客户(CNO),产品(PNO),员工(ENO)和order_number(ONO)作为主键。我在创建Orders表时遇到问题。

Note: CNO, PNO, and ENO will be the composite key in the ORDERS table:

注意:CNO,PNO和ENO将是ORDERS表中的复合键:

CREATE TABLE EMPLOYEE
(ENO VARCHAR2(3)
,emp_fname VARCHAR2(20)
,emp_lname  VARCHAR2(20)
,job_title VARCHAR2(20)
,emp_sex VARCHAR2(1)
,emp_DOB date
,emp_status VARCHAR2(10)
,PRIMARY KEY (ENO)
);

CREATE TABLE CUSTOMER
(CNO VARCHAR2(3) 
,Cust_fname VARCHAR2(20)
,Cust_lname  VARCHAR2(20)
,Cust_SEX VARCHAR2(1)
,Cust_order_date VARCHAR2(14)
,Cust_ship_date date
,PRIMARY KEY (CNO)
);

CREATE TABLE PRODUCT
(PNO VARCHAR2(4)
,Prod_category VARCHAR2(20)
,Prod_name VARCHAR2(50)
,UnitrPrice number(20)
,Unitcost number(20)
,PRIMARY KEY (PNO)
);

CREATE TABLE ORDERS
(ONO VARCHAR2(4) 
,Order_QTY number(30)
,ENO VARCHAR2(3) NOT NULL
,PNO VARCHAR2(4) NOT NULL
,CNO VARCHAR2(3) NOT NULL
,CONSTRAINT ORDER_LINE_ENO_FK FOREIGN KEY (ENO) REFERENCES EMPLOYEE (ENO),
,CONSTRAINT ORDERS_LINE_FK FOREIGN KEY (CNO) REFERENCES CUSTOMER (CNO),
,CONSTRAINT ORDERS_LINE_FK FOREIGN KEY (PNO) REFERENCES PRODUCT (PNO)
,PRIMARY KEY (CNO,ENO,PNO)
);

2 个解决方案

#1

As you probably noticed, ORDER is a SQL keyword so you can't use it for a table name. (Your question originally referred to an ORDER table but your sample code used ORDERS, which fixed that problem but left it inconsistent with EMPLOYEE, CUSTOMER and PRODUCT. In my opinion, plural names are correct as well as useful for avoiding SQL keywords, but this seems to be something nobody will ever agree on.)

您可能已经注意到,ORDER是一个SQL关键字,因此您无法将其用于表名。 (您的问题最初是指ORDER表,但您的示例代码使用了ORDERS,它修复了该问题,但使其与EMPLOYEE,CUSTOMER和PRODUCT不一致。在我看来,复数名称对于避免使用SQL关键字也是正确的,但是这个似乎是没有人会同意的东西。)

I consider it good practice to define single-column foreign keys inline as part of the column definition, as then we can leave out the data type to let it inherit from the referenced column.

我认为将单列外键作为列定义的一部分内联定义是很好的做法,因为我们可以省略数据类型以使其从引用的列继承。

I also made your PK columns integer instead of strings, though this will depend on you your system generates them.

我还将您的PK列整数而不是字符串,但这取决于您的系统生成它们。

create table employees
( eno              integer primary key
, emp_fname        varchar2(20)
, emp_lname        varchar2(20)
, job_title        varchar2(20)
, emp_sex          varchar2(1)
, emp_dob          date
, emp_status       varchar2(10)
);

create table customers
( cno integer      primary key
, cust_fname       varchar2(20)
, cust_lname       varchar2(20)
, cust_sex         varchar2(1)
, cust_order_date  varchar2(14)
, cust_ship_date   date
);

create table products
( pno              integer primary key
, prod_category    varchar2(20)
, prod_name        varchar2(50) not null
, unitrprice       number(20)
, unitcost         number(20)
);

create table orders
( ono             integer
, order_qty       number(30)
, eno             not null constraint order_emp_fk references employees (eno)
, pno             not null constraint orders_prod_fk references products (pno)
, cno             not null constraint order_cust_fk references customers (cno)
, primary key (cno,eno,pno)
);

It seems odd that CUSTOMERS includes order and shipping dates. What if a customer places more than one order? Those should probably be part of the ORDERS table.

CUSTOMERS包含订单和发货日期似乎很奇怪。如果客户下了多个订单怎么办?那些应该是ORDERS表的一部分。

Do you really need to store personal information (cust_sex) about your customers? Bear in mind that this will involve you in privacy legislation compliance issues such as GDPR.

您真的需要存储有关您客户的个人信息(cust_sex)吗?请记住,这将涉及您在隐私立法合规性问题,如GDPR。

#2

Try below:

CREATE TABLE ORDERS
(ONO VARCHAR(4) 
,Order_QTY int
,ENO VARCHAR(3) NOT NULL
,PNO VARCHAR(4) NOT NULL
,CNO VARCHAR(3) NOT NULL
,PRIMARY KEY (CNO,ENO,PNO)
);

alter table ORDERS add CONSTRAINT ORDER_LINE_ENO_FK FOREIGN KEY (ENO) REFERENCES EMPLOYEE (ENO);
alter table ORDERS add CONSTRAINT ORDERS_LINE_FK FOREIGN KEY (CNO) REFERENCES CUSTOMER (CNO);
alter table ORDERS add CONSTRAINT ORDERS_LINE_FK FOREIGN KEY (PNO) REFERENCES PRODUCT (PNO);