CODEWITHSUNDEEP
sqloracle-database
zgstudent
zgstudent

Reputation: 1

SQL Live Beginner

A student learning about SQL Live for the first time - I am attempting to create tables, but the problem is that I receive this error code: ORA-00942: table or view does not exist. Unsure of what I am doing wrong.

Here is my code:

CREATE TABLE Emp 
(
    Empno INT PRIMARY KEY,
    Ename VARCHAR(50) NOT NULL,
    DNO INT,
    BirthDate DATE,
    Sal DECIMAL(10,2) CHECK (Sal BETWEEN 1000 AND 10000),
    CONSTRAINT fk_Dept_Emp FOREIGN KEY (DNO) REFERENCES Dept(Deptno)
);

CREATE TABLE Dept 
(
    Deptno INT PRIMARY KEY,
    Dname VARCHAR(50) UNIQUE NOT NULL,
    Mgr INT,
    CONSTRAINT fk_Emp_Dept FOREIGN KEY (Mgr) REFERENCES Emp(Empno)
);

These were my instructions:

Using SQL Live, create two tables:

Select by yourself the appropriate data type for each column.

For Emp table add the following constraints: Empno is the PK, Ename not null, DNO is FK references Dept(Deptno), Sal should be between 1000 and 10000

For Dept table add the following Constraint: Deptno is PK, Dname is unique, Mgr is FK reference Emp(Empno).

Upvotes: 0

Views: 167

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

The problem with your code is you are trying to create interdependent foreign keys and no matter whichever order you try they will give error because 1 foreign key will not be present before another.

To overcome this problem. Try creating 1 table without foreign key. Then create another table and then add the foreign in first table through ALTER TABLE command -

CREATE TABLE Emp 
(
    Empno INT PRIMARY KEY,
    Ename VARCHAR(50) NOT NULL,
    DNO INT,
    BirthDate DATE,
    Sal DECIMAL(10,2) CHECK (Sal BETWEEN 1000 AND 10000)
);

CREATE TABLE Dept 
(
    Deptno INT PRIMARY KEY,
    Dname VARCHAR(50) UNIQUE NOT NULL,
    Mgr INT,
    CONSTRAINT fk_Emp_Dept FOREIGN KEY (Mgr) REFERENCES Emp(Empno)
);

ALTER TABLE Emp ADD CONSTRAINT fk_Dept_Emp FOREIGN KEY (DNO) REFERENCES Dept(Deptno);

Demo.

Upvotes: 1

Related Questions