Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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
–
–
–
–
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.