Hi can someone please help?? This is beginner's SQL but I'm new, any help would be great!
My CREATE and DROP statements, but I am getting an error when loading dummy INSERT values for tables that have a PK/FK combination (error code below). How do i fix INSERT?? :
ORA-00001: unique constraint (SQL_MXCHETYAKNOPXXVKCEVUIDYRD.EMPLOYEE_SKILLS_PK) violated ORA-06512: at "SYS.DBMS_SQL", line 1721
------SCRIPT BELOW--------
CREATE TABLE EMPLOYEE (
EmployeeID Char(11) NOT NULL,
FirstName Varchar(20) NOT NULL,
LastName Varchar(20) NOT NULL,
SSN Char(9) NOT NULL,
MiddleInitial Char(1),
Gender Char(1) NOT NULL,
Minority Varchar(3) NOT NULL,
CONSTRAINT employee_pk PRIMARY KEY (EmployeeID)
);
CREATE TABLE EMPLOYEE_SKILLS (
EmployeeID Char(11) NOT NULL,
JobCode Char(3) NOT NULL,
CONSTRAINT employee_skills_pk PRIMARY KEY (EmployeeID, JobCode)
CONSTRAINT employee_skills_fkone FOREIGN KEY (EmployeeID)
REFERENCES EMPLOYEE(EmployeeID) ON DELETE CASCADE,
CONSTRAINT employee_skills_fktwo FOREIGN KEY (JobCode)
REFERENCES SKILLS(JobCode)
);
CREATE TABLE SKILLS (
JobCode Char(3) NOT NULL,
JobClassification Varchar(20) NOT NULL,
BasicHourlyRate Numeric(3,2) NOT NULL,
FrigePayment Numeric(3,2) NOT NULL,
TotalComp Numeric(4,2) NOT NULL,
OvertimeMultiplier Numeric(4,2) NOT NULL,
CONSTRAINT skills_pk PRIMARY KEY (JobCode)
);
CREATE TABLE EMPLOYEE_PROJECT (
EmployeeID Char(11) NOT NULL,
ProjectNumber Varchar(11) NOT NULL,
CONSTRAINT employee_project_pk PRIMARY KEY (EmployeeID, ProjectNumber)
);
CREATE TABLE PROJECT (
ProjectNumber Varchar(11) NOT NULL,
ProjectName Varchar(10) NOT NULL,
ProjectLocation Varchar(50) NOT NULL,
ProjectDescription Varchar(100) NOT NULL,
ProjectCity Varchar(10) NOT NULL,
ProjectState Char(2) NOT NULL,
ProjectZip Char(6) NOT NULL,
CONSTRAINT project_pk PRIMARY KEY (ProjectNumber)
);
CREATE TABLE CONTRACTOR (
ContractorNumber Varchar(9) NOT NULL,
ContractorName Varchar(25) NOT NULL,
ContractorLocation Varchar(50) NOT NULL,
EEOCertification Varchar(3) NOT NULL,
EEOExpiration Date,
ContractorCity Varchar(10) NOT NULL,
ContractorState Char(2) NOT NULL,
ContractorZip Char(5) NOT NULL,
CONSTRAINT proj_contractor_pk PRIMARY KEY (ContractorNumber)
);
CREATE TABLE PROJ_CONTRACTOR (
ProjectNumber Varchar(11) NOT NULL,
ContractorNumber Varchar(9) NOT NULL,
CONSTRAINT proj_contractor_pkone PRIMARY KEY (ProjectNumber, ContractorNumber)
CONSTRAINT proj_contractor_projnumone_fk FOREIGN KEY (ProjectNumber)
REFERENCES PROJECT(ProjectNumber) ON DELETE CASCADE,
CONSTRAINT proj_contractor_connumone_fk FOREIGN KEY (ContractorNumber)
REFERENCES CONTRACTOR(ContractorNumber)
);
CREATE TABLE TIMESHEET (
EmployeeID Char(11) NOT NULL,
PayPeriod Date NOT NULL,
ProjectNumber Varchar(11) NOT NULL,
ContractorNumber Varchar(10) NOT NULL,
HoursLAB Numeric(3,2) NOT NULL,
HoursMAS Numeric(3,2) NOT NULL,
HoursEQP Numeric(3,2) NOT NULL,
OvertimeLAB Numeric(3,2) NOT NULL,
OvertimeMAS Numeric(3,2) NOT NULL,
OvertimeEQP Numeric(3,2) NOT NULL,
CONSTRAINT timesheet_pk PRIMARY KEY (PayPeriod),
CONSTRAINT timesheet_fk_empid FOREIGN KEY (EmployeeID)
REFERENCES EMPLOYEE(EmployeeID),
CONSTRAINT timesheet_fk_projnum FOREIGN KEY (ProjectNumber)
REFERENCES PROJECT(ProjectNumber)
);
CREATE TABLE PAYSTATEMENT (
EmployeeID Char(11) NOT NULL,
PayPeriod Date NOT NULL,
TaxYear Char(4) NOT NULL,
PayDate Date NOT NULL,
CONSTRAINT employeeid_pk PRIMARY KEY (EmployeeID),
CONSTRAINT employeeid_pktwo FOREIGN KEY (EmployeeID)
REFERENCES EMPLOYEE(EmployeeID) ON DELETE CASCADE,
CONSTRAINT payperiod_fk FOREIGN KEY (PayPeriod)
REFERENCES TIMESHEET(PayPeriod)
);
--BEGIN DROP DATA
DROP TABLE EMPLOYEE_SKILLS;
DROP TABLE SKILLS;
DROP TABLE EMPLOYEE_PROJECT;
DROP TABLE PROJ_CONTRACTOR;
DROP TABLE CONTRACTOR;
DROP TABLE PAYSTATEMENT;
DROP TABLE TIMESHEET;
DROP TABLE PROJECT;
DROP TABLE EMPLOYEE;
--drop and recreate table
--BEGIN DUMMY 1
INSERT INTO EMPLOYEE VALUES ('390 05 4489', 'James', 'Worker', '4489', 'E', 'M', 'No');
INSERT INTO EMPLOYEE_SKILLS VALUES ('390 05 4489', 'LAB');
--^^error
INSERT INTO SKILLS VALUES ('LAB', 'General Labor', 15.00, 3.00, 18.00, 1.50);
--^^error
INSERT INTO EMPLOYEE_PROJECT VALUES ('390 05 4489', 'ILSBJ335005');
--worked^^
INSERT INTO PROJECT VALUES ('ILSBJ335005', 'Project 1',
'3456 Foobar Blvd Benjamin, Illinois 62980', 'Landscaping for
park according to city plans', 'Benjamin', 'IL', '62980');
--worked^^
INSERT INTO CONTRACTOR VALUES ('310646843', 'Lily Landscaping Company',
'Franklin, Illinois 62270 Cook County', 'No', '31-DEC-20', 'Bejamin', 'IL', '62980');
--^^worked
INSERT INTO PROJ_CONTRACTOR VALUES ('ILSBJ335005', '310646843');
--^^error
INSERT INTO TIMESHEET VALUES ('390 05 4489', '14-AUG-20', 'IL-SBJ-335-005',
'310646843', 25, 5, 0, 0, 0, 3);
INSERT INTO PAYSTATEMENT ('390 05 4489', '14-AUG-20', '2020', '21-AUG-20');