Results 1 to 12 of 12

Thread: Unique Indexes?

  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: Unique Indexes?

    Heres my problem.

    I have a news database, where the scenario is that:

    One Author may write many articles over time, however One Author may only write one article per day.

    So to add a constraint on the user only writing one article per day, i need to put 2 indexes on unique, however they have to be connected.

    I have both fields on unique however setting them to both unique on the Author and Date, doesn't work on constraining the author on entering only one article per day.

    Please Help...

    If you dont understand the question please ask me to re-phrase it.. i'll try to do bette

  2. #2
    Join Date
    Jan 2008
    Posts
    16
    you have a unique constraint on the combination of author and Date. Right?

    Is the Date field datetime or only date?

  3. #3
    Join Date
    May 2009
    Posts
    8
    Date/Time - Format: General Date

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how are you setting the value in the date column
    bear in mind that JET uses the date datatype to store a time AND a date.

    if you set the date value to now() that will store a date and time element so unless the date and time elements match you will get what seems like duplicate records to you but the time will be different to the computer
    if you use date() that will store a date and time element (but the time element will be 00:00:00)

    if you choose to use now() then your index should be on the integer component of the date (thats the way how JET works out what day it is, the decimal part is the time (as a proportion of one day)).

    so ultimately its down to you, as to how you have designed your schema and/or how you populate your data

    you coudl try to see if you could specify the integer part of the date in your index definition by defining the index as, say

    author & clng(mydatecolumn)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What you are looking for is called a "composite index". You specify that the combination of column values must be unique. I think you know this - you just need to know how to do it.
    In table design, open up the Indexes dialog
    Enter an index name, then enter each column you want to be part of the index. Click on the index name and set the properties (PK, Unique, Nulls). Sounds like a PK to me. The order of the columns is important. I would expect that it should be ordered (author, date) - this would be the most conventional order, unless for example most of your queries filter on the date.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mark - isn't it just easier to pop a check constraint on the date column to ensure there is no time portion?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2009
    Posts
    8
    I've attached the database to this post. It is safe, you can run a virus scan if you want.

    I already have a primary key in the table that its all located in.

    I've tried doing this however it only allows one article per day no matter who the author is. What i need is one author per article and one article per day from an author. However this must allow another author to post an article that day.

    Sorry if i make no sense what so ever, i'm really confused.
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to set your unique constraint on a composite index made up of author AND dateofarticle.

    settign a unique constraint on date of article will exhibit the symptoms you state, ie you can only have one date in dateofarticle.
    AND if you made the author index unique on top of that then you will only allow one row per author

    its not part of the row definition, its part of the index definition (IIRC look for the lightning flash in the table designer
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Mark - isn't it just easier to pop a check constraint on the date column to ensure there is no time portion?
    does the constraint cut it in Access/JET.
    you could probably limit the date value using an input mask, but my guess would be that this problem has arisen after forms and reports ave been designed...

    I can also see a requirement to 'know' when an article was filed.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by healdem
    so you need to set your unique constraint on a composite index made up of author AND dateofarticle.

    settign a unique constraint on date of article will exhibit the symptoms you state
    Yes that is correct. So how can i actually perform this action?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    read post #5
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    does the constraint cut it in Access/JET.
    Yeah:
    Code:
    =CLng([dateTest])
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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