Home » SQL & PL/SQL » SQL & PL/SQL » Check Constraints
Check Constraints [message #220137] Mon, 19 February 2007 01:40 Go to next message
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
Re: Check Constraints [message #220142 is a reply to message #220137] Mon, 19 February 2007 01:52 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Check Constraints [message #220143 is a reply to message #220142] Mon, 19 February 2007 02:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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?
Re: Check Constraints [message #220157 is a reply to message #220151] Mon, 19 February 2007 04:37 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Check Constraints [message #220238 is a reply to message #220157] Mon, 19 February 2007 15:14 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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>
Re: Check Constraints [message #220272 is a reply to message #220142] Tue, 20 February 2007 00:11 Go to previous messageGo to next message
Frank
Messages: 7880
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! ./fa/1693/0/
Re: Check Constraints [message #220287 is a reply to message #220272] Tue, 20 February 2007 01:23 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.
Re: Check Constraints [message #221138 is a reply to message #220137] Sat, 24 February 2007 05:40 Go to previous messageGo to next message
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..
Re: Check Constraints [message #221143 is a reply to message #220137] Sat, 24 February 2007 06:25 Go to previous messageGo to next message
Frank
Messages: 7880
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

Re: Check Constraints [message #221373 is a reply to message #220287] Mon, 26 February 2007 08:27 Go to previous messageGo to next message
joy_division
Messages: 4640
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
Re: Check Constraints [message #221384 is a reply to message #221373] Mon, 26 February 2007 09:16 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I thought Ross meant meaning 1.2
Previous Topic: SQL Query
Next Topic: Trying to get first & last values in one row
Goto Forum:
  


Current Time: Sat Dec 03 10:03:29 CST 2016

Total time taken to generate the page: 0.07943 seconds