Foreign Key problem, ORA-00907: missing right parenthesis on creating table & ORA-01735: invalid ALTER TABLE option

Question

my main problem is when I create these tables particularly the Member table, it does not add the foreign key constraints for the other tables created, I keep getting errors: ORA-00907: missing right parenthesis on creating table & ORA-01735: invalid ALTER TABLE option (if using the alter table options below the table creation, I am using oracle application express (apex.oracle.com)

I have searched other questions regarding this, but they were mostly related to stored procedures or querying a table, nothing with table creation.

CREATE TABLE MemberAddress (
 MemAddID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 Address VARCHAR2(60) NOT NULL,
 SecAddress VARCHAR2(60),
 PostCode VARCHAR2(8) NOT NULL,
 City VARCHAR2(40),
 CONSTRAINT MEM_ADD_PK PRIMARY KEY (MemAddID)
);

CREATE TABLE MemberType (
 MemTypeID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemType VARCHAR2(10),
 AccountStatus VARCHAR2(20),
 CONSTRAINT MEM_TYPE_PK PRIMARY KEY (MemTypeID)
);

CREATE TABLE Members (
 MemberID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemberFirstname VARCHAR2(50) NOT NULL,
 MemberMiddlename VARCHAR2(50),
 MemberLastname VARCHAR2(50) NOT NULL,
 MemberDOB DATE NOT NULL,
 Email VARCHAR2(60) NOT NULL UNIQUE,
 Mobile VARCHAR2(11) NOT NULL UNIQUE,
 Telephone VARCHAR2(13) UNIQUE,
 MemAddID NUMBER,
 MemTypeID NUMBER,
 CONSTRAINT MEM_ID_PK PRIMARY KEY (MemberID)
 CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE ON UPDATE CASCADE
);

I used these alter table options to test whether the SQL could add the foreign key out of table definition, however I get the error ORA-01735: invalid ALTER TABLE option.

-- ALTER TABLE Members ADD CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE ON UPDATE CASCADE;

-- ALTER TABLE Members ADD CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE ON UPDATE CASCADE;

I'm not new to SQL or a pro at it, if there is a best practise im not following here, let me know?

I do not understand what is wrong with this; whether its a syntax problem or oracle application express has problems, in MySQL it works great when the syntax is changed to suit that DB.

any suggestions?

Answer 1

Oracle database does not support ON UPDATE CASCADE clause.

references_clause::=

The below works:

CREATE TABLE Members (
 MemberID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemberFirstname VARCHAR2(50) NOT NULL,
 MemberMiddlename VARCHAR2(50),
 MemberLastname VARCHAR2(50) NOT NULL,
 MemberDOB DATE NOT NULL,
 Email VARCHAR2(60) NOT NULL UNIQUE,
 Mobile VARCHAR2(11) NOT NULL UNIQUE,
 Telephone VARCHAR2(13) UNIQUE,
 MemAddID NUMBER,
 MemTypeID NUMBER,
 CONSTRAINT MEM_ID_PK PRIMARY KEY (MemberID),
 CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE,
 CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE
);