DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: add a CAPS constraint

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    add a CAPS constraint

    Hi Friends,

    How do I put contraints on my NAME column on EMP table so that all
    the data saved are all CAPS?

    Is this the correct syntax?

    allter table EMP modify(NAME check upper(NAME));
    or
    allter table EMP modify(NAME default upper(trim(NAME)));


    Thanks
    Last edited by jennifer2007; 02-19-2007 at 11:40 PM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    user a before insert trigger

  3. #3
    Join Date
    Feb 2007
    Posts
    212
    thanks dear

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Quote Originally Posted by jennifer2007
    allter table EMP modify(NAME check upper(NAME));
    The syntax you were looking for would be:

    ALTER TABLE EMP
    add CONSTRAINT constraint_name CHECK (NAME = upper(NAME));
    Assistance is Futile...

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    readin it again, you either wan tot change the data so it is in caps or ensure the data being presented is in cap, which is it

  6. #6
    Join Date
    Feb 2007
    Posts
    212
    i want to change the data in to caps...I thot i can handle it using constraint
    like allowing dafault values "sysdate"....
    So I really have to code a trigger? :( what if it have lots of varchar2
    columns in which i wanna automate entry to caps....

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    if you want to change the data, then yes you need a before update trigger, you also want the constraint to ensure that it works and nothing sneaks through

  8. #8
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    If you have a frontend for insertion,better use it to enforce such a rule instead of using triggers.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  9. #9
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    what happen when tables were already populated with unCAPs NAME?
    ---------------

  10. #10
    Join Date
    Feb 2007
    Posts
    212
    Thanks hunnies How do u audit if a table with unCaps letter in it?

    My front-end form data-entry is HTMLDB(APEX). Would you know how to
    enforce all CAPS in the field entry?


    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
  •  


Click Here to Expand Forum to Full Width