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

Question

创建此表后，我正在尝试添加外键

Create table Member
(
memeberID int Not null Primary Key,
mebershipID int Not null,
group_id  int not NULL,
Dutycode int not null,
MemberRole varchar(255),
name varchar(255),
last_joined date,
DOB date,
address varchar(255),

CONSTRAINT fk_DutCodes FOREIGN KEY (Dutycode)
REFERENCES RaceManagementDuty(Dutycode),


CONSTRAINT fk_GrMemeberinMemeber FOREIGN KEY (group_id)
REFERENCES Group_member(group_id),

CONSTRAINT fk_Mebersids FOREIGN KEY (mebershipID)
REFERENCES Membership(mebershipID)
)

我想使用Alter table创建的外键是Sailnumber：

ALTER表成员添加外键（sailnumber）引用船（sailnumber）

sailnumber是table boat的主键，如下所示

Create table Boat
(
sailnumber  int not null Primary Key,
mebershipID  int not NULL,
group_id int not NULL,
sizeOfBoat decimal(5,2),
weight decimal(5,2),
sailarea integer,
numberOfCrew integer,
portsmouthNumber integer,

CONSTRAINT fk_MebersidsBoat FOREIGN KEY (mebershipID)
REFERENCES Membership(mebershipID),


CONSTRAINT fk_GrMemeberinMemebers FOREIGN KEY (group_id)
REFERENCES Group_member(group_id)
)

然而，我得到了这个错误SQL错误： ORA-00904："SAILNUMBER"：无效的标识符，我不知道为什么我得到有人能告诉我哪里出错了吗？

Answer 1

您需要将Sailnumber列添加到您的成员表中，（在执行add外键查询之前）

ALTER TABLE Member ADD sailnumber INT NOT NULL;
ALTER TABLE Member ADD FOREIGN KEY (sailnumber) REFERENCES Boat(sailnumber);

或在一个查询中：

ALTER TABLE member 
ADD sailnumber INT NOT NULL CONSTRAINT sailnumber REFERENCES boat(sailnumber);