 How? - Create Check constraint - field must contain @ symbol [message #610781] |
Mon, 24 March 2014 05:08  |
 |
Oracleuser14
Messages: 1
Registered: March 2014
Location: Antarctica
|
Junior Member |
|
|
I need to create a check constraint for an email column/field, where the field must contain an "@" symbol.
Does sql (oracle or SQL in general) let you do this or do I need to use a different programming language.
If it SQl does let you do this, am I on the right track?
1. Tried myself:
ALTER TABLE Q_Customer
ADD CONSTRAINT Q_chk_Cus_email CHECK (Cus_email LIKE '%@%');
...but
ORA-02293: cannot validate (C3267304.Q_CHK_CUS_EMAIL) - check constraint violated
2. Tried from a forum: alter table Q_CUSTOMER
add constraint Q_Chk_cus_email check (Cus_email like '%_@__%.__%')
...but
ORA-02293: cannot validate (C3267304.Q_CHK_CUS_EMAIL) - check constraint violated
I'm using Apex 4.0. I need an answer its for my final year project at university - which is due in this week  .
[Updated on: Mon, 24 March 2014 05:09] Report message to a moderator |
|
|
|
| Re: How? - Create Check constraint - field must contain @ symbol [message #610783 is a reply to message #610781] |
Mon, 24 March 2014 05:22  |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
If ALTER TABLE failed, it means that there are some records in that table that violate the constraint. If that's OK, use NOVALIDATE option, such asALTER TABLE Q_Customer
ADD CONSTRAINT Q_chk_Cus_email CHECK (Cus_email LIKE '%@%') NOVALIDATE;
Otherwise, fix those errors first and then create the constraint.
However, the way you are checking e-mails isn't a perfect one; the first one is satisfied with @littlefoot as an e-mail address (which is wrong). The following constraint is most probably not the perfect one either, but is much better than the ones you use now. Give it a try:ALTER TABLE q_customer ADD CONSTRAINT chk_cus_email
CHECK (REGEXP_SUBSTR (
cus_email,
'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')
IS NOT NULL); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
