• 0

SQL Invalid Identifier Error


Question

/* Drop tables, sequence, and other objects you create*/
drop table physician;
drop table patient;
drop table appointment;
drop table procedure;
drop table payment;
drop sequence seq_orders;

/* Create 5 tables */
CREATE TABLE Physician
(
DoctorID numeric (10),
FirstName varchar2 (10) not null,
LastName varchar2 (20) not null,
PhoneNumber numeric (12) not null,
Specialty varchar2 (20) not null,
Constaint pk_doctorid primary key (doctorid)
);

CREATE TABLE Patient
(
PatientID numeric (10),
FirstName varchar2 (10),
LastName varchar2 (20),
DOB date,
Address varchar2 (20),
PhoneNumber numeric (12),
Constraint pk_patientid primary key (patientid))
);

CREATE TABLE Appointment
(
ApptID numeric (10),
Date date,
Location varchar2 (10),
Reason varchar2 (20),
Time time,
CONSTRAINT pk_apptid PRIMARY KEY (apptid),
CONSTRAINT fk_doctorid FOREIGN KEY (doctorid) REFERENCES physician (doctorid)
);

CREATE Table Procedure
(
ProcedureID numeric (10),
Type varchar2 (20),
Date date,
Time time,
Site varchar2 (20),
RoomNo varchar2 (10),
Constraint pk_procedureid primary key(procedureid),
Constraint fk_doctorid foreign key (doctorid) references physician (doctorid)
);

CREATE TABLE Payment
(
PaymentID numeric (10)
Method varchar2 (20),
Details varchar2 (30),
InsuranceID numeric (10),
BillNo numeric (10),
Constraint pk_paymentid primary key (paymentid),
Constraint fk_patientid foreign key (patientid) references patient (patientid)
);

create sequence seq_order
start with 1
increament by 1);

insert into physician
            (PhysicianID, FirstName, LastName, PhoneNumber, Specialty)
values
            (1, ‘John’, ‘Smith’, ‘12345678’, ‘Medicine’);

insert into patient
            (PatientID, FirstName, LastName, DOB, Address,PhoneNumber)
values
            (14, ‘John’, ‘Doe’, ‘1/2/80’, ‘Fairfax, VA’,
            ‘5716557895’);

insert into appointment
            (ApptID, Date, Time, Reason, Location)
values
            (seq_order.nextval, to date (‘08-July- 2016’, ‘dd-Mon- yyy’), ‘08:00’, ‘CAB’,
            ‘MGUH’);

insert into procedure
            (ProcedureID, Type, Date, Time, Site, RoomNo)
values
            (‘30’, ‘AVR’, (‘08-July- 2016’, ‘dd-Mon- yyy’), ’08:00’,
            ‘Cardio”, ‘OR2’);

insert into payment
            (PaymentID, Method, Details, InsuranceID, BillNo)
values
            (‘1234’, ‘insurance’, ‘insurance’, ‘45678’, ‘12’);

Friend is receiving this error and I haven't used SQL for about 6 or 7 years ago in college and I cannot find the issue:

 

Ecxlc4N.jpg

 

Any help would be greatly appreciated.

Link to comment
https://www.neowin.net/forum/topic/1302574-sql-invalid-identifier-error/
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Following case identify the solution - http://stackoverflow.com/questions/6027961/ora-00904-invalid-identifier

 

1. Created table name in UPPERCASE or "IN QUOTES"

2. Create Mixed Case then without "" ; otherwise "" will be considered as table name. (Your case)

 

Please check & confirm whether it fix your case or not.

  • 0

From what I can see, he is trying to create a foreign key on a field in the current table that doesn't exist in that table and referencing a real field in another table.

 

For instance, 

 

CREATE Table Procedure
(
ProcedureID numeric (10),
Type varchar2 (20),
Date date,
Time time,
Site varchar2 (20),
RoomNo varchar2 (10),
Constraint pk_procedureid primary key(procedureid),
Constraint fk_doctorid foreign key (doctorid) references physician (doctorid)
);

 

This is trying to create an FK between doctor id in the Procedure table and  PHYSICIAN.doctorid BUT Procedure doesn't have a doctorid field . To make this work it would need to be something like

 

CREATE Table Procedure
(
ProcedureID numeric (10),
DoctorID numeric(10),
Type varchar2 (20),
Date date,
Time time,
Site varchar2 (20),
RoomNo varchar2 (10),
Constraint pk_procedureid primary key(procedureid),
Constraint fk_doctorid foreign key (DoctorID) references Physician(DoctorID)
);

 

 

  • 0

Thank you all so much. Creating the foreign key in the Procedure table and fixing the misspelled word seemed to fix his issues. Also, sorry it took me this long to respond. I already attempted to at work over a week ago but apparently it did not post. Thanks again. He greatly appreciates it.

This topic is now closed to further replies.
  • Posts

    • Tech demos are a declaration of desire, an horizon for what they pretend to put on those boxes call consoles (i still laugh at the poor implementation of Hairworks in W3 when you compare it with the demos)...being made on UE5 doesn't give me any confidence considering how poorly it runs in those things, and CDPR is not a marvel in that aspect neither. And about the game, so far is non existent besides the cinematic trailer. If from the get go they have to justify the whys for the protagonist, you start your narrative with the left foot.
    • Seen a 5090 for just over $3,000 at Best Buy just a couple days ago. It wasn't the sticker price that made me laugh... it's knowing that, for now... that kind of investment that includes drivers that don't work, is a headache no one should have to shell out for. But.. it's not the first time Nvidia has choked on crappy drivers, won't be the last.. so, if you spent a pretty penny on the next gen, hang in there... sure they'll get it right some day here soon.
    • Because they and their 801 partners are hungry for your data, and the web is the best facilitator of everything privacy-violating in this day and age... They want all your non-microsoft account data going to microsoft's servers:
    • Sorry, I found your question odd, that's all 😅.
    • "LOL The IoT version is for embedded systems" LOL. The IoT version is for whatever you want, why are you so stuck up? It's just A NAME. Open your mind, buddy 😅. It's not a different version of Windows, only the licensing model and the support dates change, nothing else. "there no real reason to do that" Everyone has their reasons. You love to use Linux. Why? You have YOUR reasons. Other people will have theirs. I use Windows 11 and Linux on the side. Why? I have my reasons. "No security updates? Who cares!" Many people do, just not you. Why not use a supported OS instead of an unsupported one if you can? LOL. I find it odd that a person that loves Linux and choice/freedom so much has such a hard time understanding why people do things different than him.
  • Recent Achievements

    • First Post
      m10d earned a badge
      First Post
    • Conversation Starter
      DarkShrunken earned a badge
      Conversation Starter
    • One Month Later
      jrromero17 earned a badge
      One Month Later
    • Week One Done
      jrromero17 earned a badge
      Week One Done
    • Conversation Starter
      johnwin1 earned a badge
      Conversation Starter
  • Popular Contributors

    1. 1
      +primortal
      251
    2. 2
      snowy owl
      157
    3. 3
      +FloatingFatMan
      140
    4. 4
      ATLien_0
      140
    5. 5
      Xenon
      128
  • Tell a friend

    Love Neowin? Tell a friend!