Home » SQL & PL/SQL » SQL & PL/SQL » A flexible Constraint (11g)
A flexible Constraint [message #447413] Mon, 15 March 2010 03:03 Go to next message
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 #447414 is a reply to message #447413] Mon, 15 March 2010 03:09 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
you have to apply constraint separately for each column
Re: A flexible Constraint [message #447416 is a reply to message #447414] Mon, 15 March 2010 03:17 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
No jointly, single line constraint
Re: A flexible Constraint [message #447419 is a reply to message #447413] Mon, 15 March 2010 03:33 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
malhi wrote on Mon, 15 March 2010 09:03
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 .....

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 #447422 is a reply to message #447419] Mon, 15 March 2010 04:37 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Constraint are Constraints...They will play what they are design for.. Are u sure u want it on your DB only...because what i think u cn handle it on any of your GUI.
Re: A flexible Constraint [message #447430 is a reply to message #447413] Mon, 15 March 2010 05:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #447439 is a reply to message #447435] Mon, 15 March 2010 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you determine it is an old row?

Regards
Michel
Re: A flexible Constraint [message #447440 is a reply to message #447435] Mon, 15 March 2010 05:37 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Why Not Update both of "Nic's" with some name which u cn identify later easily, if its not Unique too and then apply that constraint.
Re: A flexible Constraint [message #447442 is a reply to message #447440] Mon, 15 March 2010 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@javed.khan

Please stop using IM speak, write whole words.

Regards
Michel
Re: A flexible Constraint [message #447444 is a reply to message #447430] Mon, 15 March 2010 05:43 Go to previous messageGo to next message
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 ...
Re: A flexible Constraint [message #447446 is a reply to message #447442] Mon, 15 March 2010 05:45 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
I do not understand what to want to say.
Re: A flexible Constraint [message #447448 is a reply to message #447432] Mon, 15 March 2010 05:49 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
At the present, we want to implement it on DB level instead of using GUI
Re: A flexible Constraint [message #447449 is a reply to message #447446] Mon, 15 March 2010 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please answer to my question:

Michel Cadot wrote on Mon, 15 March 2010 11:37
How do you determine it is an old row?


Without this answer no one can give you a solution.

Regards
Michel

Re: A flexible Constraint [message #447457 is a reply to message #447449] Mon, 15 March 2010 06:20 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
we have a column created_date through this we can identify old and new ones
Re: A flexible Constraint [message #447484 is a reply to message #447457] Mon, 15 March 2010 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the answer has been given, use this column in the constraint definition.

Regards
Michel
Re: A flexible Constraint [message #447554 is a reply to message #447430] Tue, 16 March 2010 00:41 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Thanks......my problem resolved.
Re: A flexible Constraint [message #447779 is a reply to message #447413] Wed, 17 March 2010 16:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am not certain this is a proper solution. Indeed, I do not believe there is a "declarative" solution for this problem.

You must be relying on either a trigger or constraint or application to set the created_date column in which case the solution has holes.

However, the holes may be acceptable depending upon your practices. For example, since I like instead-of-triggers, I would accept the corresponding solution to this problem.

In short I cannot think of a solution that uses only declarative logic, that would enforce such a rule. It would be nice though to see such a thing. It might have other applications.

Kevin
Re: A flexible Constraint [message #447780 is a reply to message #447779] Wed, 17 March 2010 16:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about the one posted by JRowbottom (changing the columns to OP ones)?

check (created_date < to_date('01-01-2000','dd-mm-yyyy') or (old_nic_no is not null or new_nic_no is not null));


Regards
Michel
Re: A flexible Constraint [message #447781 is a reply to message #447413] Wed, 17 March 2010 16:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am thinking more in terms of a "generalized" approach to the problem. The basic question them becoming, "how does a constraint know if it is operating on an existing row or on a new row?". The answer being: it does not. Additionally my puney mind was unable to craft a solution that would allow the constraint to know. For example, DEFAULT values for a hidden column does not help because constraints are applied after these values are set. Thus it appears there is no constraint oriented solution (e.g. no declarative only solution) that satisfies the general problem: If my row already exists do not enforce the constraint but if the row is new then enforce it. After all, the check constraint that tests the date assumes the application (or other parts of the database) is not cheating by supplying an incorect value for CREATED_DATE?

Maybe I am reading too fast and missing something.

A review of ASKTOMHOME yields a thread with the same question to whit Tom replies (use a trigger).

Kevin
Re: A flexible Constraint [message #447801 is a reply to message #447781] Thu, 18 March 2010 00:24 Go to previous message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
I am thinking about using Compound Trigger for this, as through this we can correctly determine insertions.
Previous Topic: Database Trigger
Next Topic: Removing Duplicate Rows when condition is matched
Goto Forum:
  


Current Time: Sun Dec 04 10:32:18 CST 2016

Total time taken to generate the page: 0.06064 seconds