0

The error occurs here:

CREATE TABLE CERDIT_CARD_LOG
(
    CERDIT_CARD_NUMBER     CHAR(20) NOT NULL,
    CERDIT_CARD_LOG_NUMBER CHAR(15) NOT NULL,
    CERDIT_CARD_NUM        CHAR(20) NOT NULL,
    DATE                   DATE     NOT NULL,
    OPERATION_TYPE         CHAR(20) NOT NULL,

    CONSTRAINT PK_CERDIT_CARD_LOG 
        PRIMARY KEY (CERDIT_CARD_LOG_NUMBER, CERDIT_CARD_NUM)
);

ALTER TABLE CERDIT_CARD_LOG
    ADD CONSTRAINT FK_CERDIT_C_RECORD_CERDIT_C 
            FOREIGN KEY (CERDIT_CARD_NUMBER)
                    REFERENCES CERDIT_CARD (CERDIT_CARD_NUMBER);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
iMaiclove
  • 1
  • 1
    A foreign key must reference a whole key. In this case two columns, not just one. – jarlh Nov 26 '21 at 14:27
  • 2
    It's a **credit card** - not a "cerdit card" .... – marc_s Nov 26 '21 at 14:28
  • 3
    `DATE` is a reserved word, https://en.wikipedia.org/wiki/SQL_reserved_words. Either chose something else, or delimit as `"DATE"`. – jarlh Nov 26 '21 at 14:29
  • Just change it to `CREDIT_CARD_DATE`. Also search and replace "CERDIT" with "CREDIT". – RBarryYoung Nov 26 '21 at 14:32
  • 1
    As mentioned, you should call this date different. This will even clarify what the date is about. Is it the card's `EXPIRATION_DATE` maybe? And, on a side note, you should never use `CHAR`. This data type is a nuisance to work with in queries and has no advantage over `VARCHAR2` in any regard. – Thorsten Kettner Nov 26 '21 at 14:37
  • @jarlh: The foreign key references the table CREDIT_CARD, the structure of which we don't see here. I assume that its complete primary key is indeed the single CREDIT_CARD_NUMBER. – Thorsten Kettner Nov 26 '21 at 14:55
  • @ThorstenKettner, yes, now I see... my bad, – jarlh Nov 26 '21 at 17:36
  • Also by the way, `char` is not a useful type in Oracle and is best avoided. – William Robertson Nov 26 '21 at 22:54

1 Answers1

0

If you want to create a column with the name DATE in Oracle (reserved word) you need to treat it as a special identifier, and enclose it in double quotes.

You can do:

create table CERDIT_CARD_LOG
(
    CERDIT_CARD_NUMBER      CHAR(20)               not null,
    CERDIT_CARD_LOG_NUMBER  CHAR(15)               not null,
    CERDIT_CARD_NUM         CHAR(20)               not null,
    "DATE"                    DATE                   not null,
    OPERATION_TYPE          CHAR(20)               not null,
  constraint PK_CERDIT_CARD_LOG primary key (CERDIT_CARD_LOG_NUMBER,CERDIT_CARD_NUM  )
);

See example at db<>fiddle.

Note: As you see it's possible to use reserved words. I personally avoid it since I would need to remember to enclose them in double quotes in every single query I write in the future. I typically prefer change its name to opened_on or similar.

The Impaler
  • 45,731
  • 9
  • 39
  • 76