CODEWITHSUNDEEP
oracle-databaseforeign-keysconstraintsddl
Tamer Ibrahim
Tamer Ibrahim

Reputation: 21

CREATE TABLE with foreign key fails with ORA-00905: missing keyword

Whenever I try to reference the foreign key i get this error (missing keyword) Here is my code:

CREATE TABLE SP (
  S# CHAR(2) NOT NULL,
  P# CHAR(2) NOT NULL,
  QTY INTEGER,
  PRIMARY KEY (S#,P#),
  FOREIGN KEY (S#) REFERENCE Supplier(S#) ON DELETE CASCADE
);

Upvotes: 0

Views: 197

Answers (1)

GMB
GMB

Reputation: 222412

There are several problems with your code:

  • the syntax of the definition of your primary key is wrong; it should be CONSTRAINT <constraint_name> PRIMARY KEY (S#, P#)

  • in the definition of the foreign key, keyword REFERENCES is missing the final S

Consider:

CREATE TABLE SP (
  S# CHAR(2) NOT NULL,
  P# CHAR(2) NOT NULL,
  QTY INTEGER,
  CONSTRAINT SP_PK PRIMARY KEY (S#, P#),
  FOREIGN KEY (S#) REFERENCES Supplier(S#) ON DELETE CASCADE
);

Demo on DB Fiddle

Reminder: please note that the column referrenced by the primary key must be unique or a primary key in the referrenced table (ie column S# must be the primary key of table Supplier or have a UNIQUE constraint).

Upvotes: 1

Related Questions