Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    19

    Unanswered: Duplicate key problem

    Hi there,

    After some help from people in here, I got a specific table created yesterday that I needed. Now, however, I have a problem since I'm unable to index the date column of this new table due to some duplicate key error. I am unable to find any duplicate data, so I'm wondering what may be wrong.

    The code for creating the table is the following:

    ---------------------------------------------

    Select a.*, b.obstime as PrevObsTime, log(a.SPCLOSE)-log(b.SPCLOSE) as SPreturn
    into ReturnSP
    from SP a inner join SP b
    on a.SPDATE = b.SPDATE
    where b.obstime=(select max(obstime) from SP where obstime<a.obstime and SPDATE = a.SPDATE)
    order by SPDATE asc


    ---------------------------------------------

    The "SPCLOSE" is a price-observation and "SPDATE" is a date-observation, while "obstime" is the time of the day.

    While the base-table, which I draw the above data from, has a PK on both the date and time-of-day columns, I'm unable to add this to the new table. I get the following error message when I try:

    'ReturnSP' table
    - Unable to create index 'PK_ReturnSP'.
    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ReturnSP' and the index name 'PK_ReturnSP'. The duplicate key value is (Jan 3 1983 12:00AM).
    Could not create constraint. See previous errors.
    The statement has been terminated.


    I'm not sure if anyone knows what to do here, but if you have encountered this before and have a suggestion, I'd be grateful!

    Thanks in advance!

    Best regards,

    Martin Falch

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What do you get for this query:

    Code:
    select * from ReturnSP where SPDATE = 'Jan 3 1983 12:00AM'

  3. #3
    Join Date
    Jul 2009
    Posts
    19
    If I do so, I get 59 rows returned, which correspond to all the intraday returns on that day. So apparently that part "works" in the sense that it doesn't return any duplicates or similar issues. I've checked that there are no overlapping obstime-observations in the returned rows either.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What is the full definition of the primary key you are trying to create, if it is not just on SPDATE?

  5. #5
    Join Date
    Jul 2009
    Posts
    19
    Well since I'm pretty new to SQL, I'm using the simple "point-and-click", right clicking the table and using /design to go and manually set a PK on both SPDATE and obstime. However, regardless of whether I set it on just one of them or both, the same type of problem occurs.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Try this, then:
    Code:
    select count(*), SPDate, obstime
    from ReturnSP
    group by SPDate, obstime
    having count(*) > 1
    if that returns no results, then there may be a problem with the tool. This should create the primary key as you want it:
    Code:
    alter table ReturnSP add constraint PK_ReturnSP primary key (SPDate, obstime)
    Last edited by MCrowley; 05-13-10 at 16:00. Reason: Fix typo in code tag

  7. #7
    Join Date
    Jul 2009
    Posts
    19
    Thanks alot I'll try that out!

  8. #8
    Join Date
    Jul 2009
    Posts
    19
    Seems like it worked, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •