在SQL Developer中创建表时收到错误ORA-00907 [英] getting error ORA-00907 while creating a table in sql developer

问题描述

我对sql开发人员很新鲜.我不确定规则.当我按照老师的示例练习创建一个小型图书馆数据库时，对于使用外键的每个表，我都会遇到该错误.我检查了示例，我确定我没有错过任何括号.我感到很困惑.请帮忙！

i am fresh to sql developer. i am not sure about the rules. when i practiced following teacher's example to create a small library database, i have that error to every table with foreign key. i check the example and i am sure i have not miss a parenthesis. i am so confused. please help!

create table Publisher
(Pname VARCHAR(25)  not null ,
 Address varchar(25) not null,
 Phone char(10),
 PRIMARY KEY (Pname));

create table Book
(Book_id int not null,
 Title varchar(20),
 Publisher_name varchar(25) ,
 primary KEY (Book_id),
    foreign key (Publisher_name) references Publisher(Pname)
      ON DELETE CASCADE   ON UPDATE CASCADE);

错误报告显示，在缺少右括号"的第一个级联和第二个级联之间.但我不这么认为.所有其他类似的构造也会发生类似的问题.我的SQL开发人员是Mac的最新版本.

error report shows that between first cascade and second on "missing right parenthesis". but i don't think so. similar question happens to all other similar construct. my sql developer is the latest version for mac.

推荐答案

我假设您每次更改Publisher表中的Pname列时都试图确保更新Book记录，但是Oracle没有on update cascade.

I assume you're trying to ensure that the Book records are updated whenever you change the Pname column in the Publisher table, but Oracle has no on update cascade.

有关语法图，请参见此处 references子句的

See here for the syntax diagram of the references clause.

要更改Oracle中的主键，您有几个选择:

To change primary keys in Oracle, you have a few options:

• 延迟约束(外部键)，直到提交为止，然后在事务中更新父级和子级.

• Defer the constraint (foreign key) check until commit, then update both parent and child within the transaction.

使用更新的详细信息创建 new 父级，然后更改所有子级，然后删除原始父级.当然，所有这些都是交易.

Create a new parent with the updated details then go change all the children, then delete the original parent. All in a transaction of course.

停止使用可变数据作为键. DBA花了很长时间才能说服我使用人工(代理)密钥，而不是真实的数据，但这是最终使我胜过的用例.使用人工密钥(无需更改)意味着该问题完全消失了.

Stop using mutable data as a key. It took a long time for DBAs to convince me that artificial (surrogate) keys should be used rather than true data, but this was the use case that finally won me over. Using an artificial key (that never has to change) means this problem disappears totally.

这篇关于在SQL Developer中创建表时收到错误ORA-00907的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！