Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am creating a table in a command SQL sections into a script already populated I have created several tables already but in this one I get a message saying

ORA-00902: invalid datatype

CREATE TABLE Weapons 
    id NUMBER(4),
    name VARCHAR2(30),
    damage NUMBER(4),
    company_id VARCHAR2 (10),
    CONSTRAINT pk_Weapons PRIMARY_KEY(id),
    CONSTRAINT fk_Weapons_company 
        FOREIGN_KEY(company_id) REFERENCES Company(id),
    CONSTRAINT fk_Weapons_ammo 
        FOREIGN_KEY(ammo_id) REFERENCES Ammo(id)
In the CONSTRAINT, it should be FOREIGN KEY and not FOREIGN_KEY. Also it should be PRIMARY KEY, not PRIMARY_KEY. 
There is no underscore required as per syntax. So the query will be:
CREATE TABLE Weapons (
    id NUMBER(4),
    name VARCHAR2(30),
    damage NUMBER(4),
    company_id VARCHAR2(10),
 CONSTRAINT pk_Weapons PRIMARY KEY(id),
 CONSTRAINT fk_Weapons_company FOREIGN KEY(company_id) REFERENCES Company(id),
 CONSTRAINT fk_Weapons_ammo FOREIGN KEY(ammo_id) REFERENCES Ammo(id)
About Foreign Keys: https://www.techonthenet.com/oracle/foreign_keys/foreign_keys.php

About Primary Keys: https://www.techonthenet.com/oracle/primary_keys.php
                CREATE TABLE Weapons (     id NUMBER(4),     name VARCHAR2(30),     damage NUMBER(4),     company_id VARCHAR2(10),     ammo_id VARCHAR2(10),     CONSTRAINT pk_Weapons PRIMARY KEY(id),     CONSTRAINT fk_Weapons_company FOREIGN KEY(company_id) REFERENCES Company(id),     CONSTRAINT fk_Weapons_ammo FOREIGN KEY(ammo_id) REFERENCES ammo(id)     );  i have changed the code now but a new error have appear thank you for your answer it was very helpfull aswell as the links u have provided  the error that appeared now is ORA-02267: column type incompatible with referenced column type
– Juan
                May 3, 2019 at 20:04
                It may due to the company_id in Weapons datatype is not same as the Company(id) or ammo_id in Weapons datatype is not same as the Ammo(id). Please match both the column's datatype
– Arulkumar
                May 3, 2019 at 20:08
                the Create command used in Company Table that posees company id is the following CREATE TABLE Company ( id VARCHAR(3),  name CHAR(30), CONSTRAINT pk_Company PRIMARY KEY(id) ); could it be possible that is due to the lenght of the storage capability inside the id of company?
– Juan
                May 3, 2019 at 21:18
                @Juan Yes, due to the length of the columns the error occures. So if company_id VARCHAR2(10) in Weapons table, then in the Company table the id should be VARCHAR2(10) only
– Arulkumar
                May 4, 2019 at 12:40
Here's a working example. I've created the AMMO table (whose description you didn't post, so I used only the ID column so that the foreign key constraint wouldn't fail). Pay attention to comments I wrote within the code.
SQL> create table ammo
  2    ( id  VARCHAR2(10),
  3      CONSTRAINT pk_ammo PRIMARY KEY(id) );
Table created.
SQL> CREATE TABLE Company
  2    ( id   VARCHAR(3),
  3      name VARCHAR(30),                   --> switch from CHAR to VARCHAR2
  4      CONSTRAINT pk_Company PRIMARY KEY(id) );
Table created.
SQL> CREATE TABLE Weapons
  2    ( id         NUMBER(4),
  3      name       VARCHAR2(30),
  4      damage     NUMBER(4),
  5      company_id VARCHAR2(3),             --> should match COMPANY.ID datatype
  6      ammo_id    VARCHAR2(10),            --> should match AMMO.ID datatype
  7      CONSTRAINT pk_Weapons PRIMARY KEY(id),
  8      CONSTRAINT fk_Weapons_company FOREIGN KEY(company_id) REFERENCES Company(id),
  9      CONSTRAINT fk_Weapons_ammo FOREIGN KEY(ammo_id) REFERENCES ammo(id) );
Table created.
In referential integrity constraint, you should match datatypes of the foreign and primary key columns. There's no sense in having a VARCHAR2(10) in the detail table which points to a VARCHAR2(3) column in the master table; you won't be able to put anything longer than 3 characters into the detail table's column anyway (foreign key constraint won't let you).
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.