• 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

    • I’m new to the Neowin forum and just wanted to introduce myself. I’ve always had an interest in tech — especially Windows, software updates, and gadgets. I’ve been reading posts here for a while, and it seemed like a good time to finally join in and be part of the conversation. Outside of tech, I enjoy music, movies, and keeping up with the latest news in the digital world. I’m hoping to learn from others, share a few tips of my own, and have some good discussions.
    • Hey! What a difference it makes to upgrade from an old WiFi 5 router to a new one. The Asus BE88U and BE92U are both very top picks. Asus gives you more control and better firmware support than most. Netgear’s new stuff is decent, but locked down. If you need more than 4 wired ports, I’d suggest a separate 2.5G or 10G switch. It makes life easier. Synology’s UI is clean too, but they don’t have a WiFi 7 router yet. If the price isn’t a big deal, go with Asus and pair it with a switch. Let us know what you think!  
    • Honestly, I think the long-term play here is for Microsoft to ditch the idea of a traditional console entirely and just turn Xbox into a full-on operating system. They (or anyone!) could release hardware like a Mac Mini or a typical console with built-in GPU and RAM, but instead of being locked into a console ecosystem, they run the Xbox OS. It makes total sense because it pushes Game Pass, Windows, and all their other software. The handheld angle is really interesting too. You’d basically have a portable PC. Hook up a keyboard and mouse, and suddenly you can edit videos or get some real work done while on the go. Something like a prebuilt Xbox PC would be more than just a gaming box. It could be a decent little PC that people might actually upgrade with each generation. And since it’s running a PC-based OS, you’d get all the usual perks like mod support, cheaper games across different stores, and no extra charges just to plug in a webcam or other standard accessories. Plus, if they let you install the OS on your own rig, then you’ve got full upgradability too. Best bit also being Microsoft wont even need to build them anyone can and when they do Microsoft just wins. The competition is kind of stuffed. Those maybe relying on SteamOS might be ok but will still have software support issues and no Game Pass. Sony and Nintendo can’t really offer the same kind of flexibility at all not sure how they will live on. They’d struggle to match something that works as a desktop OS, console, handheld, streaming box, and media hub, all with your game library ready to go, never needing to rebuy games. Console exclusives are the only way they can live I think, but if they ever get blocked by antitrust rulings down the line, it’s game over. I'm waiting to see the bloat that's still left on these and if they let you install on a regular old PC. Fingers crossed this could be the lightweight OS we have been asking for.
    • I take my Apple Watch off at night and put it on the charger when I go to bed. Then I wake up the next morning I put it back on.
  • Recent Achievements

    • Conversation Starter
      NeoToad777 earned a badge
      Conversation Starter
    • Week One Done
      VicByrd earned a badge
      Week One Done
    • Reacting Well
      NeoToad777 earned a badge
      Reacting Well
    • Reacting Well
      eric79XXL earned a badge
      Reacting Well
    • First Post
      brynmot earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      480
    2. 2
      +FloatingFatMan
      277
    3. 3
      ATLien_0
      243
    4. 4
      snowy owl
      209
    5. 5
      Edouard
      187
  • Tell a friend

    Love Neowin? Tell a friend!