J. high J. high - 4 months ago 25
SQL Question

I cant properly join the foreign keys i've created

So i created this mother board in sql in which doesnt have any foreign keys at all. Just primary key

CREATE TABLE ALUMNI (
ALUMNIID INTEGER CONSTRAINT ALUMN_ID_PK PRIMARY KEY,
ANAME VARCHAR(30) NOT NULL,
AADDRESS VARCHAR2(30) NOT NULL,
AEMAIL VARCHAR2(30) NOT NULL,
AWORK# INTEGER NOT NULL,
AHOME# INTEGER,
ACITY VARCHAR(30) NOT NULL
);


And then there's this baby table in which inherits/foreign key the primary of the ALUMNIID

CREATE TABLE QUALIFICATION
(QUALIFICATIONID INTEGER CONSTRAINT QUALI_ID PRIMARY KEY,
DEGREEEARNED VARCHAR2(30) NOT NULL,
AREASPECIFIED VARCHAR2(30) NOT NULL,
DISCIPAREA VARCHAR2(30) NOT NULL,
DATEGRAD VARCHAR2(30) NOT NULL,
SCHOONAME VARCHAR2(30) NOT NULL,
QUALI_ALUM INTEGER,
CONSTRAINT QUALI_ALUM_FK FOREIGN KEY (QUALI_ALUM) REFERENCES
ALUMNI (ALUMNIID));


Heres where I got some problems. I tried the views statements

CREATE VIEW VIEWA AS SELECT ALUMNIID, ANAME, AEMAIL, AWORK#, AHOME#,DATEGRAD FROM ALUMNI,QUALIFICATION WHERE
ALUMNI.ALUMNIID = QUALIFICATION.ALUMNIID AND
DATEGRAD = '2015';


But the output says

Error starting at line : 1 in command -
CREATE VIEW VIEWA AS SELECT ALUMNIID, ANAME, AEMAIL, AWORK#, AHOME#,DATEGRAD FROM ALUMNI,QUALIFICATION WHERE
ALUMNI.ALUMNIID = QUALIFICATION.QUALI_ALUM_FK AND
DATEGRAD = '2015'
Error report -
SQL Error: ORA-00904: "QUALIFICATION"."QUALI_ALUM_FK": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:


Is my creating or combining the pk in other tables wrong? Thats why I cannot join two tables in the views?

Answer

First, you should fix your query to use proper JOIN syntax. The problem is that you are using the name of the foreign key reference rather than the name of the column. You would seem to want:

CREATE VIEW VIEWA AS
    SELECT a.ALUMNIID, a.ANAME, a.AEMAIL, a.AWORK#, a.AHOME#, q.DATEGRAD
    FROM ALUMNI a JOIN
         QUALIFICATION q
         ON a.ALUMNIID = q.QUALI_ALUM AND
            q.DATEGRAD = '2015';

Note that the table aliases make the query easier to write and to read. I don't see a reason to include DATEGRAD in the SELECT, because you know it is '2015'.

Also, I would advise you to make the column names the same in the two tables As a general rule, I like to have foreign key references have the same column name as the primary key they are referring to. This makes the reference transparent (this is not always possible, but it usually is).