Inserting values into the table

Asked by At

I'm trying to insert the values into the tables that I created. This is the values that I'm trying to insert.

INSERT INTO DDR_Rental (customer_ID, rental_date, rent_fee, film_title, start_date, expiry_date, rating)
VALUES (12345, '12-Mar-19', '4.99', 'Peppermint', '12-Mar-19', '22-Mar-19', 4);

This is the datatypes and the constraints.

CREATE TABLE DDR_Rental
 (customer_ID NUMBER(5),
  rental_date DATE,
  rent_fee NUMBER(3,2) CONSTRAINT SYS_RENTAL_FEE_NN NOT NULL,
  film_title VARCHAR2(20),
  start_date DATE,
  expiry_date DATE,
  rating NUMBER(5),
  CONSTRAINT SYS_RENTAL_PK PRIMARY KEY ((customer_ID), (rental_date), (film_title)),
  CONSTRAINT SYS_RENTAL_CUS_ID_FK1 FOREIGN KEY (customer_ID) REFERENCES 
     DDR_CUSTOMER(CUSTOMER_ID),
  CONSTRAINT SYS_RENTAL_FILM_TITLE_FK2 FOREIGN KEY (film_title) REFERENCES
     DDR_MOVIE_TITLE(FILM_TITLE),
  CONSTRAINT SYS_RENTAL_EXP_DATE_CK CHECK (expiry_date >= start_date),
  CONSTRAINT SYS_RENTAL_START_DATE_CK CHECK (start_date >= rental_date),
  CONSTRAINT SYS_RENTAL_RATING_CK CHECK (REGEXP_LIKE(rating,('[12345]'))));

The error says unique constraint (CPRG250.SYS_RENTAL_PK) violated

2 Answers

1
Kamil Gosciminski On Best Solutions

It seems like you are trying to add a duplicate rental event for the same film by the same customer on exact one day. That can obviously happen, if you allow in your business logic the situation that a customer can rent a movie, give it back on the same day and rent it back again.

Knowing your business, you have 2 ways to deal with this situation:

  1. Your business model don't allow that. This means that this is a duplicate record and you shouldn't add currently existing record, in which case showing that error is perfectly fine and doesn't allow for duplicates, since this event happened only once.

  2. Your business model allows that. In this case, you should modify your rental_date column to store time along with the date, instead of only storing date, so that you know when the rental event actually happen. You could use datetime type for example to store date with time. This can be done when creating your table, just replace rental_date date with rental_date datetime. If the table is already created you will need to drop and recreate PRIMARY KEY and then after that you could change type of your column using ALTER TABLE ddr_rental ALTER COLUMN rental_date datetime and re-create the primary key. Check values stored in your table after that, since 2019-01-01 will now be represented as 2019-01-01 00:00:00.000 appending the time which wasn't specified before.

In addition to (1) you could wrap your code and handle this exception to return a clear message when this happens, showing that the movie has already been rented.

Moreover, since you don't have a table for storing movies in your inventory, this can lead to a possible mistake, since you may have more than 1 copy of a movie. In this case I suggest that you create separate film and film_copy tables to properly identify which copy of a film has been rented, so that you can rent another copy.

1
Kristjan Kica On

You have a unique constraint in your table. Your table already has a record with customer_id, rental_date and film_title that you want to insert.

Try this query and you will see that there already is a record

select * from DDR_Rental
where customer_id=12345 and rental_date='12-Mar-19' and film_title='Peppermint'