| A flexible Constraint [message #447413] |
Mon, 15 March 2010 03:03  |
malhi
Messages: 96
Registered: December 2009
Location: Karachi
|
Member |
|
|
I have applied following constraint on a table, it is working fine,
alter table t add constraint nic_cnic_chk check (old_nic_no is not null or new_nic_no is not null) enable novalidate;
i want to know is it possible that this constraint only work at the time of new insertions, but when user update any old records this constraint remain silent .....
|
|
|
|
|
|
|
|
| Re: A flexible Constraint [message #447419 is a reply to message #447413] |
Mon, 15 March 2010 03:33  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
malhi wrote on Mon, 15 March 2010 09:03i want to know is it possible that this constraint only work at the time of new insertions, but when user update any old records this constraint remain silent .....
No, use BEFORE INSERT trigger instead, which raises an exception when both column values are NULL.
But, as your requirements are quite uncommon, rather re-think them. Do you really want to restrict INSERT, although the (immediately) following UPDATE may set both column values to NULL anyway?
|
|
|
|
|
|
| Re: A flexible Constraint [message #447430 is a reply to message #447413] |
Mon, 15 March 2010 05:04 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
IF you have a way of identifying the old records then you can get this to work by specifying a constraint like this:
create table test_145
(date_Created date
, id_1 number
, id_2 number);
alter table test_145 add constraint test_145_ck1
check (date_created < to_date('01-01-2000','dd-mm-yyyy') or (id_1 is not null or id_2 is not null));
[Updated on: Mon, 15 March 2010 05:35] by Moderator Report message to a moderator |
|
|
|
| Re: A flexible Constraint [message #447432 is a reply to message #447422] |
Mon, 15 March 2010 05:06 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:Are u sure u want it on your DB only...because what i think u cn handle it on any of your GUI.
It's generally considered that constraints are better on the table - that way you can guarantee that the data in the table conforms to the constraints applied. If you leave it up to the GUI to enforce the constraints then you have to trust that every place that updates the table enforces the same rules.
|
|
|
|
| Re: A flexible Constraint [message #447435 is a reply to message #447419] |
Mon, 15 March 2010 05:18 |
malhi
Messages: 96
Registered: December 2009
Location: Karachi
|
Member |
|
|
Our requirement is that if either old_nic_no or new_nic_no is filled in any new insertion, it is correct, Both should not be null.
But in our old data there are some records where both are null, problem arise when an amendment is being made in these records e.g changing name or any other column's value, then this constraint come in way, asking user to give old_nic_no or new_nic_no, which user do not have.
I hope the problem is now clear.....
|
|
|
|
|
|
|
|
|
|
| Re: A flexible Constraint [message #447444 is a reply to message #447430] |
Mon, 15 March 2010 05:43 |
malhi
Messages: 96
Registered: December 2009
Location: Karachi
|
Member |
|
|
Tell me if i am correct, after applying constraint mentioned by you, it will do like this
if create_date < specified date then constraint will not be applicable ...
if create_date > specified date then constraint will be applicable ...
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
