OraFAQ Forum: SQL & PL/SQL » Check Constraints

Home » SQL & PL/SQL » SQL & PL/SQL » Check Constraints

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Check Constraints [message #220137]

Mon, 19 February 2007 01:40

amit_nanote
Messages: 56
Registered: July 2005
Location: Indore

Member

Hi All,

I want to create a table on insert null(explicitly) should take a specified value, and dont want to use triggers.

like

Insert into tablename values (1,null);
null should be converted to 'TEST'

I am defining table following Implemented using check constraint.

create table aa1
(
n number(1),
name varchar2(10) check (name=decode(name, null, 'TEST'))
);
Table created

SQL> insert into aa1 values (1,null);

1 row created.

SQL> select * from aa1;

N NAME
---------- ----------
1
insert is also successful, but
select * from table; Wont converts the Name to TEST.
Tried nvl also on place of decode.

create table aa1
(
n number(1),
name varchar2(10) check (name=nvl(name, 'TEST'))
);

Table created.

SQL> insert into aa1 values (1,null);

1 row created.

SQL> select * from aa1;

N NAME
---------- ----------
1

Is there any other way without using trigger to do it.
Thank you

Report message to a moderator

Re: Check Constraints [message #220142 is a reply to message #220137]

Mon, 19 February 2007 01:52

Frank
Messages: 7901
Registered: March 2000

Senior Member

A check constraint is just that: a constraint that checks a certain condition, not change a value.
To change an explicitly issued value, there is no other way then using a trigger.
(being bold in this, risking being gazumphed again)
The thing that comes closest to what you want is to define a default value and NOT issue the value null.

Report message to a moderator

Re: Check Constraints [message #220143 is a reply to message #220142]

Mon, 19 February 2007 02:39

amit_nanote
Messages: 56
Registered: July 2005
Location: Indore

Member

Thanxs Frank Cool

,

Actually application front end is java hibernate...
And There is condition they have to include this kind of column for further usage. I have already set the value default but they were demanding to do without trigger and also they will provide null in the insert query for the column.

regards
Amit

[Updated on: Mon, 19 February 2007 02:40]

Report message to a moderator

Re: Check Constraints [message #220151 is a reply to message #220143]

Mon, 19 February 2007 03:45

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Out of curiosity, what difference does it make to the Java team HOW you do it, as long as their requirements are met?

Report message to a moderator

Re: Check Constraints [message #220157 is a reply to message #220151]

Mon, 19 February 2007 04:37

Frank
Messages: 7901
Registered: March 2000

Senior Member

I totally agree with JRowbottom.
Make a deal with the Java team: you do what you do best and they do what they do best. Both of you don't interfere with the other's business by restricting things you do not understand.

Report message to a moderator

Re: Check Constraints [message #220238 is a reply to message #220157]

Mon, 19 February 2007 15:14

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

@amit_nanote, would DEFAULT keyword help in what you are doing? Probably not, but ... who knows?

SQL> create table brisime
  2  (id    number,
  3   name  varchar2(20) DEFAULT 'Test'    --> note the DEFAULT keyword here
  4  );

Table created.

SQL> -- case A) such a case will be OK
SQL> insert into brisime (id) values (1);

1 row created.

SQL> -- case B) but this one will NOT be OK
SQL> insert into brisime (id, name) values (2, null);

1 row created.

SQL> select * From brisime;

        ID NAME
---------- --------------------
         1 Test                 --> case A) this is OK
         2                      --> case B) this is NOT OK

SQL>

Report message to a moderator

Re: Check Constraints [message #220272 is a reply to message #220142]

Tue, 20 February 2007 00:11

Frank
Messages: 7901
Registered: March 2000

Senior Member

Frank wrote on Mon, 19 February 2007 08:52

The thing that comes closest to what you want is to define a default value and NOT issue the value null.

Good idea Littlefoot! /forum/fa/1693/0/

Report message to a moderator

Re: Check Constraints [message #220287 is a reply to message #220272]

Tue, 20 February 2007 01:23

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm sorry, Frank. It has nothing to do with an idea, but 22:14 PM and being lazy to carefully read previously posted messages.

If I knew what this gazumphed thing was (can't find it in a dictionary), perhaps I am gazumphed lately.

Report message to a moderator

Re: Check Constraints [message #221138 is a reply to message #220137]

Sat, 24 February 2007 05:40

crisJedi
Messages: 1
Registered: November 2006
Location: Barcelona

Junior Member

what about trying this:
create or replace trigger aa1_tg
before insert on aa1
for each row
begin
if :new.name is null then
:new.name := 'Test';
end if;
end;
/
best regards,
cris..

Report message to a moderator

Re: Check Constraints [message #221143 is a reply to message #220137]

Sat, 24 February 2007 06:25

Frank
Messages: 7901
Registered: March 2000

Senior Member

amit_nanote wrote on Mon, 19 February 2007 08:40

Is there any other way without using trigger to do it.

crisJedi wrote:

what about trying this:
create or replace trigger aa1_tg
before insert on aa1

Report message to a moderator

Re: Check Constraints [message #221373 is a reply to message #220287]

Mon, 26 February 2007 08:27

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Littlefoot wrote on Tue, 20 February 2007 02:23

If I knew what this gazumphed thing was (can't find it in a dictionary), perhaps I am gazumphed lately.

That's because I think it is gazumped, although I think you have to look deeper into the definitions than the first few, unless you two had some bad business dealings Wink

Report message to a moderator

Re: Check Constraints [message #221384 is a reply to message #221373]

Mon, 26 February 2007 09:16

Frank
Messages: 7901
Registered: March 2000

Senior Member

I thought Ross meant meaning 1.2

Report message to a moderator

Previous Topic:	SQL Query
Next Topic:	Trying to get first & last values in one row

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 22:03:41 CST 2024