CREATE TABLE TASK_SKILL (TASK_ID INT REFERENCES PROJECT_SCHEDULE_TASK(TASK_ID),
                SKILL_ID INT REFERENCES PROJECT(PROJECT_ID),
                NUMBER_OF_EMPLOYEES INT,
                PRIMARY KEY (TASK_ID, PROJECT_ID, SKILL_ID));

Good day, I know my questions seem to be quite newbie-ish and is common sense for most of you here, but I am still trying to learn SQL

I encountered "ORA-00904: "PROJECT_ID": invalid identifier".

I have already checked and it looks like I have a Project_ID column but still, I can't seem to run it.

CodePudding user response：

You are creating a table TASK_SKILL with 3 fiels: TASK_ID, SKILL_ID and NUMBER_OF_EMPLOYEES. Also you want to create a Primary Key by TASK_ID, PROJECT_ID, SKILL_ID. Oracle is right, you do not have a PROJECT_ID field in your table. Your field is called SKILL_ID, so the Primary key should be created using it, like this:

CREATE TABLE TASK_SKILL (TASK_ID INT REFERENCES PROJECT_SCHEDULE_TASK(TASK_ID),
                SKILL_ID INT REFERENCES PROJECT(PROJECT_ID),
                NUMBER_OF_EMPLOYEES INT,
                PRIMARY KEY (TASK_ID, SKILL_ID));

In the PK of a table you ony include fields from the table and not field from the related table. So, no need to include te referenced PROJECT_ID.

CodePudding user response：

This happens because you are trying to create a table with a column that doesn't exist, in this case, PROJECT_ID. In the TASK_SKILL Table you only have 3 Columns defined:

1. TASK_ID, which is a foreign key of the PROJECT_SCHEDULE_TASK table
2. SKILL_ID, which is a foreign key of the PROJECT table.
3. NUMBER_OF_EMPLOYEES which is an integer.

Try to create the table like this:

CREATE TABLE TASK_SKILL (TASK_ID INT REFERENCES PROJECT_SCHEDULE_TASK(TASK_ID),
                SKILL_ID INT REFERENCES PROJECT(PROJECT_ID),
                NUMBER_OF_EMPLOYEES INT,
                PRIMARY KEY (TASK_ID, SKILL_ID));

Lastly, if you're just starting out learning SQL, you can first lay the foundations of database design before going hands-on.

oracle documentation of foreign keys