PL/SQL Primary Key Violation

Question

I keep getting a primary key violation in the following code. If I drop the primary key in the Times table, the everything populates as expected but I have to have a primary key. In my CASE statement, I tried to remove the sale_day field but then received a violation for inserting a NULL in the primary key. In my cursor, you will see reference to a Sales table, this table contains the sale_date field that populates the sale_day field in the Times table. I am lost on how to proceed.

 SQL> CREATE TABLE Times (
  2  sale_day DATE NOT NULL,
  3  day_type VARCHAR2(50) NOT NULL,
  4  PRIMARY KEY (sale_day));

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE Time_Procedure
  2  AS
  3  l_sale_date date;
  4  temp_value varchar2(5);
  5  CURSOR c1 IS SELECT SALE_DATE FROM SALES;
  6
  7  BEGIN
  8  OPEN c1;
  9  LOOP
 10  FETCH c1 INTO l_sale_date;
 11  EXIT WHEN c1%NOTFOUND;
 12  SELECT to_char(to_date(l_sale_date), 'DY') into temp_value from dual;
 13  CASE
 14  WHEN l_sale_date LIKE '%-JAN-01' OR l_sale_date LIKE '%-JAN-21' OR l_sale_date LIKE '%-FEB-18' OR l_sale_date LIKE '%-MAY-28'
 15  OR l_sale_date LIKE '%-JUL-04' OR l_sale_date LIKE '%-SEP-03' OR l_sale_date LIKE '%-OCT-08' OR l_sale_date LIKE '%-NOV-11'
 16  OR l_sale_date LIKE '%-NOV-22' OR l_sale_date LIKE '%-DEC-25'  THEN
 17  INSERT INTO Times values(l_sale_date,'Holiday');
 18  WHEN temp_value='MON' OR temp_value ='TUE' OR temp_value='WED' OR temp_value='THU' OR temp_value='FRI' THEN
 19  INSERT INTO Times values (l_sale_date,'Weekday');
 20  ELSE
 21  INSERT INTO Times values (l_sale_date, 'Weekend');
 22  END CASE;
 23  END LOOP;
 24  CLOSE c1;
 25  END;
 26  /

Procedure created.

SQL>
SQL> /* Populate table*/
SQL>
SQL> BEGIN
  2  Time_Procedure;
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (SDEV350USER.SYS_C0013043) violated
ORA-06512: at "SDEV350USER.TIME_PROCEDURE", line 19
ORA-06512: at line 2

Possible duplicate of [Oracle use LIKE '%' on DATE](https://stackoverflow.com/questions/33151961/oracle-use-like-on-date) — Tripp Kinetics, Apr 16 '18 at 23:59
Are you absolutely certain that the source data in `SALES` table consists of unique dates? — APC, Apr 17 '18 at 06:18

score 0 · Accepted Answer · answered Apr 17 '18 at 06:31

It seems likely that SALES is not a source of unique dates. Perhaps it contains days with a time element? If so, perhaps truncating the date (to remove the time element) and applying a DISTINCT will solve the problem.

There is no need for PL/SQL to populate TIMES, as we can use CASE in SQL.

insert into times
select sale_date
       , case
           when to_char(sale_date, 'MON-DD') in ('JAN-01','JAN-21','FEB-18','MAY-28'
                       ,'JUL-04','SEP-03','OCT-08','NOV-11','NOV-22','DEC-25') then
                'Holiday'        
           when to_char(sale_date, 'DY') in ('MON','TUE','WED','THU','FRI') then
                'Weekday'
           else
                'Weekend'
         end as day_type  
from (select distinct trunc(sale_date) as sale_date 
      from sales)

Looking at the dates in the Sales table, you are correct, the dates are not unique and no there is not a time element to the field. My requirement however is to make the sale_day field in the Times table the primary key. I am also required to use PL/SQL to populate the Times table but I do agree that it is not needed. I will try the select distinct statement. Thank you! — beginnerDeveloper, Apr 17 '18 at 10:46

PL/SQL Primary Key Violation

1 Answers1