Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Location
    Tucson, AZ
    Posts
    9

    Unanswered: how to ensure unique value over multiple columns

    I'm looking for a way to ensure a value of a column in an insert or update is unique over multiple columns. For example, in this table
    accounts
    id varchar(32)
    readkey varchar(16)
    writekey varchar(16)
    I want to ensure that when a row of accounts is inserted or updated that the union of all readkey values and writekey values contains no duplicates.

    I know some "hard" ways to do this (like a second table of all keys, or a trigger that tests new readkey values against all readkey and writekey values, and likewise new writekey values against all readkey and writekey values, and so on). But I'm betting that savvy SQL folks know a better way. In case it matters, I'm using IBM DB2 8.1.

    Thanks
    Bill

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create a composite key with unique attribute.
    not sure if it will work in DB2 though
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2004
    Location
    Tucson, AZ
    Posts
    9
    Thanks but that doesn't do it. I'm not trying to ensure that no combination of readkey || writekey ever occurs twice. I need to ensure that no readkey is the same as any other readkey or writekey, and no other writekey is the same as any other readkey or writekey.

    Bill

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know DB2. In standard SQL you can create a constraint something like:

    ALTER TABLE accounts a1
    ADD CONSTRAINT c1
    CHECK (NOT EXISTS (SELECT NULL FROM accounts a2 WHERE a2.readkey = a1.writekey));

    That, along with UNIQUE constraints on the 2 columns, would do it.

    Alternatively, perhaps a Materialized View based on:

    SELECT 'R' AS mode, readkey AS key FROM accounts
    UNION
    SELECT 'W' AS mode, writekey AS key FROM accounts

    ... with a unique constraint on (key).

  5. #5
    Join Date
    Oct 2004
    Location
    Tucson, AZ
    Posts
    9
    Thanks for the suggestions. I couldn't make either work, but the ideas in them provided a way.

    The CHECK constraint was my first idea, but I learned that CHECK constraints cannot depend on values from more than one row, so the SELECT which examines the whole table will not work.

    The materialized view was a good idea but doesn't work, since materialized views do not permit UNION - the query has to be a subset query.

    What I did get to work was to create a (regular) VIEW using the UNION roughly as you suggested, then create triggers for insert and update that throw an error if the key already exists in the union view. The view and two triggers is more complex than I hoped, but at this point I'm happy just to have a solution.

    Thanks again for the help.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    In DB2 v8 you can use a sequence object for this purpose.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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