Oracle中的外键创建问题 [英] Foreign Key Creation issue in Oracle
问题描述
SQL错误:ORA-00904:COLLECTIBLENUM:无效的标识符
我确定这是一个noob错误,但我只是没有看到它。有人可以指出我做错了什么?
CREATE TABLE收藏品(
收藏品编号(10)NOT NULL,
CONSTRAINT collectibles_pk PRIMARY KEY(CollectibleNum));
创建表DiecastItems(
DiecastName VARCHAR2(45)NOT NULL,
DiecastCopy NUMBER(2)NOT NULL,
DiecastScale VARCHAR2(25),
ColorScheme VARCHAR2(25),
DiecastYear NUMBER(4),
CONSTRAINT diecastItem_pk PRIMARY KEY(DiecastName,DiecastCopy),
CONSTRAINT diecastItem_Collectible_fk FOREIGN KEY(CollectibleNum)REFERENCES Collectibles(CollectibleNum));
当您添加FK时, em>作为孩子从您正在创建的表,到父表父母。因此,您需要提供子列名称以及父列名称。
一般语法是
CREATE TABLE table_name
(
column1 datatype null / not null,
column2 datatype null / not null,
...
CONSTRAINT fk_column
FOREIGN KEY(column1,column2,... column_n)
REFERENCES parent_table(column1,column2,... column_n)
);
请注意, FOREIGN KEY
括号,来自您正在创建的表,而<
CollectibleNum
C>。因此,通过添加这样的列,以下工作正常:
CREATE TABLE collectibles
(
collectiblenum NUMBER (10)NOT NULL,
CONSTRAINT collectibles_pk PRIMARY KEY(collectiblenum)
);
CREATE TABLE diecastitems
(
diecastname VARCHAR2(45)NOT NULL,
diecastcopy NUMBER(2)NOT NULL,
diecastscale VARCHAR2(25),
colorscheme VARCHAR2(25),
diecastyear NUMBER(4),
collectiblenum NUMBER(10),--added column
CONSTRAINT diecastitem_pk PRIMARY KEY(diecastname,diecastcopy),
CONSTRAINT diecastitem_collectible_fk FOREIGN KEY(collectiblenum)
REFERENCES collectibles(collectiblenum)
);
When I try to create these two tables I get:
"SQL Error: ORA-00904: "COLLECTIBLENUM": invalid identifier"
I'm sure it's a noob error but I'm just not seeing it. Can someone please point out what I'm doing wrong? Thanks in advance.
CREATE TABLE Collectibles(
CollectibleNum Number(10) NOT NULL,
CONSTRAINT collectibles_pk PRIMARY KEY(CollectibleNum));
Create table DiecastItems(
DiecastName VARCHAR2(45) NOT NULL,
DiecastCopy NUMBER(2) NOT NULL,
DiecastScale VARCHAR2(25),
ColorScheme VARCHAR2(25),
DiecastYear NUMBER(4),
CONSTRAINT diecastItem_pk PRIMARY KEY(DiecastName, DiecastCopy),
CONSTRAINT diecastItem_Collectible_fk FOREIGN KEY(CollectibleNum) REFERENCES Collectibles(CollectibleNum));
When you add FK, you are linking a column as a child from the table you are creating, to its parent from the parent table. Hence, you need to provide the child column name, as well as the parent column name.
The general syntax is
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
);
Notice that the columns between FOREIGN KEY
brackets, are from the table you are creating, while the columns betweeN REFERENCES PARENT_TABLE
are from the parent table.
You do not have a column called CollectibleNum
in yourDiecastItems
. Hence, the following works fine by adding such a column:
CREATE TABLE collectibles
(
collectiblenum NUMBER(10) NOT NULL,
CONSTRAINT collectibles_pk PRIMARY KEY(collectiblenum)
);
CREATE TABLE diecastitems
(
diecastname VARCHAR2(45) NOT NULL,
diecastcopy NUMBER(2) NOT NULL,
diecastscale VARCHAR2(25),
colorscheme VARCHAR2(25),
diecastyear NUMBER(4),
collectiblenum NUMBER(10), --added column
CONSTRAINT diecastitem_pk PRIMARY KEY(diecastname, diecastcopy),
CONSTRAINT diecastitem_collectible_fk FOREIGN KEY(collectiblenum)
REFERENCES collectibles(collectiblenum)
);
这篇关于Oracle中的外键创建问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!