Results 1 to 9 of 9
  1. #1
    Join Date
    May 2007
    Posts
    24

    Unanswered: How would I solve this using a check?

    Lets say I have two columns A and B.

    Two rows of can't have the exact same value for A and B

    Example

    A - type String
    B - type String

    A = Cat; B = one;
    A = Dog; B = one;
    Ok

    A = Cat; B = one;
    A = Cat; B = two;
    Ok

    A = Cat; B = one;
    A = Cat; B = one;
    Bad

    How would I prevent a new row to be inserted with a constraint check?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    CREATE TABLE test 
    (
      somecol integer, 
      a varchar(20), 
      b varchar(20),
      unique (a,b)
    )
    Although I would simply create a unique index on the two columns (which is technically the same, but I like it better)
    Code:
    CREATE TABLE test 
    (
      somecol integer, 
      a varchar(20), 
      b varchar(20)
    );
    
    CREATE UNIQUE INDEX test_unique_ab on TEST (a,b);
    )

  3. #3
    Join Date
    May 2007
    Posts
    24
    thanks Ill try it out

  4. #4
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    Be careful with that. I am not sure what you intend to do but null values will allow weird results there.

    A = Cat, B = NULL
    A = Cat, B = NULL

    can exist or vice versa.

    some not null restraints would be good if they always must have value.

    Here is a good reference page: http://www.postgresql.org/docs/8.1/s...nstraints.html

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by amthomas
    Be careful with that. I am not sure what you intend to do but null values will allow weird results there.

    A = Cat, B = NULL
    A = Cat, B = NULL

    can exist or vice versa.
    Good to know.
    I always thought that PG behaved like Oracle regarding this, where the above combination is not possible with an unique index on (A,B)

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by shammat
    Good to know.
    I always thought that PG behaved like Oracle regarding this, where the above combination is not possible with an unique index on (A,B)
    No. You need to explicitly add 'not null' constraints - pg doesn't make that assumption.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by loquin
    No. You need to explicitly add 'not null' constraints - pg doesn't make that assumption.
    I did understand that, and the manual clearly states that this complies with the ANSI standard.
    But still: for me (A, null) is the same as (A, null)
    Although I have to agree that a unique index over nullable columns probably doesn't make that much sense.

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Well, since a null value is defined as an unknown value, you cannot make the assumption that one unknown is the same as another unknown... After all, if the field containing the null is a real number, you would have an essentially infinite number of possible values. For the 20 character varchar field that CrazyTN detailed, you're looking 10^48 possible values...
    Last edited by loquin; 08-14-07 at 17:19.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by loquin
    Well, since a null value is defined as an unknown value, you cannot make the assumption that one unknown is the same as another unknown... After all, if the field containing the null is a real number, you would have an essentially infinite number of possible values. For the 20 character varchar field that CrazyTN detailed, you're looking 10^48 possible values...
    I do understand the reasoning and the logic around NULL values, but still I prefer Oracle's logic
    But then I do like Oracle's non-standard way of treating zero-length strings as NULL as well, but that should not be discussed again

Posting Permissions

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