This is my CREATBL.sql code CREATE TABLE JOB( JOB_CODE VARCHAR (9) NOT NULL, JOB

Question

This is my CREATBL.sql code

CREATE TABLE JOB(
JOB_CODE VARCHAR (9)                   NOT NULL,
JOB_DESCRIPTION VARCHAR (24),
PRIMARY KEY (JOB_CODE));

CREATE TABLE PLAN(
PLAN_CODE VARCHAR(11)            NOT NULL,
PLAN_DESCRIPTION VARCHAR (28),
PRIMARY KEY (PLAN_CODE));

CREATE TABLE EMPLOYEE(
EMP_CODE VARCHAR (9)                   NOT NULL,
EMP_LNAME VARCHAR (24)               NOT NULL,
JOB_CODE VARCHAR (9)                   NOT NULL,
PRIMARY KEY (EMP_CODE),
FOREIGN KEY (JOB_CODE) REFERENCES JOB);

CREATE TABLE BENEFIT(
EMP_CODE VARCHAR (9)                   NOT NULL,
PLAN_CODE VARCHAR (11),
PRIMARY KEY (EMP_CODE, PLAN_CODE),
FOREIGN KEY (EMP_CODE) REFERENCES EMPLOYEE,
FOREIGN KEY (PLAN_CODE) REFERENCES PLAN);

This is my LOADTBL.sql code

INSERT INTO JOB
VALUES (1, 'Clerical');
INSERT INTO JOB
VALUES (2, 'Technical');
INSERT INTO JOB
VALUES (3, 'Managerial');
INSERT INTO JOB
VALUES (4, 'Data Scientist');
INSERT INTO JOB
VALUES (5, 'Database admin');
INSERT INTO JOB
VALUES (6, 'Network IT');
INSERT INTO JOB
VALUES (7, 'IT technician');

INSERT INTO PLAN
VALUES (1, 'Term life');
INSERT INTO PLAN
VALUES (2, 'Stock purchase');
INSERT INTO PLAN
VALUES (3, 'Long term disability');
INSERT INTO PLAN
VALUES (4, 'Dental');
INSERT INTO PLAN
VALUES (5, 'Eagle life');
INSERT INTO PLAN
VALUES (6, 'Premium plan');
INSERT INTO PLAN
VALUES (7, 'Golden plan');
INSERT INTO PLAN
VALUES (8, 'Platinum plan');

INSERT INTO EMPLOYEE
VALUES (14,'Rudell',2);
INSERT INTO EMPLOYEE
VALUES (15,'McDade',1);
INSERT INTO EMPLOYEE
VALUES (16,'Ruellardo',1);
INSERT INTO EMPLOYEE
VALUES (17,'Smith',3);
INSERT INTO EMPLOYEE
VALUES (20,'Smith',2):
INSERT INTO EMPLOYEE
VALUES (21,'Randal',2);
INSERT INTO EMPLOYEE
VALUES (22,'Mcdonald',1);
INSERT INTO EMPLOYEE
VALUES (23,'Reuben',1);
INSERT INTO EMPLOYEE
VALUES (24,'Buckshot',3);

INSERT INTO BENEFIT
VALUES (15, 2);
INSERT INTO BENEFIT
VALUES (15, 3);
INSERT INTO BENEFIT
VALUES (16, 1);
INSERT INTO BENEFIT
VALUES (17, 1);
INSERT INTO BENEFIT
VALUES (17, 3);
INSERT INTO BENEFIT
VALUES (17, 4);
INSERT INTO BENEFIT
VALUES (20, 3);
INSERT INTO BENEFIT
VALUES (21, 2);
INSERT INTO BENEFIT
VALUES (21, 3);
INSERT INTO BENEFIT
VALUES (22, 1);
INSERT INTO BENEFIT
VALUES (23, 1);

need urgent urgent help i keep getting constrain errors no matter how many times i redo it. Thanks

the errors are below i just can't seem to find the problem?????

1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. INSERT INTO BENEFIT ERROR at line 1: ORA-02291: integrity constraint (OPSSAMESQUIT.SYS_C00136556) violated parent key not found INSERT INTO BENEFIT ERROR at line 1: ORA02291: integrity constraint (OPSSAMESQUIT.SYS_C00136556) violated-parent key not found INSERT INTO BENEFIT ERROR at line 1: ORA-02291: integrity constraint (OPSSAMESQUIT.SYS_C00136556) violated parent key not found 1 row created. 1 row created. SQL>

Explanation / Answer

CREATE TABLE BENEFIT(

EMP_CODE VARCHAR (9) NOT NULL,

PLAN_CODE VARCHAR (11),

KEY (EMP_CODE, PLAN_CODE), // Build INDEX ont his non-unique-value columns

FOREIGN KEY (EMP_CODE) REFERENCES EMPLOYEE,

FOREIGN KEY(PLAN_CODE) REFERENCES PLAN

);

Here we are using the EMP_CODE and PLAN_CODE as reference variable from other tables, In that existing tables they are created as a UNIQUE and NOT NULL constraints(i.e, PRIMARY KEY CONSTRAINTS). In BENEFITS we are just treated them as referral columns. KEY is for change the unique columns in the other table changed as a non-unique-value columns.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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