• 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

    • Stable..... No, he isn't..
    • Of course the sales are bad. Who even asked for a thinner phone with way less battery? Lightness? It's still a giant brick, it's just a thinner giant brick. It makes no sense at all. Making folding phones thinner, now that does make sense. Because when folded, the thinner it is unfolded, the more usable and pocketable it is when folded. You already expect worse battery at expense of actually being more pocketable. Galaxy Flip, when folded is half the size of S Ultra models and about as thick. That does make a big difference when fitting it in a pocket. But the phone that's as big as Ultra, making it thinner, you don't really solve anything, it's still a giant slab that barely fits into a pocket. All the "Mini" phones made way more sense than this thin crap. Especially now that it's literally impossible to find a phone smaller than 6.5". My dad only needs phone for calls and SMS and he doesn't want to go with smartphone because they are all so massive. Especially cheaper ones. Like, he'd be fine with Galaxy A06 for all he cares in terms of hardware, but it only comes in giant 6.7" format. It's useless. Or is he suppose to find a 800€ old gen iPhone Mini or Zenfone? He doesn't even need those stupid specs and such stupid price. And then you see old people fumbling around with giant smartphones and they don't even need 3/4 of features on them.
    • its funny now instead of robots built for a specific task which is more efficient we are focused on humanoid robots that are built for do anything but might not do it as well because of restrictions similar to how human body works and they were molded after...
  • Recent Achievements

    • First Post
      emptyother earned a badge
      First Post
    • Week One Done
      Crunchy6 earned a badge
      Week One Done
    • One Month Later
      KynanSEIT earned a badge
      One Month Later
    • One Month Later
      gowtham07 earned a badge
      One Month Later
    • Collaborator
      lethalman went up a rank
      Collaborator
  • Popular Contributors

    1. 1
      +primortal
      664
    2. 2
      ATLien_0
      270
    3. 3
      Michael Scrip
      218
    4. 4
      Steven P.
      162
    5. 5
      +FloatingFatMan
      158
  • Tell a friend

    Love Neowin? Tell a friend!