CODEWITHSUNDEEP
sqloracle-database
Robin
Robin

Reputation: 262

One or more foreign keys created in Create table statement Oracle

I have two tables, an employee table and a project table. Each project has to have at least one employee, but it could have more. I'm struggling to figure out how to create the Project table so you can have a non-fixed number of foreign keys. What I have right now is this:

CREATE TABLE EMPLOYEE (
EmployeeID number(10) NOT NULL,
FirstName varchar2(20),
LastName varchar2(20),
Address varchar2(20),
City varchar2(20),
State varchar2(2),
Zip number(5),
DoB varchar2(10),
PRIMARY KEY (EmployeeID)
);

INSERT INTO EMPLOYEE
Values ('1', 'Joe', 'Smith', '1 Main St', 'Stillwater', 'OK', 74075, '07/05/1992');

INSERT INTO EMPLOYEE
Values ('2', 'John', 'Johnson', '3 College Way', 'Stillwater', 'OK', 74074, '08/05/1992');

CREATE TABLE PROJECT (
    ProjectID number(10) NOT NULL,
    ProjectName varchar2(20),
    ProjectDesc varchar2(20),
    ProjectStartDate varchar2(20),
    EmployeeID number(10),
    PRIMARY KEY (ProjectID),
    CONSTRAINT FK_EmployeeID FOREIGN KEY (EmployeeID)
    REFERENCES EMPLOYEE(EmployeeID)
);

INSERT INTO Project
Values ('1', 'ProjectA', 'A Project', '12/05/2017', '1');

INSERT INTO Project
Values ('2', 'ProjectB', 'B Project', '01/05/2018', '1', '2');

Obviously, the last line is where it goes wrong, because there are too many pieces of information. How do I solve this?

Upvotes: 0

Views: 34

Answers (1)

Obsidian Age
Obsidian Age

Reputation: 42304

The values directly correlate to the fields in the table, and you need to ensure that the number of values you insert correctly corresponds to the number of fields.

In order to insert more than one row into the relevant table, you have to split up the inserts. So, you need to split up your two employee insertions into ProjectB, and insert both employee independently.

Note that you'll also want to ensure that both of your primary keys auto increment, so that you don't have to worry about the foreign key constraints! In Oracle, you can do this with the IDENTITY column:

CREATE TABLE EMPLOYEE (
  EmployeeID number(10) NOT NULL as IDENTITY,
  ...
  PRIMARY KEY (EmployeeID)
);

CREATE TABLE PROJECT (
  ProjectID number(10) NOT NULL as IDENTITY,
  ...
  PRIMARY KEY (ProjectID)
);

Then you can insert with:

INSERT INTO Project Values (null, 'ProjectB', 'B Project', '01/05/2018', '1');
INSERT INTO Project Values (null, 'ProjectB', 'B Project', '01/05/2018', '2');

This gives you a total of three INSERT statements:

INSERT INTO Project Values (null, 'ProjectA', 'A Project', '12/05/2017', '1');
INSERT INTO Project Values (null, 'ProjectB', 'B Project', '01/05/2018', '1');
INSERT INTO Project Values (null, 'ProjectB', 'B Project', '01/05/2018', '2');

Note that because the employee is the only thing changing in your two inserts to ProjectB, you might want to consider looping over these inserts:

    DECLARE
    i    NUMBER (1);
BEGIN
    FOR i IN 1 .. x
    LOOP
        INSERT INTO Project Values ('2', 'ProjectB', 'B Project', '01/05/2018', i);
    END LOOP;
END;

However, down the line, I can envisage multiple projects can have multiple employees, and employees working on multiple projects. This would be a many to many relationship, which would require a bridging table (also know as associative entity). It may be worthwhile keeping this in mind :)

Hope this helps!

Upvotes: 1

Related Questions