在 Oracle 中添加约束时数据类型无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9963639/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Invalid datatype when adding constraint in Oracle
提问by Peter Penzov
I want to create this table in Oracle. This is only the table SQL script.
我想在 Oracle 中创建这个表。这只是表 SQL 脚本。
-- CREATE TABLES SECTION -------------------------------------------------
-- TABLE DATACENTER
CREATE TABLE DATACENTER(
DATACENTERID INTEGER NOT NULL,
NAME VARCHAR2(80 ) NOT NULL,
LOCATION VARCHAR2(200 ),
DCALLOWEDWEIGHTKG NUMBER(9,0),
DCMAXIMUMWEIGHTKG NUMBER(9,0),
DCALLOWEDPOWERWATT NUMBER(9,0),
DCMAXPOWERWATT NUMBER(9,0),
DCALLOWCOOLINGPOWERBTU NUMBER(9,0),
DCMAXCOOLINGPOWERBTU NUMBER(9,0),
DESCRIPTION CLOB
)
/
-- ADD KEYS FOR TABLE DATACENTER
ALTER TABLE DATACENTER ADD CONSTRAINT DATACENTERID PRIMARY KEY (DATACENTERID)
/
-- TABLE COMPONENT
CREATE TABLE COMPONENT(
COMPONENTID INTEGER NOT NULL,
DATACENTERID INTEGER,
FKCOMPONENTID INTEGER,
COMPONENTSTATSID INTEGER NOT NULL
)
/
-- ADD KEYS FOR TABLE COMPONENT
ALTER TABLE COMPONENT ADD CONSTRAINT COMPONENTID PRIMARY KEY (COMPONENTID)
/
-- CREATE RELATIONSHIPS SECTION -------------------------------------------------
ALTER TABLE COMPONENT ADD CONSTRAINT IS PART OF A FOREIGN KEY (DATACENTERID) REFERENCES DATACENTER (DATACENTERID)
/
ALTER TABLE COMPONENT ADD CONSTRAINT IS A SUBPART OF FOREIGN KEY (FKCOMPONENTID) REFERENCES COMPONENT (COMPONENTID)
/
The error stack:
错误堆栈：
table DATACENTER created.
table DATACENTER altered.
table COMPONENT created.
table COMPONENT altered.
Error starting at line 39 in command:
ALTER TABLE COMPONENT ADD CONSTRAINT IS PART OF A FOREIGN KEY (DATACENTERID) REFERENCES DATACENTER (DATACENTERID)
Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:
Error starting at line 42 in command:
ALTER TABLE COMPONENT ADD CONSTRAINT IS A SUBPART OF FOREIGN KEY (FKCOMPONENTID) REFERENCES COMPONENT (COMPONENTID)
Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:
EDITI edited the code because I saw that some SQL statements are missing. I can provide the schema if you want?
编辑我编辑了代码，因为我看到缺少一些 SQL 语句。如果需要，我可以提供架构吗？
Best wishes
最好的祝愿
回答by a_horse_with_no_name
Your constraint name IS PART OF Ais an illegal identifier (because of the spaces).
您的约束名称IS PART OF A是非法标识符（因为有空格）。
You need to remove the spaces, e.g. IS_PART_OF_A:
您需要删除空格，例如IS_PART_OF_A：
ALTER TABLE COMPONENT
ADD CONSTRAINT IS_PART_OF_A
FOREIGN KEY (DATACENTERID)
REFERENCES DATACENTER (DATACENTERID)
Edit
I also realized that your script is not correctly terminating the individual statements. There is a ;(or /) missing after the first CREATE TABLE. And the first ALTER TABLEis not properly terminated as well. I don't know if that is a copy & paste error though.
编辑
我还意识到您的脚本没有正确终止各个语句。在第一个 CREATE TABLE 之后缺少一个;（或/）。第一个ALTER TABLE也没有正确终止。我不知道这是否是复制和粘贴错误。
Edit2:
编辑2：
Here is the complete script with correct names, statement termination and in the correct order:
这是具有正确名称、语句终止和正确顺序的完整脚本：
CREATE TABLE COMPONENT(
COMPONENTID INTEGER NOT NULL,
DATACENTERID INTEGER,
FKCOMPONENTID INTEGER,
COMPONENTSTATSID INTEGER NOT NULL
)
/
CREATE TABLE DATACENTER(
DATACENTERID INTEGER NOT NULL,
NAME VARCHAR2(80 ) NOT NULL,
LOCATION VARCHAR2(200 ),
DCALLOWEDWEIGHTKG NUMBER(9,0),
DCMAXIMUMWEIGHTKG NUMBER(9,0),
DCALLOWEDPOWERWATT NUMBER(9,0),
DCMAXPOWERWATT NUMBER(9,0),
DCALLOWCOOLINGPOWERBTU NUMBER(9,0),
DCMAXCOOLINGPOWERBTU NUMBER(9,0),
DESCRIPTION CLOB
)
/
ALTER TABLE DATACENTER ADD CONSTRAINT DATACENTERID PRIMARY KEY (DATACENTERID)
/
ALTER TABLE COMPONENT ADD CONSTRAINT COMPONENTID PRIMARY KEY (COMPONENTID)
/
ALTER TABLE COMPONENT ADD CONSTRAINT IS_PART_OF_A FOREIGN KEY (DATACENTERID) REFERENCES DATACENTER (DATACENTERID)
/
