This discussion is archived
4 Replies Latest reply: Feb 27, 2012 5:36 PM by 920287 RSS

Foreign Key Invalid Identifier Error

920287 Newbie
Currently Being Moderated
Hi Folks,

I am new to the Oracle environment, and am trying to create the below tables for a university project. I checked with the professor, and he is okay with me consulting the Oracle forum on issues that I am having.

So far, PATIENT_IDENTIFICATION, LAB_INFORMATION, EXPERIMENTAL_DRUG_TRIAL_DATA, and STUDY_INFORMATION have been made without issues. For the other three tables, (VITAL_SIGNS, AE_SAE, and STAFF_TIME_CARD), I need to reference foreign keys from the aforementioned created tables, but I keep getting the error "ERROR at line 1:
ORA-00904: : invalid identifier" issue when trying to run the CREATE statement. I know that the syntax for the STAFF_TIME_CARD fk is wrong - I only worked on the ones for VITAL_SIGNS and AE_SAE so far, but any help would be appreciated.

Sorry in case this is in the wrong forum or area - any advice would be appreciated!


Thanks!


1 CREATE TABLE PATIENT_IDENTIFICATION (patientID CHAR(5) PRIMARY KEY, race NVARCHAR2(10), height CHAR(2), weight CHAR(3), ethnicity NVARCHAR2(10), dateOfBirth DATE, childbearingStatus NVARCHAR2(2), gender NVARCHAR2(1), dateOfInformedConsent DATE, affectedSystem NVARCHAR2(15), startDate DATE, endDate DATE, issueStatus NVARCHAR2(15), diagnosis NVARCHAR2(20));
2
3 Table created.
4
5
6 CREATE TABLE LAB_INFORMATION (labID CHAR(5) PRIMARY KEY, testStatus NVARCHAR2(10), labValues CHAR(10), collectionData NVARCHAR2(15), labAddress NVARCHAR2(50), studyLocations NVARCHAR2(50));
7
8 Table created.
9
10
11 CREATE TABLE VITAL_SIGNS (uid INTEGER PRIMARY KEY, FOREIGN KEY (patientID) REFERENCES PATIENT_IDENTIFICATION(patientID), temperature CHAR(3), respirations CHAR(2), pulse CHAR(3), bloodPressure NVARCHAR2(7));
12
13
14
15
16 CREATE TABLE AE_SAE (uid INTEGER PRIMARY KEY, patientID CHAR(5) REFERENCES PATIENT_IDENTIFICATION(patientID), sideEffects NVARCHAR2(50), timeOfOnset TIMESTAMP, natureOfEvent NVARCHAR2(50), severity NVARCHAR2(50), finalOutcome NVARCHAR2(50));
17
18
19
20
21 CREATE TABLE EXPERIMENTAL_DRUG_TRIAL_DATA (drugID CHAR(5) PRIMARY KEY, baselineSignsAndSymptoms NVARCHAR2(50), dosage NVARCHAR2(50), dateOfDosage TIMESTAMP, postAdministrationSymptoms NVARCHAR2(50));
22
23 Table created.
24
25
26 CREATE TABLE STUDY_INFORMATION (coordinator NVARCHAR2(25) PRIMARY KEY, sponsor NVARCHAR2(25), studySize CHAR(5), studyDeadline DATE, studyDescription NVARCHAR2(50), studyInformation TIMESTAMP, studyCompensation CHAR(4));
27
28 Table created.
29
30
31 CREATE TABLE STAFF_TIME_CARD (employeeID CHAR(5) PRIMARY KEY, sponsor NVARCHAR2(25) FOREIGN KEY, salary CHAR(6), loginTime TIMESTAMP, logoutTime TIMESTAMP, fName NVARCHAR2(20), lName NVARCHAR2(20), address NVARCHAR2(50), department NVARCHAR2(15));
  • 1. Re: Foreign Key Invalid Identifier Error
    clcarter Expert
    Currently Being Moderated
    I know that the syntax for the STAFF_TIME_CARD fk is wrong -
    Yes, its an incomplete constraint spec, the referenced table(column(s)) must be identified.

    An FK constraint must reference column(s) that are a PK or have a UNIQUE constraint, the table containing your sponsor PK values has to be created first.

    And the {forum:id=75} forum is probably a better place for DML questions, with code tags for clarity, clean up the extra not relevant content (i.e. line numbers, "Table Created" ...), and your oracle version will also be helpful in getting faster answers.
  • 2. Re: Foreign Key Invalid Identifier Error
    920287 Newbie
    Currently Being Moderated
    But then how would I fix something like this:

    CREATE TABLE VITAL_SIGNS (uid INTEGER PRIMARY KEY, FOREIGN KEY (patientID) REFERENCES PATIENT_IDENTIFICATION(patientID), temperature CHAR(3), respirations CHAR(2), pulse CHAR(3), bloodPressure NVARCHAR2(7));

    I thought that saying "REFERENCES PATIENT_IDENTIFICATION(patientID)" accomplishes exactly the table(column) structure you mentioned, right?


    And thanks for the advice - I'll be sure to do that going forward.
  • 3. Re: Foreign Key Invalid Identifier Error
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!
    917284 wrote:
    ... So far, PATIENT_IDENTIFICATION, LAB_INFORMATION, EXPERIMENTAL_DRUG_TRIAL_DATA, and STUDY_INFORMATION have been made without issues. For the other three tables, (VITAL_SIGNS, AE_SAE, and STAFF_TIME_CARD), I need to reference foreign keys from the aforementioned created tables, but I keep getting the error "ERROR at line 1:
    ORA-00904: : invalid identifier" issue when trying to run the CREATE statement. I know that the syntax for the STAFF_TIME_CARD fk is wrong - I only worked on the ones for VITAL_SIGNS and AE_SAE so far, but any help would be appreciated.

    Sorry in case this is in the wrong forum or area - any advice would be appreciated!
    As DCarter said, this forum is for the SQL*Plus and iSQL*Plus front-ends. This is actually an issue with the SQL language, and so it would be better, in the future, to post questions like this in the SQL and PL/SQL forum:
    SQL and PL/SQL
    Many more people read that forum, too, so you'll probably get quicker replies.
    ... 11 CREATE TABLE VITAL_SIGNS (uid INTEGER PRIMARY KEY, FOREIGN KEY (patientID) REFERENCES PATIENT_IDENTIFICATION(patientID), temperature CHAR(3), respirations CHAR(2), pulse CHAR(3), bloodPressure NVARCHAR2(7));
    There are 2 different ways to specify constraints:
    (1) Table Constraints (or Out-of-Line Constraints ), which are delimited by a comma from other items in hte CREATE TABLE statement
    (2) Column Constraints (or In-Line Constraints ), which are placed at the end of a column definition, not separated by a comma.

    What you posted was the correct form for a table constraint, but when you use a table constraint, you must have a separate column declaration for each column referenced. You never declared a column in the vital_signs table called patiendID.
    Here's one correct way to use a table constraint:
    CREATE TABLE     vital_signs 
    (       u_id              INTEGER         PRIMARY KEY
    ,     patientID         CHAR (5)                              -- Column declared here
    ,     temperature          CHAR (3)
    ,     respirations         CHAR (2)
    ,     pulse              CHAR (3)
    ,     bloodPressure         NVARCHAR2 (7)
    ,     FOREIGN KEY (patientID) REFERENCES PATIENT_IDENTIFICATION (patientID)     -- Column referenced here
    );
    Most people put table constraints after all the columns have been declared, as I did above, but that's not required.

    Here's one way to do get the same effect using a column constraint:
    CREATE TABLE     vital_signs 
    (       u_id              INTEGER         PRIMARY KEY
    ,     patientID         CHAR (5)         REFERENCES PATIENT_IDENTIFICATION (patientID)
    ,     temperature          CHAR (3)
    ,     respirations         CHAR (2)
    ,     pulse              CHAR (3)
    ,     bloodPressure         NVARCHAR2 (7)
    );
    Personally, I prefer the column constraint way. I find it easier to read and understand, becuase it's easier to notice the constraint when you see the column definition in the code. I also find it useful to give meaningful names to foreign key constraints, like this:
    CREATE TABLE     vital_signs 
    (       u_id              INTEGER         PRIMARY KEY
    ,     patientID         CHAR (5)         CONSTRAINT     vs_patientID_fk
                                      REFERENCES PATIENT_IDENTIFICATION (patientID)
    ,     temperature          CHAR (3)
    ,     respirations         CHAR (2)
    ,     pulse              CHAR (3)
    ,     bloodPressure         NVARCHAR2 (7)
    );
    If you violate the constraint, the error message will say "VS_PATIENTID_FK violated", which helps identify exactly what was wrong.

    For more, look up "Constraints" in hte SQL language manual:
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/clauses002.htm#g1053592

     

    By the way, the CHAR datatype is very confusing, and there's no good reason not to use VARCHAR2 instead.
  • 4. Re: Foreign Key Invalid Identifier Error
    920287 Newbie
    Currently Being Moderated
    Wow - thank you very much for the explanation. I'm really thankful for your help (and also to the prior poster), and this makes a lot more sense to me now!

    I'll be sure to work with the correct forum going forward!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points