SQL Error: ORA-00904: invalid identifier

Question

I'm trying to add a foreign key after creating this table

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

The foreign key I'd like to create is Sailnumber using Alter table:

ALTER TABLE Member ADD FOREIGN KEY (sailnumber) REFERENCES Boat(sailnumber)

The sailnumber is the primary key for table boat, as follows

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

However, I'm getting this error SQL Error: ORA-00904: "SAILNUMBER": invalid identifier, which I am not sure why I'm getting can anybody tell me where I'm going wrong?

Answer 1

You need to add the Sailnumber column to your Member table, (before you execute your add foreign key query)

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

Or in one query:

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