Home » SQL & PL/SQL » SQL & PL/SQL » regarding check constraints (oracle 9.0.2.0 windows xp)
regarding check constraints [message #329630] Thu, 26 June 2008 02:04 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hello friends .

Am creating new table with column user_id .but this
user_id column must have at least 5 characters and should not
be more than 12 in size.


create table reg(stud_name varchar2(10),user_id varchar2(10) NOT NULL ,
pass_word varchar2(10),dob date,email_id varchar2(10),phone varchar2(10),
constraint uni_cols UNIQUE (user_id,email_id))


shall i achieve this with check constraint???. .
thanks in advance.
Re: regarding check constraints [message #329641 is a reply to message #329630] Thu, 26 June 2008 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If user_id is varchar2(10) it surely will be not more than 12 characters; I even bet it will never have more than 10. Wink

You can add a check constraint that check length(user_id).

Regards
Michel

(Edit: fix typo]

[Updated on: Thu, 26 June 2008 02:40]

Report message to a moderator

Re: regarding check constraints [message #329647 is a reply to message #329630] Thu, 26 June 2008 02:34 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hello micheal,
Quote:

You can add a check constraint than check length(user_id).


how do i have to check both sizes (5 to 12) characters ..
can u written the code pls. .
Re: regarding check constraints [message #329650 is a reply to message #329647] Thu, 26 June 2008 02:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Think.
Reread Michel's comment and think.
How would you make sure the max length is 12?
Re: regarding check constraints [message #329677 is a reply to message #329630] Thu, 26 June 2008 03:47 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

got it.
constraint check_user_id check(length(user_id) between 5 and 12)
..
Re: regarding check constraints [message #329678 is a reply to message #329677] Thu, 26 June 2008 03:53 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But, if you chose to create a table with
user_id varchar2(10) NOT NULL
, why do you check
check(length(user_id) between 5 and 12)
Compare: varchar2(10), length 12.

OK, let's say it was a typo. But there's still no need to check whether column length is "between 5 and 12"; "length >= 5" is sufficient as you can't enter a string of 13 or more characters into a 12-character column.

So, perhaps you might use
constraint check_user_id check(length(user_id) >= 5)
Previous Topic: SQL request without PL/SQL
Next Topic: regarding update statement
Goto Forum:
  


Current Time: Fri Dec 02 21:04:20 CST 2016

Total time taken to generate the page: 0.25253 seconds