| how do you set a fixed character length [message #372417] |
Fri, 09 February 2001 14:28  |
Zufar
Messages: 2
Registered: February 2001
|
Junior Member |
|
|
I want to set it so that the user has to type 3 characters for deptcode, unable to type in 1 or 2 characters for deptcode. Any help would be appreciated.
create table dept (
deptcode CHAR(3) NOT NULL,
deptname VARCHAR2(20)
);
|
|
|
|
| Re: how do you set a fixed character length [message #372418 is a reply to message #372417] |
Fri, 09 February 2001 18:26  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Creating check constraints can be very useful. If it's a data rule, enforce it on the table.
ALTER TABLE dept ADD CONSTRAINT
deptcode_len3 CHECK (length(RTRIM(LTRIM(dept_code)))= 3) ;
You can have all kinds of checks, but can't run queries etc as part of the check.
process_date = TRUNC(process_date) ;
run_type IN (null, 'X', 'Y', 'Z') ;
sal between 100 and 200;
Notes:
I'd probably make deptno varchar2(3) rather than char(3). char is for compatibility "only" varchar2(1) happens to be the same as char(1), but still rather user varchar2.
No 7.x you could take advantage of check constraints having a check like "deptno is not null". The advantage is that when the check fails, Oracle reports which field is causing the problem, rather than just:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
On Ora 8.x it is sorted out:
ORA-01400: cannot insert NULL into ("SCOTT"."ABC"."B")
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
