CODEWITHSUNDEEP
oracle-databaseduplicates
Mistu4u
Mistu4u

Reputation: 5416

How can I make sure no duplicate row is inserted

I have a table in Oracle:

NAME     DATE      TIMEOFDAY    PAYMENT
SUBIR   10-08-12      DAY        10000 
SUBIR   10-08-12      NIGHT      12000

Now I want to make modification in the oracle table so that a single user cannot pay more than once in a particular day. For example, I cannot insert like:

NAME     DATE      TIMEOFDAY    PAYMENT
SUBIR   10-08-12      DAY        10000 
SUBIR   10-08-12      NIGHT      12000
SUBIR   10-08-12      DAY        14000

The 3rd row should not be inserted after the previous two records as there is already a payment that has been made in day on 10-08-12. So how can I make this function in the oracle table?

EDIT- I guess I have to make date and timeofday compositely unique. Is it possible? The reason behind my such thinking is if I make both of them unique separately, users will not be allowed to enter a row with the same date for the second time but with different timeofday.

Upvotes: 1

Views: 1429

Answers (2)

Faruk AZAKLI
Faruk AZAKLI

Reputation: 101

  1. If it's primary key:

alter table YOUR_TABLE add constraint YOUR_TABLE_PK primary key (DATE, TIMEOFADAY);

  1. If you need only uniqueness

create unique index YOUR_TABLE_IX1 on YOUR_TABLE_TAB (DATE, TIMEOFADAY);

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Add a UNIQUE constraint on (DATE, TIMEOFDAY):

ALTER TABLE tableX
  ADD CONSTRAINT unique_date_timeofday
    UNIQUE (DATE, TIMEOFDAY) ;

This constraint allows only unique combinations of the two fields.

Upvotes: 3

Related Questions