Oracle SQL setting the primary and foreign key with constraint

Question

CREATE TABLE DriverTest
            (DTest_ID number(8),
            DTest_Type varchar2(50) NOT NULL,
            DTest_Date date NOT NULL,
            DTest_Result varchar2(50) NOT NULL,
            CONSTRAINT pk_DTestID PRIMARY KEY(DTest_ID));
            CONSTRAINT fk_EmployeeData FOREIGN KEY (Employee_ID) REFERENCES (Employee_ID));

CREATE TABLE Employee
        (Employee_ID number(8),
        Em_Name varchar2(50) NOT NULL,
        Em_Address varchar2(50) NOT NULL,
        Home_no number(15) NOT NULL,
        Em_no varchar2(30) NOT NULL,
        DoB date NOT NULL,
        Gender  varchar2(50) NOT NULL,
        NI_no number(10) NOT NULL,
        Annual_Salary number(10) NOT NULL,
        CONSTRAINT pk_EmployeeID PRIMARY KEY(Employee_ID));

Why won't this execute? It worked with my first two table for customer to hiring, but employee to driver, it says:

'Employee_ID' invalid indentifier, ORA-00904 and 00904 00000 - Invalid identifier

I thought I declared the primary and foreign? May I please have some help, I've looked at similar enquire, but they all have the same script as mine.

Gordon Linoff · Accepted Answer · 2018-12-14 00:47:52Z

You have a few errors in your code. Here is a working version in db<>fiddle.

Some of the issues:

The Employees table needs to be defined before it can be used in a foreign key relationship. Hence, the tables are in the wrong order.
A column used in a foreign key needs to be declared. So, EmployeeId needs to be declared in DriverTests.
You have an extra paren and semicolon in the second table definition.

I changed the names of the tables to be plural, because that is how I would name them.

The working code is:

CREATE TABLE Employees (
        Employee_ID number(8),
        Em_Name varchar2(50) NOT NULL,
        Em_Address varchar2(50) NOT NULL,
        Home_no number(15) NOT NULL,
        Em_no varchar2(30) NOT NULL,
        DoB date NOT NULL,
        Gender varchar2(50) NOT NULL,
        NI_no number(10) NOT NULL,
        Annual_Salary number(10) NOT NULL,
        CONSTRAINT pk_EmployeeID PRIMARY KEY(Employee_ID)
 );

CREATE TABLE DriverTests (
        DTest_ID number(8),
        Employee_ID number(8),
        DTest_Type varchar2(50) NOT NULL,
        DTest_Date date NOT NULL,
        DTest_Result varchar2(50) NOT NULL,
        CONSTRAINT pk_DTestID PRIMARY KEY (DTest_ID),
        CONSTRAINT fk_EmployeeData FOREIGN KEY (Employee_ID) REFERENCES Employees(Employee_ID)
);

Gauravsa · Accepted Answer · 2018-12-14 00:47:00Z

Your create table ordering is incorrect. Need to create Employee table first:

CREATE TABLE Employee
    (Employee_ID number(8),
    Em_Name varchar2(50) NOT NULL,
    Em_Address varchar2(50) NOT NULL,
    Home_no number(15) NOT NULL,
    Em_no varchar2(30) NOT NULL,
    DoB date NOT NULL,
    Gender  varchar2(50) NOT NULL,
    NI_no number(10) NOT NULL,
    Annual_Salary number(10) NOT NULL,
    CONSTRAINT pk_EmployeeID PRIMARY KEY(Employee_ID)); 

CREATE TABLE DriverTest
        (DTest_ID number(8),
        DTest_Type varchar2(50) NOT NULL,
        DTest_Date date NOT NULL,
        DTest_Result varchar2(50) NOT NULL,
        CONSTRAINT pk_DTestID PRIMARY KEY(DTest_ID));
        CONSTRAINT fk_EmployeeData FOREIGN KEY (Employee_ID) REFERENCES (Employee_ID));

Collectives™ on Stack Overflow

Oracle SQL setting the primary and foreign key with constraint

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