oracle ORA-00907: 创建表时缺少右括号错误？

ORA-00907: missing right parenthesis Error while creating a table?

提问by Nina

I am new to oracle, I have created two tables using following queries,

我是 oracle 的新手，我使用以下查询创建了两个表，

CREATE TABLE employee
(
 emp_name VARCHAR(20) NOT NULL,
 street VARCHAR(50) NOT NULL,
 city VARCHAR(20) NOT NULL,
 PRIMARY KEY(emp_name)
)

and

和

CREATE TABLE company
(
 comp_name VARCHAR(20) NOT NULL,
 city VARCHAR(20) NOT NULL,
 PRIMARY KEY(comp_name)
)

Now I am trying to create another table using some foreign keys,

现在我正在尝试使用一些外键创建另一个表，

CREATE TABLE works
(
emp_name varchar(20) NOT NULL,
comp_name varchar(20) NOT NULL,
salary  int(10) NOT NULL,
FOREIGN KEY(emp_name) REFERENCES employee(emp_name),
FOREIGN KEY(comp_name) REFERENCES company(comp_name)
)

Getting ERROR : ORA-00907: missing right parenthesis

出现错误：ORA-00907：缺少右括号

I have also tried with

我也试过

CREATE TABLE works
(
emp_name varchar(20) NOT NULL,
comp_name varchar(20) NOT NULL,
salary  int(10) NOT NULL,
constraint wemployee FOREIGN KEY(emp_name) REFERENCES employee(emp_name),
constraint wcompany FOREIGN KEY(comp_name) REFERENCES company(comp_name)
)

But getting same error. Can any one tell me that where I am doing mistake?

但得到同样的错误。谁能告诉我我在哪里做错了？

采纳答案by pilotcam

I'm no expert in oracle, but are you allowed to specify the (10)in salary int(10) NOT NULL?

我不是 oracle 的专家，但是你可以指定(10)insalary int(10) NOT NULL吗？

回答by Raakhee Bora

There are two different ways to create a table with constraints:

有两种不同的方法可以创建带有约束的表：

1)

1)

   create table department(
   deptno number(5) primary key,
   deptname varchar2(30),
   empno number(5) references emp(empno));

2)

2)

   create table department(
   deptno number(5),
   deptname varchar2(30),
   empno number(5),
   constraint pkey_deptno primary key(deptno),
   constraint fkey_empno foreign key(empno) references Emp(empno));  

回答by Sheel

1: you should have a table called "test" with two columns, id and testdata. (This is just a dumb quick example, so I won't bother to specify any constraints on id.)

1：您应该有一个名为“test”的表，其中包含两列，id 和 testdata。（这只是一个愚蠢的快速示例，所以我不会费心指定对 id 的任何约束。）

create table test (id number, testdata varchar2(255));

2: Next we'll create a sequence to use for the id numbers in our test table.

2：接下来，我们将创建一个序列以用于我们测试表中的 id 号。

create sequence test_seq 
start with 1 
increment by 1 
nomaxvalue;

You could change "start with 1" to any number you want to begin with (e.g. if you already have 213 entries in a table and you want to begin using this for your 214th entry, replace with "start with 214"). The "increment by 1" clause is the default, so you could omit it. You could also replace it with "increment by n" if you want it to skip n-1 numbers between id numbers. The "nomaxvalue" tells it to keep incrementing forever as opposed to resetting at some point.i (I'm sure Oracle has some limitation on how big it can get, but I don't know what that limit is).

3: Now we're ready to create the trigger that will automatically insert the next number from the sequence into the id column.

您可以将“从 1 开始”更改为您想要开始的任何数字（例如，如果表中已有 213 个条目，并且您想开始将其用于第 214 个条目，请替换为“以 214 开始”）。“increment by 1”子句是默认的，所以你可以省略它。如果您希望它在 id 编号之间跳过 n-1 个数字，您也可以将其替换为“增加 n”。“nomaxvalue”告诉它永远保持递增，而不是在某个点重置。i（我确定 Oracle 对它的大小有一些限制，但我不知道该限制是什么）。

3：现在我们准备好创建触发器，它会自动将序列中的下一个数字插入到 id 列中。

create trigger test_trigger
before insert on test
for each row beginselect test_seq.nextval into :new.id from dual;
end;
/

回答by leepowers

When creating the index inline with the rest of the table creation statement try dropping the FOREIGN KEYpart:

在创建与表创建语句的其余部分内联的索引时，请尝试删除该FOREIGN KEY部分：

CREATE TABLE works
(
emp_name varchar(20) NOT NULL,
comp_name varchar(20) NOT NULL,
salary  int(10) NOT NULL,
emp_name REFERENCES employee(emp_name),
comp_name REFERENCES company(comp_name)
)

See this question for more details:

有关更多详细信息，请参阅此问题：

ORA-00907: missing right parenthesis

ORA-00907: 缺少右括号