CODEWITHSUNDEEP
sqloracle-database
Daniel Khosa
Daniel Khosa

Reputation: 37

ORA-00904: "BRANCH_ID": invalid identifier

Why is the create tables not allowing me to add the staff tables, the CONSTRAINT seem logical to me.

CREATE TABLE branch 
(
Branch_ID  VARCHAR(2),
Branch_Name  VARCHAR(20),
Branch_Address  VARCHAR(40),
Branch_Postcode  VARCHAR(15),
Branch_Telephone NUMBER(15),
Branch_email VARCHAR(40),
Branch_Fax NUMBER(15),
PRIMARY KEY ( Branch_ID )
);

CREATE TABLE staff
(
    Staff_ID INT NOT NULL PRIMARY KEY,
    firstName VARCHAR(20),
    lastName VARCHAR(20),
    addressLine_1 VARCHAR2(30),
    city VARCHAR2(15),
    postcode VARCHAR2(7),
    telephone VARCHAR2(15),
    salary DECIMAL (19,4),
    CONSTRAINT BRANCH_fk FOREIGN KEY(Branch_ID ) REFERENCES branch(Branch_ID )
);

ORA-00904: "BRANCH_ID": invalid identifier

Upvotes: 0

Views: 1275

Answers (1)

Max
Max

Reputation: 13378

I think you forgot to add the Branch_ID field. You are referencing this one to be your foreign key at the Staff table, but you didn't define it in your staff table yet.

Change staff table definition to:

CREATE TABLE staff
(
    Staff_ID INT NOT NULL PRIMARY KEY,
    firstName VARCHAR(20),
    lastName VARCHAR(20),
    addressLine_1 VARCHAR2(30),
    city VARCHAR2(15),
    postcode VARCHAR2(7),
    telephone VARCHAR2(15),
    salary DECIMAL (19,4),
    Branch_ID VARCHAR2(2),
    CONSTRAINT BRANCH_fk FOREIGN KEY(Branch_ID) REFERENCES branch(Branch_ID)
);

About Gordon Linoff's comment, check the link below. I modified my answer to match the 'best-practice'.

Difference VARCHAR and VARCHAR2

Upvotes: 4

Related Questions