SQL name error with foreign key in sql code

Question

I am getting ORA-00955 error which is name is already used by an existing object. I know it is in the foreign key constraint im trying to use. It happens in both foreign key constraints. I cant figure out why its happening. I tried renaming them to anything possible and I cant understand why it wont work.

DROP TABLE STUDENT;
CREATE TABLE STUDENT
(stuID VARCHAR (7) PRIMARY KEY NOT NULL,
major VARCHAR (15) NOT NULL,
firstName VARCHAR (15) NOT NULL,
lastName VARCHAR (15) NOT NULL,
yr  VARCHAR(8) NOT NULL
);

DROP TABLE ENROLL;
CREATE TABLE ENROLL
(stuID VARCHAR (7) NOT NULL,
grade VARCHAR (1) NOT NULL,
courseID VARCHAR(7) NOT NULL,
  CONSTRAINT fk_STUDENT
   FOREIGN KEY (stuID)
   REFERENCES STUDENT(stuid)
);

DROP TABLE CLASS;
CREATE TABLE CLASS
(instructorID VARCHAR(7) NOT NULL,
classRoomNumber VARCHAR(7) NOT NULL,
dateStarts DATE NOT NULL,
courseID VARCHAR(7) PRIMARY KEY NOT NULL,
description CHAR(100) NOT NULL,
 CONSTRAINT fk_INSTRUCTOR
  FOREIGN KEY (instructorID)
  REFERENCES INSTRUCTOR(instructorid)
);

DROP TABLE INSTRUCTOR;
CREATE TABLE INSTRUCTOR
(firstName VARCHAR (15) NOT NULL,
lastName VARCHAR (15) NOT NULL,
departmentName VARCHAR (15) NOT NULL,
instructorID VARCHAR(7) PRIMARY KEY NOT NULL
);

You cannot define a reference to a table that does not exist yet. Put instructor above class — juergen d
– juergen d, Commented Dec 6, 2018 at 20:42

Littlefoot · Accepted Answer · 2018-12-07 18:53:34Z

In a comment, you said that there's still an error.

If you create (and/or drop) tables in a correct order, everything is OK. I prefer dropping them separately.

At first, tables don't exist so I'll just create them:

SQL> CREATE TABLE student (
  2    stuid       VARCHAR(7) PRIMARY KEY NOT NULL,
  3    major       VARCHAR(15) NOT NULL,
  4    firstname   VARCHAR(15) NOT NULL,
  5    lastname    VARCHAR(15) NOT NULL,
  6    yr          VARCHAR(8) NOT NULL
  7  );

Table created.

SQL> CREATE TABLE enroll (
  2    stuid      VARCHAR(7) NOT NULL,
  3    grade      VARCHAR(1) NOT NULL,
  4    courseid   VARCHAR(7) NOT NULL,
  5    CONSTRAINT fk_student FOREIGN KEY ( stuid )
  6      REFERENCES student ( stuid )
  7  );

Table created.

SQL> CREATE TABLE instructor (
  2    firstname        VARCHAR(15) NOT NULL,
  3    lastname         VARCHAR(15) NOT NULL,
  4    departmentname   VARCHAR(15) NOT NULL,
  5    instructorid     VARCHAR(7) PRIMARY KEY NOT NULL
  6  );

Table created.

SQL> CREATE TABLE class (
  2    instructorid      VARCHAR(7) NOT NULL,
  3    classroomnumber   VARCHAR(7) NOT NULL,
  4    datestarts        DATE NOT NULL,
  5    courseid          VARCHAR(7) PRIMARY KEY NOT NULL,
  6    description       CHAR(100) NOT NULL,
  7    CONSTRAINT fk_instructor FOREIGN KEY ( instructorid )
  8      REFERENCES instructor ( instructorid )
  9  );

Table created.

Drop tables in reverse order, so that detail is dropped before its master

SQL> DROP TABLE enroll;

Table dropped.

SQL> DROP TABLE student;

Table dropped.

SQL> DROP TABLE class;

Table dropped.

SQL> DROP TABLE instructor;

Table dropped.

SQL>

GMB · Accepted Answer · 2018-12-07 02:24:36Z

0

Table INSTRUCTOR is referenced by table CLASS, hence you heed to create table INSTRUCTOR before you create table CLASS.

Also, you should use DROP TABLE ... CASCADE CONSTRAINTS instead of just DROP TABLE .... This allows foreign and primary keys to be properly dropped at the same time as the table, and might avoid error name already used by existing object that you are currently getting.

edited Dec 7, 2018 at 2:24

answered Dec 7, 2018 at 1:45

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

SQL name error with foreign key in sql code

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