Oracle / SQL Error Invalid Identifier

Question

I'm having a problem with a FOREIGN KEY. I'm always getting

FOREIGN KEY (STUDID)
            *

ERROR at line 3:
ORA-00904: "STUDID": invalid identifier

The behavior you are observing is expected. In order to add a foreign key constraint, the column(s) being referenced in the constraint must exist. If you add the column, e.g. ALTER TABLE SUBJECT ADD STUDID NUMBER(10) COMMENT 'fk ref student.studid', then the statement that adds the foreign key constraint will succeed. That fixes the error, but see the answer from Ditto: this looks way more like a many-to-many relationship, i.e. a subject should be allowed to be related to more than one student. But if you want a student related to at most one subject, then add SUBJECTID column to STUDENT — spencer7593
– spencer7593, Commented Mar 30, 2015 at 19:14

Carson Moore · Accepted Answer · 2015-03-30 18:52:44Z

1

You have no field called STUDID in your SUBJECT table. Looks like you're trying to create a foreign key on a field that doesn't exist.

answered Mar 30, 2015 at 18:52

Carson Moore

1,2871 gold badge8 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ditto · Accepted Answer · 2015-03-30 18:55:48Z

1

Carson is correct - it seems like you are trying to build a relationship between Students and Subjects. Typically this is a many to many relationship, and would likely require a third, middle table to link them.

Think

"A Student can be enrolled to many subjects."

"A Subject can have many Students enrolled."

So you need a middle table with both STUDID and SUBJECTID columns, and that table would have 2 FKs declared, pointing to the other tables.

[edit] of course, that's the whole 3rd normal form thing .. you may or may not want/need to denormalize at some point, but that's a different discussion ;) [/edit]

answered Mar 30, 2015 at 18:55

Ditto

3,3793 gold badges16 silver badges31 bronze badges

Collectives™ on Stack Overflow

Oracle / SQL Error Invalid Identifier

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related