Results 1 to 4 of 4

Thread: constraints

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Smile Unanswered: constraints

    Hi I'm a little new to constraints and am just wonder if it possilbe and how to write a constraint that will not allow a record to be saved if att x is left null if att y is not left null. so if a person compelteds field say for his fore name it forces them to complete the fields for thier sur name also, but it ok to leave both blank.

    Thanks
    Asta

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    Hi,

    you can create a CHECK constraint on a table with any logical boolean expression that returns TRUE or FALSE based the logical operators, so you *could*:

    ALTER TABLE <table>
    ADD CONSTRAINT null_name_check CHECK (
    (column_a IS NOT NULL AND column_b IS NOT NULL) OR
    (column_a IS NULL AND column_b IS NULL)
    )

    ...now whether you *would* want to
    a) design your tables that way, or
    b) do this sort of validation in a constraint rather than in your application

    are rather more interesting questions....

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That would be a CHECK constraint. Have a look in Books Online, have a go, and post if you get stuck.
    Out of curiosity - if your data entry person has a "customer" called Mr Smith, you really want them to enter nothing?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    forcing both firstname and lastname to be non-NULL (and to have a length) will most assuredly piss off those people who legally have only one name

    but maybe this question isn't about names

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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