I need to create tables and input values. All but two rows when inserted give me an error, Unique Constraint Violated. The last row in DEPT_LOCATIONS table and the second to the last row in DEPENDENT table. I’m not sure why these rows aren’t being added because other rows in the same table are.
Here’s my code so far:
/*Create Employee Table/*
create table EMPLOYEE(FNAME varchar(30), MINIT varchar(5), LNAME varchar(30), SSN varchar(10) PRIMARY KEY, BDATE varchar(30), ADDRESS varchar(30), SEX varchar(5), SALARY NUMERIC(10,0),
SUPERSSN varchar(30),DNO varchar(30));
/*Insert values into Employee Table/*
insert into EMPLOYEE values('John','B','Smith','123456789','1965-01-09','731 Fonden, Houston, TX','M', 30000,'333445555','5');
insert into EMPLOYEE values('Franklin','T','Wong','333445555','1955-12-08','638 Voss, Houston, TX','M', 40000,'888665555','5');
insert into EMPLOYEE values('Alice','J','Zelaya','999887777','1968-01-19','3321 Castle, Spring, TX','F', 25000,'987654321','4');
insert into EMPLOYEE values('Jennifer','S','Walace','987654321','1941-06-20','291 Berry, Bellaire, TX','F', 43000,'888665555','4');
insert into EMPLOYEE values('Ramesh','K','Narayan','666884444','1962-09-15','975 Fire Oak, Humble, TX','M', 38000,'333445555','5');
insert into EMPLOYEE values('Joyce','A','English','453453453','1972-07-31','5631 Rice, Houston, TX','F', 25000,'333445555','5');
insert into EMPLOYEE values('Ahmed','V','Jabbar','987987987','1969-03-29','980 Dallas, Houston, TX','M', 25000,'987654321','4');
insert into EMPLOYEE values('James','E','Bong','888665555','1937-11-10','450 Stone, Houston, TX','M', 55000,'null','1');
/*Create Department Table*/
create table DEPARTMENT(DNAME varchar(30), DNUMBER varchar(30) PRIMARY KEY, MGRSSN varchar(30), MGRSTARTDATE varchar(30));
/*Insert values into Department Table*/
insert into DEPARTMENT values('Research', '5', '333445555', '1988-05-22');
insert into DEPARTMENT values('Administration', '4', '987654321', '1995-01-01');
insert into DEPARTMENT values('Headquarters', '1', '888665555', '1981-06-19');
/*Create Department Location Table*/
create table DEPT_LOCATIONS(DNUMBER varchar(30) REFERENCES DEPARTMENT(DNUMBER), DLOCATION varchar(30) PRIMARY KEY);
/*Insert values into Department Location Table*/
insert into DEPT_LOCATIONS values('1', 'Houston');
insert into DEPT_LOCATIONS values('4', 'Stafford');
insert into DEPT_LOCATIONS values('5', 'Bellarire');
insert into DEPT_LOCATIONS values('5', 'Sugarland');
insert into DEPT_LOCATIONS values('5', 'Houston');
/*Create Project Table*/
create table PROJECT(PNAME varchar(30), PNUMBER varchar(30) PRIMARY KEY, PLOCATION varchar(30), DNUM varchar(30) REFERENCES DEPARTMENT(DNUMBER));
/*Insert values into Project Table*/
insert into PROJECT values('ProductX', '1', 'Bellaire', '5');
insert into PROJECT values('ProductY', '2', 'Sugarland', '5');
insert into PROJECT values('ProductZ', '3', 'Houston', '5');
insert into PROJECT values('Computerization', '10', 'Stafford', '4');
insert into PROJECT values('Reorganization', '20', 'Houston', '1');
insert into PROJECT values('Newbenefits', '30', 'Stafford', '4');
/*Create Works On table*/
create table WORKS_ON(ESSN varchar(30) REFERENCES EMPLOYEE(SSN), PNO varchar(30) REFERENCES PROJECT(PNUMBER), HOURS numeric(5, 1));
/*Insert values into Works on Table*/
insert into WORKS_ON values('123456789', '1', 32.5);
insert into WORKS_ON values('123456789', '2', 7.5);
insert into WORKS_ON values('666884444', '3', 40.0);
insert into WORKS_ON values('453453453', '1', 20.0);
insert into WORKS_ON values('453453453', '2', 20.0);
insert into WORKS_ON values('333445555', '2', 10.0);
insert into WORKS_ON values('333445555', '3', 10.0);
insert into WORKS_ON values('333445555', '10', 10.0);
insert into WORKS_ON values('333445555', '20', 10.0);
insert into WORKS_ON values('999887777', '30', 30.0);
insert into WORKS_ON values('999887777', '10', 10.0);
insert into WORKS_ON values('987987987', '10', 35.0);
insert into WORKS_ON values('987987987', '30', 5.0);
insert into WORKS_ON values('987654321', '30', 20.0);
insert into WORKS_ON values('987654321', '20', 15.0);
insert into WORKS_ON values('888665555', '20', null);
/*Create Dependent table*/
create table DEPENDENT(ESSN varchar(30) REFERENCES EMPLOYEE(SSN), DEPENDENT_NAME varchar(30) PRIMARY KEY, SEX varchar(30), BDATE varchar(30), RELATIONSHIP varchar(30));
/*Insert values into Dependent Table*/
insert into DEPENDENT values('333445555', 'Alice', 'F', '1986-04-05', 'Daughter');
insert into DEPENDENT values('333445555', 'Theodore', 'M', '1983-10-25', 'Son');
insert into DEPENDENT values('333445555', 'Joy', 'F', '1958-05-03', 'Spouse');
insert into DEPENDENT values('987654321', 'Abner', 'M', '1942-02-28', 'Spouse');
insert into DEPENDENT values('123456789', 'Michael', 'M', '1988-01-04', 'Son');
insert into DEPENDENT values('123456789', 'Alice', 'F', '1988-12-30', 'Daughter');
insert into DEPENDENT values('123456789', 'Elizabeth', 'F', '1967-05-05', 'Spouse');
The tables should look like this: enter image description here
Could someone please help me understand what is going on? Thank you
Advertisement
Answer
DLOCATION varchar(30) PRIMARY KEY
insert into DEPT_LOCATIONS values(‘1’, ‘Houston’);
insert into DEPT_LOCATIONS values(‘5’, ‘Houston’);
The DLOCATION column is a primary key, but you are trying to insert duplicate values 'Houston' into the table. Therefore it throws unique constraint violated error. To overcome this, you could create it as a composite primary key with DNUMBER and DLOCATION together as:
CONSTRAINT pk_num_loc PRIMARY KEY (DNUMBER, DLOCATION)
DDL & inserts:
/*Create Department Location Table*/
CREATE TABLE dept_locations (
dnumber VARCHAR(30)
REFERENCES department ( dnumber ),
dlocation VARCHAR(30),
CONSTRAINT pk_num_loc PRIMARY KEY ( dnumber, dlocation ));
insert into DEPT_LOCATIONS values('1', 'Houston');
insert into DEPT_LOCATIONS values('4', 'Stafford');
insert into DEPT_LOCATIONS values('5', 'Bellarire');
insert into DEPT_LOCATIONS values('5', 'Sugarland');
insert into DEPT_LOCATIONS values('5', 'Houston');
SELECT * FROM dept_locations;
Output:
DNUMBER DLOCATION ------- ------------------------------ 1 Houston 4 Stafford 5 Bellarire 5 Houston 5 Sugarland
Similarly, you need to do the same for rest of the tables which are failing due to unique constraint error.