大家好,我是SQL的新手,我想在我的书中回答这个问题:
7.5为EMPLOYEE表写一个CREATE TABLE语句。Email是必需的,是一个备用键,Department的默认值是Human Resources。从DEPARTMENT级联更新但不删除到EMPLOYEE。
我在Oracle iSQL*Plus中运行查询,我成功地创建了部门表,但是当我尝试创建员工表时,同时满足这些要求,我在第12行得到缺失的关键字错误
constraint DepartmentFK FOREIGN KEY(DepartmentName),
这是整个查询(我在尝试整个查询之前删除了department表,它仍然给出相同的错误)
CREATE TABLE DEPARTMENT (
DepartmentName char(35) NOT NULL,
BudgetCode char(30) NOT NULL,
OfficeNumber char(15) NOT NULL,
Phone char(12) NOT NULL,
Constraint DepartmentPK PRIMARY KEY(DepartmentName)
);
CREATE TABLE EMPLOYEE (
ProjectID int NOT NULL,
Name char(30) NOT NULL,
Department char(15) NOT NULL,
MaxHours int NOT NULL,
StartDate char(8) NULL,
EndDate char(8) NULL,
Email char(30) DEFAULT 'Human Resources' NOT NULL,
Constraint EmployeePK PRIMARY KEY(ProjectID),
Constraint EmployeeAK1 UNIQUE(Email),
constraint DepartmentFK FOREIGN KEY(DepartmentName),
references DEPARTMENT(DepartmentName)
ON UPDATE CASCADE
ON DELETE no ACTION
);
我试着遵循书中最相似的例子,查找外键约束和引用,但我不明白为什么我得到这个错误…
编辑:我去掉了逗号,但还是有两个错误:
CREATE TABLE DEPARTMENT (
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ON UPDATE CASCADE
*
ERROR at line 14:
ORA-00905: missing keyword
您必须删除您在
之后使用的comma
constraint DepartmentFK FOREIGN KEY(DepartmentName),
这是一个单位
constraint DepartmentFK FOREIGN KEY(DepartmentName) references DEPARTMENT(DepartmentName)
编辑:既然,你已经编辑了你的问题-
ON UDDATE CASCADE
选项在Oracle数据库中不可用,这就是为什么你得到一个错误。
你得到一个错误的Department
表,因为表已经存在,可能从上次运行!