| Updating records in a Table [message #372732] |
Tue, 06 March 2001 10:03  |
Arpit
Messages: 99
Registered: March 2001
|
Member |
|
|
Hi,
I need some help in the SQL script. I'm tyring to update my "Contact" table which has
"POSTAL_ZIP_CODE column with postal code records having character 'O' instead of number '0' in it.
Example: MOR ON1 --->M0R 0N1
Can you please help me with the related SQL script to update the table. Any help on this asap would highly
be appreciated.
Thanks!
Alpesh
|
|
|
|
|
|
|
|
| Re: Updating records in a Table [message #372739 is a reply to message #372732] |
Tue, 06 March 2001 10:35  |
Arpit
Messages: 99
Registered: March 2001
|
Member |
|
|
Hi Andrew,
Thanks for your reply back.
Will the above constraint prevent the users from entering in char 'O' instead of number'0' in postal code field. Please confirm.
Thanks!
Alpesh
|
|
|
|
| Re: Updating records in a Table [message #372740 is a reply to message #372732] |
Tue, 06 March 2001 10:56 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Yes. Try to make sure that your App gives the user a meaningful error message. If you cant change the app, then naming the costraint like CANT_HAVE_O_IN_CODE could help if only the violated constraint will be displayed to the user.
You can expand the constraint like
(length(POSTAL_ZIP_CODES) = length(rtrim(ltrim(PSOTAL_ZIP_CODE)))
and POSTAL_ZIP_CODES = upper(POSTAL_ZIP_CODES)
and length(POSTAL_ZIP_CODES) >= 5) etc.
|
|
|
|
| Re: Updating records in a Table [message #372741 is a reply to message #372732] |
Tue, 06 March 2001 11:28 |
Arpit
Messages: 99
Registered: March 2001
|
Member |
|
|
Hi Andrew,
Thanks ! once again.
Last question--How can we find what all constraints are there on a table and is there any way we can define a message for the constraint ?
Thanks!
Alpesh
|
|
|
|
| Re: Updating records in a Table [message #372745 is a reply to message #372741] |
Tue, 06 March 2001 17:04 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
SELECT owner, constraint_name, table_name, search_condition, r_owner, r_constraint_name,
delete_rule, status,
DECODE (constraint_type,
'U', 'Unique Key',
'C', 'Check',
'P', 'Primary Key',
'R', 'Referential Integrity',
'V', 'Check Option on a view'
) constraint_type
FROM user_constraints
WHERE generated = 'USER NAME';
Note, this won't show constraints which have system generated names like SYS_nnnnn, there will be lots of them, especially not null constraints which you may not even be aware are being created.
As far as the message is concerned, no I dont think the constraint can give you a meaningful message othe than the name of the constraint.
You could try something like this in an combined Insert and Update trigger rather than a check constraint too...
if upper(new:POSTAL_ZIP_CODE) like '%O%' then
RAISE_APPLICATION_ERROR(-20501, 'ERROR: Postal Zip Code cant contain the letter O, check Postal Zip Code.', true);
end if;
This will give you the message you are after if errors are redirected to the user (they often are).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
