oracle identifier too long

Question

New to oracle Im trying to assign composite key

CREATE TABLE rep_contracts
( Store_id NUMBER(8),
  Name NUMBER(5)
  Quarter CHAR(3),
  Rep_id NUMBER(5),
    CONSTRAINT rep_contracts_rep_idstore_id_pk PRIMARY KEY (rep_id, store_id),
    CONSTRAINT rep_contracts_rep_id_fk FOREIGN KEY (rep_id),
     CONSTRAINT rep_contracts_store_id_fk FOREIGN KEY (store_id) );

and im receiving

ORA-00972: identifier is too long

Im aware that composit key identifier longer than 30 char but if I cut identifier few character shorter ex:

rep_contrac_rep_idstore_id_pk

then I receive

ORA-00905: missing keyword

I couldn't find any workaround

You have omitted the REFERENCES clause of both the foreign keys. — Tony Andrews, Feb 24 '16 at 17:05
Er so the error in the question is not the error - you solved that, its got nothing to do with the length, the issue is *missing keyword* — Alex K., Feb 24 '16 at 17:05
@Alex yes but having the keyword too long "solves" the missing keyword issue! ;-) — Tony Andrews, Feb 24 '16 at 17:06

score 1 · Answer 1 · edited Feb 24 '16 at 17:22

1

I cleaned the script and incorporated the comments:

added a comma after number(5)
changed all names to uppercase to save pain later
changed NAME which is an Oracle reserved key work to S_NAME
changed the data type from CHAR to VARCHAR2
added the references keyword to point to the table where the foreign key is
renamed the constraint so it is not more than 30 characters

like so:

 CREATE TABLE rep_contracts
   ( STORE_ID NUMBER(8),
     S_NAME NUMBER(5),
     QUARTER VARCHAR2(3),
     REP_ID NUMBER(5),
     CONSTRAINT rep_cont_rep_idstore_id_pk PRIMARY KEY (REP_ID, STORE_ID),
     CONSTRAINT rep_contracts_rep_id_fk FOREIGN KEY REFERENCES your_table(REP_ID),
     CONSTRAINT rep_contracts_store_id_fk FOREIGN KEY REFERENCES your_table(STORE_ID) );

edited Feb 24 '16 at 17:22

Boneist

22,910
1
25
40

answered Feb 24 '16 at 17:11

kevinskio

4,431
1
22
36

2

`•changed all names to uppercase to save pain later` -- this is an irrelevant change. It only matters if you're using case sensitive identifiers (i.e. enclosed within double-quotes), which should be very much the exception when creating tables! – Boneist Feb 24 '16 at 17:24
@Boneist Irrelevant if you know about case sensitive identifiers. For someone new at Oracle uppercasing is a good habit. – kevinskio Feb 24 '16 at 17:26

oracle identifier too long

1 Answers1