sql SQL Invalid Identifier Error

[Yorak]

/* 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:

 

 

Any help would be greatly appreciated.

[Matthew S.]

Aren't Field names case sensitive? 

[+Zlip792 MVC]

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.

[Laser]

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) ); 

he also spelled constraint wrong on this table

[+Biscuits Brown MVC]

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)
);

 

 

[Yorak]

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.