What is wrong with my \"BusinessViolation\" and \"Inspection\" tables? CREATE TA

Question

What is wrong with my "BusinessViolation" and "Inspection" tables?

CREATE TABLE BUSINESSVIOLATION(
CAMIS           VarChar(6) NOT NULL,
InspectionDate Date,
ActionCode      VarChar(1) NOT NULL,
ViolationCode   VarChar(100) NOT NULL,
PRIMARY KEY(CAMIS, InspectionDate, ActionCode, ViolationCode),
FOREING KEY(ViolationCode) REFERENCES ViolationFlag(ViolationCode),
FOREING KEY(ActionCode) REFERENCES Action(ActionCode),
FOREING KEY(InspectionDate) REFERENCES Inspection(InspectionDate),
FOREING KEY(CAMIS) REFERENCES Building(CAMIS)
);

CREATE TABLE INSPECTION(
InspectionDate        Date,
CAMIS                 VarChar(6)   NOT NULL,
Score                 Int,
CurrentGrade          Char(1),
GradeDate             Date,
Action                Char(100),
PRIMARY KEY(InspectionDate, CAMIS)
);

Explanation / Answer

Answer :

Just Create Tables For ViolationFlag,Action,Building with ViolationCode,ActionCode,CAMIS as primary keys.And Your INSPECTION table is working perfectly.Below is the process i have resolved the error in SQL prompt.

SQL> connect system/123
Connected.
SQL> CREATE TABLE BUSINESSVIOLATION(
2 CAMIS VARCHAR(6) NOT NULL,
3 InspectionDate DATE,
4 ActionCode VARCHAR(1) NOT NULL,
5 ViolationCode VARCHAR(100) NOT NULL,
6 PRIMARY KEY(CAMIS, InspectionDate, ActionCode, ViolationCode),
7 FOREIGN KEY(ViolationCode) REFERENCES ViolationFlag(ViolationCode),
8 FOREIGN KEY(ActionCode) REFERENCES Action(ActionCode),
9 FOREIGN KEY(InspectionDate) REFERENCES Inspection(InspectionDate),
10 FOREIGN KEY(CAMIS) REFERENCES Building(CAMIS)
11 );
FOREIGN KEY(ViolationCode) REFERENCES ViolationFlag(ViolationCode),
*
ERROR at line 7:
ORA-00942: table or view does not exist

SQL> CREATE TABLE ViolationFlag(
2 ViolationCode VARCHAR(100) NOT NULL);

Table created.

SQL> CREATE TABLE BUSINESSVIOLATION(
2 CAMIS VARCHAR(6) NOT NULL,
3 InspectionDate DATE,
4 ActionCode VARCHAR(1) NOT NULL,
5 ViolationCode VARCHAR(100) NOT NULL,
6 PRIMARY KEY(CAMIS, InspectionDate, ActionCode, ViolationCode),
7 FOREIGN KEY(ViolationCode) REFERENCES ViolationFlag(ViolationCode),
8 FOREIGN KEY(ActionCode) REFERENCES Action(ActionCode),
9 FOREIGN KEY(InspectionDate) REFERENCES Inspection(InspectionDate),
10 FOREIGN KEY(CAMIS) REFERENCES Building(CAMIS)
11 );
FOREIGN KEY(ViolationCode) REFERENCES ViolationFlag(ViolationCode),
*
ERROR at line 7:
ORA-02270: no matching unique or primary key for this column-list

SQL> CREATE TABLE ViolationFlag(
2 ViolationCode VARCHAR(100) NOT NULL,
3 PRIMARY KEY(ViolationCode));
CREATE TABLE ViolationFlag(
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> CREATE TABLE ViolationFlags(
2 ViolationCode VARCHAR(100) NOT NULL,
3 PRIMARY KEY(ViolationCode));

Table created.

SQL> CREATE TABLE BUSINESSVIOLATION(
2 CAMIS VARCHAR(6) NOT NULL,
3 InspectionDate DATE,
4 ActionCode VARCHAR(1) NOT NULL,
5 ViolationCode VARCHAR(100) NOT NULL,
6 PRIMARY KEY(CAMIS, InspectionDate, ActionCode, ViolationCode),
7 FOREIGN KEY(ViolationCode) REFERENCES ViolationFlags(ViolationCode),
8 FOREIGN KEY(ActionCode) REFERENCES Action(ActionCode),
9 FOREIGN KEY(InspectionDate) REFERENCES Inspection(InspectionDate),
10 FOREIGN KEY(CAMIS) REFERENCES Building(CAMIS)
11 );
FOREIGN KEY(ActionCode) REFERENCES Action(ActionCode),
*
ERROR at line 8:
ORA-00942: table or view does not exist

SQL> CREATE TABLE Action(
2 ActionCode VARCHAR(1) NOT NULL,
3 PRIMARY KEY(ActionCode));

Table created.

SQL> CREATE TABLE BUSINESSVIOLATION(
2 CAMIS VARCHAR(6) NOT NULL,
3 InspectionDate DATE,
4 ActionCode VARCHAR(1) NOT NULL,
5 ViolationCode VARCHAR(100) NOT NULL,
6 PRIMARY KEY(CAMIS, InspectionDate, ActionCode, ViolationCode),
7 FOREIGN KEY(ViolationCode) REFERENCES ViolationFlags(ViolationCode),
8 FOREIGN KEY(ActionCode) REFERENCES Action(ActionCode),
9 FOREIGN KEY(InspectionDate) REFERENCES Inspection(InspectionDate),
10 FOREIGN KEY(CAMIS) REFERENCES Building(CAMIS)
11 );
FOREIGN KEY(InspectionDate) REFERENCES Inspection(InspectionDate),
*
ERROR at line 9:
ORA-02270: no matching unique or primary key for this column-list

//PLEASE DONT CREATE THIS TABLE SINCE YOU HAVE ALREADY CREATED IT
SQL> CREATE TABLE Inspection(
2 InspectionDate DATE,
3 PRIMARY KEY(InspectionDate));
CREATE TABLE Inspection(
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

//HERE I HAVE TESTED USING INSPECTIONS TABLE TO CHECK WEATHER WORKING OR NOT

SQL> CREATE TABLE Inspections(
2 InspectionDate DATE,
3 PRIMARY KEY(InspectionDate));

Table created.

SQL> CREATE TABLE BUSINESSVIOLATION(
2 CAMIS VARCHAR(6) NOT NULL,
3 InspectionDate DATE,
4 ActionCode VARCHAR(1) NOT NULL,
5 ViolationCode VARCHAR(100) NOT NULL,
6 PRIMARY KEY(CAMIS, InspectionDate, ActionCode, ViolationCode),
7 FOREIGN KEY(ViolationCode) REFERENCES ViolationFlags(ViolationCode),
8 FOREIGN KEY(ActionCode) REFERENCES Action(ActionCode),
9 FOREIGN KEY(InspectionDate) REFERENCES Inspections(InspectionDate),
10 FOREIGN KEY(CAMIS) REFERENCES Building(CAMIS)
11 );
FOREIGN KEY(CAMIS) REFERENCES Building(CAMIS)
*
ERROR at line 10:
ORA-00942: table or view does not exist

SQL> CREATE TABLE Building(
2 CAMIS VARCHAR(6) NOT NULL,
3 PRIMARY KEY(CAMIS));

Table created.

SQL> CREATE TABLE BUSINESSVIOLATION(
2 CAMIS VARCHAR(6) NOT NULL,
3 InspectionDate DATE,
4 ActionCode VARCHAR(1) NOT NULL,
5 ViolationCode VARCHAR(100) NOT NULL,
6 PRIMARY KEY(CAMIS, InspectionDate, ActionCode, ViolationCode),
7 FOREIGN KEY(ViolationCode) REFERENCES ViolationFlags(ViolationCode),
8 FOREIGN KEY(ActionCode) REFERENCES Action(ActionCode),
9 FOREIGN KEY(InspectionDate) REFERENCES Inspections(InspectionDate),
10 FOREIGN KEY(CAMIS) REFERENCES Building(CAMIS)
11 );

Table created. //FINALLY YOUR TABLE IS CREATED

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.