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

Question

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
);

Answer 1

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).