• 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

    • Been using Linux for the last 15-16 years, and I don't agree to any of this.. Come on, guys..
    • Crucial 8TB X10 2100 MB/s USB-C (3.2) external SSD is priced lowest ever at just $440 by Sayan Sen External SSDs are great as their portability makes them quite convenient. If you are shopping for one, then Micron has a deal that may just be right for you. The company's Crucial X10 external SSDs are currently on sale for their lowest ever prices (purchase links down below). What is interesting about the Pro X9 and X10 models is that they are based on TLC or triple-level cell NAND compared to QLC or quad-level on the X6 and X8 external SSDs, making them higher endurance and better for longer sustained reads and writes. Unfortunately, Crucial does not specifically state the kind of NAND flash the X10 has, though, from its three-year warranty, as opposed to five years on the Pro models, it can be said that the drive is QLC. As such it is not the ideal drive for constant data reads and writes, but you can certainly use it fairly often. Speaking of reads and writes, Crucial promises up to 2100 MB/s sequential reads. To enable such speeds, the SSD supports USB 3.2 Gen-2 2x2 interface and comes with a Type-C to Type-C USB cable in the box. But it is also backward compatible with USB 3.2 Gen 2/USB 3.2 Gen1/USB 3.1 Gen1/ USB 3.0 (5Gb/s), so this would mean lower speeds. In terms of durability, the X10 Pro and X9 Pro pack IP65 water and dust resistance, and the firm claims that the drive's drop-proof durability is up to 9.8 feet or about 3 meters. Get the Crucial X10 at the link below: Crucial X10 8TB Portable SSD, Up to 2,100MB/s, USB 3.2 USB-C, Compatible with Windows, Mac & Android, Blue - CT8000X10SSD9-02: $439.99 (Amazon US first-party seller) This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
  • Recent Achievements

    • Conversation Starter
      johnwin1 earned a badge
      Conversation Starter
    • One Month Later
      Marwin earned a badge
      One Month Later
    • One Year In
      fred8615 earned a badge
      One Year In
    • Week One Done
      Jim Dugan earned a badge
      Week One Done
    • Week One Done
      Adam Todd earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      215
    2. 2
      snowy owl
      156
    3. 3
      ATLien_0
      134
    4. 4
      Xenon
      127
    5. 5
      +FloatingFatMan
      117
  • Tell a friend

    Love Neowin? Tell a friend!