0

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?

SausageBuscuit
  • 1,226
  • 2
  • 20
  • 34
  • 3
    Yes - set a unique constraint on `(dept_id, creation_date)`. However - question: What is the data type of `creation_date`? It should be `DATE`. In that case, it should also have a constraint to make sure the time component is 00:00:00 so it *behaves* like a pure date. A constraint like `creation_date = trunc(creation_date)`. –  Dec 22 '16 at 16:12
  • 1
    I added the table definition, it is a date. That looks promising...sometimes my Googling wordage is off just enough for me to not find it. I will give the UNIQUE constraint a try. Thanks. – SausageBuscuit Dec 22 '16 at 16:24
  • 1
    Make sure you add the constraint on the date column as well; otherwise one **could** work around your unique constraint on two columns, by inserting a date with the time component of, say, `09:30:00`. –  Dec 22 '16 at 16:45

1 Answers1

0

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

Srihari Karanth
  • 2,067
  • 2
  • 24
  • 34