Oracle - Preventing duplicates based on two columns

Question

Say that I have a table an Oracle 11g database that was defined like this

CREATE TABLE LAKES.DEPARTMENTAL_READINGS
(
  ID NUMBER     NOT NULL,
  DEPT_ID       INTEGER NOT NULL,
  READING_DATE  DATE NOT NULL,
  VALUE         NUMBER(22,1)
):

And the data in the table looks like this:

ID (PK)   DEPT_ID       CREATION_DATE         VALUE
-------------------------------------------------------------
1         101           10/12/2016            3.0
2         102           10/12/2016            2.5
3         103           10/12/2016            3.3
4         101           10/13/2016            3.4
5         102           10/13/2016            2.7
6         103           10/13/2016            4.0

As you can see, I have one entry for each date for each department ID. There should no more than one. We have merge statements handling our scripts for data imports so most of this is being prevented when data is pulled in. However, as there's no telling who may continue to write scripts for this application and we want to be as stringent as possible. Is there a way to set constraints to prevent duplicate data from being entered for each dept_id/creation_date combination?

Answer 1

You can create composite primary key on those 2 columns together. This will deny insert by throwing an error.