| problem in table creation [message #569028] |
Thu, 18 October 2012 15:29  |
 |
maan1789
Messages: 7
Registered: September 2012
|
Junior Member |
|
|
i want to create a table to perform a task when i insert client account details in this table then check the
account type is in(S,C,R) and
balance should be also check when account type S then balance >=5000, account type C then balance >=10000 and
account type R then balance >=5000
but when i run this query then facing error "ORA-02438: Column check constraint cannot reference other columns"
SQL> ed
Wrote file afiedt.buf
1 create table kcb_acc_tab
2 (
3 accno varchar2(20) constraint accno_pk primary key,
4 name varchar2(20) constraint name_nn not null
5 constraint name_chk check((substr(name,1,1) between 'A' and 'Z') and name=upper(name)),
6 acctype char constraint acctype_nn not null
7 constraint acctype_chk check(acctype in ('S','C','R')),
8 dot timestamp default sysdate,
9 bal number(7,2) constraint bal_chk check ((acctype='S' and bal>=5000)
10 or (acctype='C' and bal>=10000)
11 or (acctype='R' and bal>=5000))
12* )
SQL> /
)
*
ERROR at line 12:
ORA-02438: Column check constraint cannot reference other columns
|
|
|
|
|
|
|
|
| Re: problem in table creation [message #569032 is a reply to message #569031] |
Thu, 18 October 2012 16:02  |
John Watson
Messages: 8979
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The problem is absolutely clear:
ORA-02438: Column check constraint cannot reference other columns
To take a trivial example:orcl> create table t1(c1 number,c2 number constraint con1 check (c2 > c1));
create table t1(c1 number,c2 number constraint con1 check (c2 > c1))
*
ERROR at line 1:
ORA-02438: Column check constraint cannot reference other columns
orcl> create table t1(c1 number,c2 number constraint con1 check (c2 > 10));
Table created.
orcl>
--
Edit: sorry, hit the wrong key. You add the constraint later:
orcl> create table t1(c1 number,c2 number);
Table created.
orcl> alter table t1 add constraint con1 check (c2 > c1);
Table altered.
orcl> [Updated on: Thu, 18 October 2012 16:08] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
