ORA-00905: missing keyword (copied from textbook)

Question

Have tried looking through the code up-and-down and cannot get it to work. Was mostly trying out the Create table function. Worked fine until trying to create a table with composite primary key. Absolutely stumped. Any help is appreciated.

Tried removing the names for the keys, but then was redirected to a different sort of error: "invalid datatype"

Create Table Cust_Artist_EOI    (ArtistID   Number(38)  Not Null,
                                 CustomerID  Number(38)  Not Null,
                                 Constraint  Cust_Artist_EOI_PK Primary Key(ArtistID, CustomerID),
                                 Constraint  Cust_Artist_EOI_ArtistFK Foreign Key(ArtistID)
                                            References  MyArtist(ArtistID)
                                                On Update No Action
                                                On Delete Cascade,
                                 Constraint  Cust_Artist_EOI_CustFK  Foreign Key(CustomerID)
                                            References MyCustomer(CustomerID)
                                                On Update No Action
                                                On Delete Cascade);

Answer 1

The problem is the on update. Remove that:

Create Table Cust_Artist_EOI  (
    ArtistID   Number(38)  Not Null,
    CustomerID  Number(38)  Not Null,
    Constraint  Cust_Artist_EOI_PK Primary Key(ArtistID, CustomerID),
    Constraint  Cust_Artist_EOI_ArtistFK Foreign Key(ArtistID)
        References  MyArtist(ArtistID) On Delete Cascade,
    Constraint  Cust_Artist_EOI_CustFK  Foreign Key(CustomerID)
        References MyCustomer(CustomerID) On Delete Cascade
 );

Here is a db<>fiddle.

If you look in the syntax diagram for foreign key constraint in Oracle, you will see that neither on update nor no action is supported.

I'm not sure why no action is not supported, because that is the default behavior (and I think it is a good idea to be able to express default behavior).

Not supporting on update is a better idea. It discourages changes to primary keys. And changing primary keys is generally a bad idea.