CODEWITHSUNDEEP
sqloracle-database
Juan
Juan

Reputation: 1

How do I fix a ORA-00902: invalid datatype creating a table

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)
);

Upvotes: 0

Views: 2675

Answers (2)

Littlefoot
Littlefoot

Reputation: 143073

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.

SQL>

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).

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13247

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

Upvotes: 2

Related Questions