Oracle SQL开发。外键错误:未知命令

Question

每次我尝试做外键时，都会遇到这样的错误：

从命令第9行开始的错误： 约束tp_landlordrole_FK外键(useraccountid) 错误报告： 未知指挥

我的一个建议是

ALTER TABLE 
ADD CONSTRAINT

第一个表工作正常，但是当我尝试下一个表时，它没有工作，错误又回来了。

下面是我的代码示例：

PROMPT 'Creating Table landlordrole'
CREATE TABLE tp_landlordrole
(
    landlordroleid          NUMBER(20) NOT NULL,
    useraccountid           NUMBER(20) NOT NULL,
    numberofpropertiesowned     Number(6),
CONSTRAINT tp_landlordrole_PK PRIMARY KEY ( landlordroleid ) ) ; 

ALTER TABLE tp_landlordrole
ADD CONSTRAINT tp_landlordrole_FK FOREIGN KEY (useraccountid)
REFERENCES  tp_useraccount(useraccountid) ON DELETE CASCADE ;

PROMPT Creating Index 'tp_landlordrole_I'
CREATE INDEX tp_landlordrole_I ON tp_landlordrole
( useraccountid );

PROMPT 'Creating Sequence tp_landlordroleid_seq for the tp_landlordrole table'
CREATE SEQUENCE tp_landlordroleid_seq START WITH 0 MINVALUE 0 NOCACHE;



PROMPT 'Creating Table realtorrole'
CREATE TABLE tp_realtorrole
(
    realtorroleid                   NUMBER(20) NOT NULL,
    useraccountid               NUMBER(20) NOT NULL,
    currentrealestatecompanyname    VARCHAR2(20) NOT NULL,
CONSTRAINT  tp_realtorrole_PK PRIMARY KEY ( realtorroleid ) ) ;

ALTER TABLE tp_realtorrole
ADD CONSTRAINT  tp_realtorrole_FK FOREIGN KEY ( useraccountid )
REFERENCES  tp_useraccount(useraccountid) );

PROMPT Creating Index 'tp_realtorrole_I'
CREATE INDEX tp_realtorrole_I ON tp_realtorrole
( useraccountid );

PROMPT 'Creating Sequence tp_realtorroleid_seq for the tp_realtorrole table'
CREATE SEQUENCE tp_realtorroleid_seq START WITH 0 MINVALUE 0 NOCACHE;

欢迎任何建议。

Answer 1

代码应该更像这样：

PROMPT 'Creating Table landlordrole'
CREATE TABLE tp_landlordrole (
    landlordroleid          NUMBER(20) NOT NULL,
    useraccountid           NUMBER(20) NOT NULL,
    numberofpropertiesowned     Number(6),
    CONSTRAINT tp_landlordrole_PK PRIMARY KEY ( landlordroleid )
);

ALTER TABLE tp_landlordrole
    ADD CONSTRAINT tp_landlordrole_FK FOREIGN KEY (useraccountid)
        REFERENCES  tp_useraccount(useraccountid) ON DELETE CASCADE;

PROMPT 'Creating Index tp_landlordrole_I'
CREATE INDEX tp_landlordrole_I ON tp_landlordrole(useraccountid);

PROMPT 'Creating Sequence tp_landlordroleid_seq for the tp_landlordrole table'
CREATE SEQUENCE tp_landlordroleid_seq START WITH 0 MINVALUE 0 NOCACHE;

PROMPT 'Creating Table realtorrole'
CREATE TABLE tp_realtorrole (
    realtorroleid                   NUMBER(20) NOT NULL,
    useraccountid               NUMBER(20) NOT NULL,
    currentrealestatecompanyname    VARCHAR2(20) NOT NULL,
    CONSTRAINT  tp_realtorrole_PK PRIMARY KEY (realtorroleid)
);

ALTER TABLE tp_realtorrole
    ADD CONSTRAINT tp_realtorrole_FK FOREIGN KEY (useraccountid)
        REFERENCES tp_useraccount(useraccountid);

当然，除非定义了tp_useraccount，否则外键引用将失败。