Oracle 中的外键创建问题

2021-12-06 00:00:00 sql foreign-keys oracle

当我尝试创建这两个表时，我得到:

When I try to create these two tables I get:

SQL 错误:ORA-00904:COLLECTIBLENUM":标识符无效"

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

推荐答案

当您添加 FK 时，您将作为子列从您正在创建的表中链接到它的父级 来自父表.因此，您需要提供子列名称以及父列名称.

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.

一般语法是

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 括号之间的列来自您正在创建的表，而 REFERENCES PARENT_TABLE 之间的列来自父表.

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.

您的DiecastItems 中没有名为CollectibleNum 的列.因此，通过添加这样的列，以下内容可以正常工作:

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

小提琴

相关文章