• 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

    • Not having a native app I guess is not good. That said when I have to use Windows and I want to use AI I do not use co-pilot. Lots of AI apps work on lots of OS’es. Just install the AI app you want on iOS or MacOs. Also we are in early days. In 3 years time suspect lots of these AI apps will be gone either by not making it or being bought up by others.
    • Gemini can now catch you up on the latest changes to your files in Google Drive by David Uzondu Google recently announced a new feature for Drive designed to help you keep track of what has changed in your files without having to dig through them manually. The feature, called "Catch me up", shares its name with a similar tool in Google Chat, which helps users quickly catch up on conversations without scrolling through long message threads. With the new feature, Gemini will now attempt to identify files in your Drive that have been altered since you last opened them, offering a summary of these changes. This aims to save you the headache of figuring out what is new or crucial. This covers edits made to Docs and also new comments left on Docs, Sheets, and Slides. Google clarifies that this summary is not exhaustive but is meant to point out potentially important updates. To use it, you can find a "Catch me up" prompt on the Google Drive Home page, and clicking it will bring up the summary in the Gemini side panel. Alternatively, for individual files, you will see an activity indicator on documents that have likely changed. You can click this indicator directly or select the file and hit a "catch me up" button in the selection toolbar to get the lowdown. The feature is currently available in English and has begun rolling out to users on both Rapid Release and Scheduled Release domains. It could take up to 15 days for everyone eligible to see it. It is coming to Google Workspace accounts, including Business Standard and Plus, Enterprise Standard and Plus, as well as customers with a Gemini Education or Gemini Education Premium add-on, and Google One AI Premium subscribers. Users who previously bought the Gemini Business or Gemini Enterprise add-ons before those were discontinued for new sales on January 15, 2025, will also get access.
    • Hehehe.. So, I realise my post came rather late, but did you actually try building one yet? 
    • Oh shoot. Could this be because of the OpenAI sale?
    • Not gonna lie, turned me on
  • Recent Achievements

    • Week One Done
      mywakehealth earned a badge
      Week One Done
    • Dedicated
      jbatch earned a badge
      Dedicated
    • Week One Done
      Leonard grant earned a badge
      Week One Done
    • One Month Later
      portacnb1 earned a badge
      One Month Later
    • Week One Done
      portacnb1 earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      284
    2. 2
      snowy owl
      158
    3. 3
      +FloatingFatMan
      149
    4. 4
      ATLien_0
      140
    5. 5
      Xenon
      130
  • Tell a friend

    Love Neowin? Tell a friend!