Oracle : Foreign Key Error : Unknown Command

Question

Every time i try to make a Foreign Key or try to do a ON DELETE CASCADE

I get errors like this :

Error starting at line 9 in command:
CONSTRAINT tp_landlordrole_FK FOREIGN KEY (zillowuseraccountid)
Error report:
Unknown Command

The following is an example of my code

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

CONSTRAINT tp_landlordrole_FK FOREIGN KEY (zillowuseraccountid)
    REFERENCES  tp_zillowuseraccount(zillowuseraccountid) ON DELETE CASCADE  ;

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

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

Any suggestions are welcome!!

Gordon Linoff · Accepted Answer · 2018-03-31 22:45:07Z

1

I believe you just need an alter table statement:

ALTER TABLE tp_landlordrole 
    ADD CONSTRAINT tp_landlordrole_FK
        FOREIGN KEY (zillowuseraccountid) REFERENCES  tp_zillowuseraccount(zillowuseraccountid) ON DELETE CASCADE  ;

However, you can define the foreign key directly in the table definition.

CREATE INDEX and CREATE SEQUENCE do not need ALTER TABLE.

answered Mar 31, 2018 at 22:45

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ArtBajji · Accepted Answer · 2018-04-01 10:56:57Z

There are multiple ways of defining constraints as per OracleDocumentation:

a. Inline Constraints:

create table par(n number not null constraint par_pk primary key);
create table chld(m number constraint chld_pk primary key, 
                  n number not null constraint chld_fk references par(n) ON DELETE CASCADE);

drop table chld;
drop table par;

b. Out of line constraints:

create table par(n number, constraint par_pk primary key(n));
create table chld(m number, n number not null, constraint chld_pk primary key(m), constraint chld_fk foreign key(n) references par(n) ON DELETE CASCADE);

drop table chld;
drop table par;

c. Separate ALTER TABLE statement.

create table par(n number);
alter table par add constraint par_pk primary key(n);
create table chld(m number, n number not null);
alter table chld add constraint chld_pk primary key(m);
alter table chld add constraint chld_fk foreign key(n) references par(n) ON DELETE CASCADE;

In your case you need to use the correct syntax for out of line constraints.

jose_bacoy · Accepted Answer · 2018-03-31 23:43:51Z

You need to add another ALTER TABLE command before you can add a constraint. Here is the complete commands assuming you also need to define the parent table tp_zillowuseraccount.

CREATE TABLE tp_zillowuseraccount(zillowuseraccountid NUMBER(20),
  PRIMARY KEY ( zillowuseraccountid ));

CREATE TABLE tp_landlordrole
(
    landlordroleid          NUMBER(20) NOT NULL,
    zillowuseraccountid         NUMBER(20) NOT NULL,
    numberofpropertiesowned     Number(6),
PRIMARY KEY ( landlordroleid ));

ALTER TABLE  tp_landlordrole 
ADD CONSTRAINT tp_landlordrole_FK FOREIGN KEY (zillowuseraccountid)
    REFERENCES  tp_zillowuseraccount(zillowuseraccountid) ON DELETE CASCADE;

CREATE INDEX tp_landlordrole_I ON tp_landlordrole
( zillowuseraccountid );
CREATE SEQUENCE tp_landlordroleid_seq START WITH 0 MINVALUE 0 NOCACHE;

Collectives™ on Stack Overflow

Oracle : Foreign Key Error : Unknown Command

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related