CODEWITHSUNDEEP
sqloracle-database
miapaulina
miapaulina

Reputation: 25

Create a table with primary key and 2 foreign keys

Create the WPC assignment table. Note: there is a composite PRIMARY KEY and 2 FOREIGN KEYs which will require 3 CONSTRAINTs. See below Figure 2-44 taken from textbook. Also, please remember that no 2 constraints within the same schema can have the same name. Here is partial DDL for you to complete:

CREATE TABLE ASSIGNMENT 
(
    EmployeeNumber INTEGER NOT NULL, 
    ProjectID INTEGER NOT NULL,
    Hoursworked NUMBER (6,2),

    CONSTRAINT PK_Assignment 
        PRIMARY KEY (EmployeeNumber, ProjectID),

    CONSTRAINT FK_Assignment_EMPLOYEE 
        FOREIGN KEY (EmployeeNumber) REFERENCES EMPLOYEE,…

(please complete the remainder of this statement yourself)

I get an error:

Error starting at line : 1 in command:

CREATE TABLE ASSIGNMENT 
(
    EmployeeNumber INTEGER NOT NULL,
    ProjectID INTEGER NOT NULL,
    Hoursworked NUMBER (6,2),

    CONSTRAINT PK_Assignment 
        PRIMARY KEY (EmployeeNumber, ProjectID),
    CONSTRAINT FK_Assignment_EMPLOYEE 
        FOREIGN KEY (EmployeeNumber), 
    CONSTRAINT FK_Project_Assignment 
        FOREIGN KEY (ProjectID) REFERENCES EMPLOYEE.employeenumber(EmployeeNumber), PROJECT.projectID(ProjectId)
)

Error report:

ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:

I can't find what I'm missing.. Can someone please help?

Upvotes: 2

Views: 1889

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

You chose to create outline constraints (which is perfectly OK, only if you followed syntax). Just to mention another options, maybe you'll find them useful.

Note that columns which make the primary key doesn't have to explicitly be declared as not null because primary key constraint won't accept nulls anyway.

Outline constraints (they follow columns' definitions):

SQL> create table assignment
  2  (   employeenumber integer,
  3      projectid      integer,
  4      hoursworked    number (6,2),
  5      constraint pk_assignment
  6          primary key (employeenumber, projectid),
  7      constraint fk_assignment_employee
  8          foreign key (employeenumber) references employee (employeenumber),
  9      constraint fk_project_assignment
 10          foreign key (projectid) references project (projectid)
 11  );

Table created.

Inline constraints, in the same line as columns; as there's a composite primary key (consists of two or more columns), it can't be inline:

SQL> create table assignment
  2  (   employeenumber integer constraint fk_assignment_employee
  3                             references employee (employeenumber),
  4      projectid      integer constraint fk_project_assignment
  5                             references project (projectid),
  6      hoursworked    number (6,2),
  7      constraint pk_assignment
  8          primary key (employeenumber, projectid)
  9  );

Table created.

Table is created separately, constraints are created with ALTER TABLE command:

SQL> create table assignment
  2  (   employeenumber integer,
  3      projectid      integer,
  4      hoursworked    number (6,2)
  5  );

Table created.

SQL> alter table assignment add constraint pk_assingment primary key (employeenumber, projectid);

Table altered.

SQL> alter table assignment add constraint fk_assignment_employee
  2    foreign key (employeenumber) references employee (employeenumber);

Table altered.

SQL> alter table assignment add constraint fk_project_assignment
  2    foreign key (projectid) references project (projectid);

Table altered.

Upvotes: 1

Related Questions