SQL创建2个有约束的表

Question

从第一个表开始:创建两个有约束的表

CREATE TABLE department
(
depid varchar2(3) CONSTRAINT PKdepid PRIMARY KEY,
dname varchar2(10) NOT NULL
);

创建第一个表。

开始第二个表:

CREATE TABLE employee
(
eid number CONSTRAINT PKEID PRIMARY KEY, 
ename varchar2(10), 
depid varchar2(3) 
CONSTRAINT FKDEPID 
FOREIGN KEY REFERENCE dep(depid),
designation varchar2(10),
salary number CHECK (salary > 10000),
doj date
);
CREATE TABLE employee
(
eid number CONSTRAINT PKEID PRIMARY KEY, 
ename varchar2(10), 
depid varchar2(3) 
CONSTRAINT FKDEPID 
FOREIGN KEY REFERENCE dep(depid),
designation varchar2(10),
salary number CHECK (salary > 10000),
doj date
)

ERROR at line 1:

ORA-02253:这里不允许约束规范

在第二种情况下应用

CREATE TABLE employee
(
eid number CONSTRAINT PKEID PRIMARY KEY, 
ename varchar2(10), 
depid varchar2(3) 
CONSTRAIN FKDEPID 
FOREIGN KEY REFERENCE dep(depid),
designation varchar2(10),
salary number CHECK (salary > 10000),
doj date
);
CREATE TABLE employee
(
eid number CONSTRAINT PKEID PRIMARY KEY, 
ename varchar2(10), 
depid varchar2(3) 
CONSTRAIN FKDEPID 
FOREIGN KEY REFERENCE dep(depid),
designation varchar2(10),
salary number CHECK (salary > 10000),
doj date
)

ERROR at line 1:

ORA-00907:缺少右括号

我只是想创建一个表

Answer 1

您不应该在内联外键定义中指定foreign key，只指定references(而不是当前的reference)。另外，注意您的语句引用了表dep，而您的另一个表实际上称为department:

CREATE TABLE employee (
eid NUMBER CONSTRAINT pkeid PRIMARY KEY, 
ename VARCHAR2(10), 
depid VARCHAR2(3) CONSTRAINT fkdepid REFERENCES department(depid), -- Here!
designation VARCHAR2(10),
salary NUMBER CHECK(salary>10000),
doj DATE);