Home » SQL & PL/SQL » SQL & PL/SQL » Constraint restricting hierarchical use of table
Constraint restricting hierarchical use of table [message #417555] Mon, 10 August 2009 04:27 Go to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
I have this table where I want to store relationships between an old and a new article. The old article is to be fased out from storage and be replaced by the new article.

The table:
OLD_ARTICLE_NO   NUMBER   NOT NULL
NEW_ARTICLE_NO   NUMBER   NOT NULL

I have a primary key on the OLD_ARTICLE_NO and a unique index on the NEW_ARTICLE_NO column. This way no old article can be replaced by two or more different new articles and no new article can replace two or more different old articles. I also have a check constraint to make sure that no old article can be the same as a new article. Both columns have a foreign key constraint to the ARTICLES table where all articles are specified.

What i also would like is some kind of check constraint to make sure that no article specified in the column NEW_ARTICLE_NO is also specified in the column OLD_ARTICLE_NO on another row in the table. It is up to the users to specify which article is old and which is new and I just want to make sure that they can't do this wrong.

As an example this should not be possible:
ROW# OLD_ARTICLE_NO   NEW_ARTICLE_NO
1    11111            22222
2    22222            33333

It seems that this should (maybe) be easy to do and I'm just confused at the moment, I don't know. All input appreciated!
Re: Constraint restricting hierarchical use of table [message #417558 is a reply to message #417555] Mon, 10 August 2009 04:48 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You can use Materialized View to achieve this.


SQL> create table test (old_article_no int primary key, new_article_no int unique);

Table created.

SQL> alter table test add constraint chk_old_not_equal_new check (old_article_no <> new_article_no);

Table altered.

SQL>  create materialized view test_mv
  2   build immediate
  3   refresh force on commit
  4   as
  5   select count(*) x from test a, test b
  6   where a.new_article_no = b.old_article_no
  7  /

Materialized view created.


SQL> alter materialized view test_mv add constraint chk_x_is_zero check (x=0);

Materialized view altered.

SQL> insert into test values (1111,2222);

1 row created.

SQL> insert into test values (2222,1111);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (HR.CHK_X_IS_ZERO) violated


SQL> select * from test;

no rows selected

SQL> select * from test_mv;

no rows selected

SQL> insert into test values (2222,1111);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_mv;

         X
----------
         0

1 row selected.

SQL> select * from test;

OLD_ARTICLE_NO NEW_ARTICLE_NO
-------------- --------------
          2222           1111

1 row selected.

SQL>

Re: Constraint restricting hierarchical use of table [message #417567 is a reply to message #417558] Mon, 10 August 2009 05:29 Go to previous messageGo to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
bonker wrote on Mon, 10 August 2009 11:48
You can use Materialized View to achieve this.



I tried your script but got stuck when adding the constraint chk_x_is_zero on the materialized view. I got this error message:

ORA-02438: Column check constraint cannot reference other columns

I tried searching for the error but found nothing useful. What Oracle version did you try this on? I'm on 9.2.0.8.0.
Re: Constraint restricting hierarchical use of table [message #417570 is a reply to message #417567] Mon, 10 August 2009 05:36 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
mine is 10g Express edition


SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

SQL>



If you can copy paste your SQL*PLUS session then we can see what error you get.
Re: Constraint restricting hierarchical use of table [message #417571 is a reply to message #417558] Mon, 10 August 2009 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can use Materialized View to achieve this.

But until you commit you don't know that you are working with incorrect data and it fits the requirements only for other sessions.

Regards
Michel

[Updated on: Mon, 10 August 2009 05:44]

Report message to a moderator

Re: Constraint restricting hierarchical use of table [message #417579 is a reply to message #417570] Mon, 10 August 2009 05:52 Go to previous messageGo to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
bonker wrote on Mon, 10 August 2009 12:36
If you can copy paste your SQL*PLUS session then we can see what error you get.


This is the output I got in SQL*PLUS:

SQL> create table test (old_article_no int primary key, new_article_no int unique);

Table created.

SQL> 
SQL> alter table test add constraint chk_old_not_equal_new check (old_article_no <> new_article_no);


Table altered.

SQL> 
SQL> create materialized view test_mv
  2   build immediate
  3   refresh force on commit
  4   as
  5   select count(*) x from test a, test b
  6   where a.new_article_no = b.old_article_no;

Materialized view created.

SQL> 
SQL> alter materialized view test_mv add constraint chk_x_is_zero check (x=0);
alter materialized view test_mv add constraint chk_x_is_zero check (x=0)
                                                                      *
ERROR at line 1:
ORA-02438: Column check constraint cannot reference other columns


SQL> 

and...

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> 

[Updated on: Mon, 10 August 2009 05:54]

Report message to a moderator

Re: Constraint restricting hierarchical use of table [message #417581 is a reply to message #417571] Mon, 10 August 2009 05:58 Go to previous messageGo to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
Michel Cadot wrote on Mon, 10 August 2009 12:42
Quote:
You can use Materialized View to achieve this.

But until you commit you don't know that you are working with incorrect data and it fits the requirements only for other sessions.


I can accept this as I don't think the users will get this wrong very often. This is just an extra precaution.
Re: Constraint restricting hierarchical use of table [message #417583 is a reply to message #417555] Mon, 10 August 2009 06:07 Go to previous messageGo to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
I forgot to write this in my original post, but I had a thought about using the Connect By statement/clause and somehow use the Levels to decide if there's more than one level.

Something like this maybe:
SQL> Insert into test values (11111, 22222);

1 row created.

SQL> Insert into test values (22222, 33333);

1 row created.

SQL> Select level 
  2  From test
  3  Connect by prior OLD_ARTICLE_NO = NEW_ARTICLE_NO
  4  Group by level
  5  Having level > 1;

     LEVEL
----------
         2

SQL> 

But I don't know how to create a check constraint out of it, if it's even possible that is...?
Re: Constraint restricting hierarchical use of table [message #417586 is a reply to message #417581] Mon, 10 August 2009 06:16 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Unfortunately. I do not have 9.2.0.8 version with me to test this and therefore cannot help you resolve the error. I hope somebody from the forum would help you to resolve the error.

@Michel,

Can you please let me know if you have any other solution apart from Materialized view for this? I can think of triggers but then it requires locking the table and therefore it will not be scalable solution as compared to Materialized view.

[Updated on: Mon, 10 August 2009 06:20]

Report message to a moderator

Re: Constraint restricting hierarchical use of table [message #417597 is a reply to message #417555] Mon, 10 August 2009 07:25 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't think you actually need this table at all. I suspect your life would be a lot simpler if you just added a new_article colunm to the parent table and then stick a foreign key and unique index on that.
Re: Constraint restricting hierarchical use of table [message #417609 is a reply to message #417586] Mon, 10 August 2009 08:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could just create a Refresh On Commit materialised view that just holds OLD_ARTICLE_NO and NEW_ARTICLE_NO. You could then check this Mview from a trigger to see if that Article number was in use.
Re: Constraint restricting hierarchical use of table [message #417647 is a reply to message #417555] Mon, 10 August 2009 14:27 Go to previous messageGo to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
Thank you all for the input!

I'm sorry for not being that responsive to your posts, I had to leave work for the day...

Now I'm home though and I tried this on my XE installation (version 10).

SQL> drop table test purge;

Table dropped.

SQL> create table test (old_article_no int primary key, new_article_no int unique);

Table created.

SQL> alter table test add constraint chk_old_not_equal_new check (old_article_no <> new_article_no);

Table altered.

SQL> CREATE OR REPLACE TRIGGER TEST_AT
  2  AFTER INSERT OR UPDATE ON TEST
  3  DECLARE
  4
  5    Cursor cTest is
  6      Select ARTICLE_NO
  7      From (Select OLD_ARTICLE_NO ARTICLE_NO
  8            From test
  9            union all
 10            Select NEW_ARTICLE_NO
 11            From test)
 12      Group by ARTICLE_NO
 13      Having count(*) > 1;
 14
 15  BEGIN
 16
 17    For rec in cTest loop
 18      Raise NO_DATA_FOUND;
 19    End loop;
 20
 21  END;
 22  /

Trigger created.

SQL> insert into test values (11111, 22222);

1 row created.

SQL> insert into test values (22222, 33333);
insert into test values (22222, 33333)
                  *
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "BJOBER.TEST_AT", line 16
ORA-04088: error during execution of trigger 'BJOBER.TEST_AT'


SQL> Select * from test;

OLD_ARTICLE_NO NEW_ARTICLE_NO
-------------- --------------
         11111          22222

1 row selected.

SQL> insert into test values (44444, 33333);

1 row created.

SQL> Select * from test;

OLD_ARTICLE_NO NEW_ARTICLE_NO
-------------- --------------
         11111          22222
         44444          33333

2 rows selected.

SQL> Commit;

Commit complete.

I had no imagination for a name on a user-defined exception so I just used the no_data_found to try it out. It seems that this works for me at home but we'll see what happens when I try it at work tomorrow (version 9).
Re: Constraint restricting hierarchical use of table [message #417676 is a reply to message #417647] Mon, 10 August 2009 23:37 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Have you tried this trigger solution in multi-user environment?


SQL>insert into test values (4,5);

1 row created.

SQL> --- Now open another session and do this

SQL>insert into test values (5,7);

1 row created.

SQL> -- Now back to session 1;

SQL>commit;

Commit complete.

SQL>

SQL> -- Now in session 2;

SQL>commit;

Commit complete.

SQL>select * from test
  2  /

OLD_ARTICLE_NO NEW_ARTICLE_NO
-------------- --------------
             4              5
             5              7

2 rows selected.




For trigger based solution across rows you have to lock the table to serialize the transactions and only then it will work.

[Updated on: Tue, 11 August 2009 00:00]

Report message to a moderator

Re: Constraint restricting hierarchical use of table [message #417685 is a reply to message #417676] Tue, 11 August 2009 02:00 Go to previous messageGo to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
bonker wrote on Tue, 11 August 2009 06:37
Have you tried this trigger solution in multi-user environment?

For trigger based solution across rows you have to lock the table to serialize the transactions and only then it will work.


No I didn't try this, I had no idea. Embarassed
I will try it as soon as I get a chance. Thanks for the heads up!
Re: Constraint restricting hierarchical use of table [message #417690 is a reply to message #417685] Tue, 11 August 2009 02:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're going to be using this in an environment where there will be a lot of insert/updates to this table, you really don't want to serialize access to the table in this way - it means every transaction except one that wants to modify the table has to wait until the first one has finished.
Re: Constraint restricting hierarchical use of table [message #417761 is a reply to message #417690] Tue, 11 August 2009 07:36 Go to previous messageGo to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
JRowbottom wrote on Tue, 11 August 2009 09:43
If you're going to be using this in an environment where there will be a lot of insert/updates to this table, you really don't want to serialize access to the table in this way - it means every transaction except one that wants to modify the table has to wait until the first one has finished.

You're right, it's probably not such a good idea to lock the table. I don't expect that many users and not a lot of inserts/updates either but that could probably change a lot faster than I realise...

[Updated on: Tue, 11 August 2009 07:43]

Report message to a moderator

Re: Constraint restricting hierarchical use of table [message #417810 is a reply to message #417761] Tue, 11 August 2009 10:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
bbergstrom74 wrote on Tue, 11 August 2009 14:36
I don't expect that many users and not a lot of inserts/updates either but that could probably change a lot faster than I realise...

Very wise words!
The first part of the sentence make up for some very famous last words, but the latter part shows great insight!
Re: Constraint restricting hierarchical use of table [message #417819 is a reply to message #417810] Tue, 11 August 2009 11:17 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You can also try to write the trigger on Materialized View to achieve this till you are able resolve the check constraint error on Materialized View.

I do not have access to Oracle right now to test this but just to get you started, I am posting this.

create materialized view test_mv
  build immediate
  refresh force on commit
  as
  select count(*) x from test a, test b
     where a.new_article_no = b.old_article_no;


Create or replace trigger aft_inst_updt after insert or update on test_mv
declare
l_reccnt int;
begin
select x into l_reccnt from test_mv;

if ( l_reccnt != 0 )
the
   raise_application_error(-20101,'Error: Article no already 
used');

end if;

end;



[Updated on: Tue, 11 August 2009 11:22]

Report message to a moderator

Re: Constraint restricting hierarchical use of table [message #417929 is a reply to message #417819] Wed, 12 August 2009 03:01 Go to previous messageGo to next message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
bonker wrote on Tue, 11 August 2009 18:17
I do not have access to Oracle right now to test this but just to get you started, I am posting this.


Thanks bonker! It worked almost right away, you had just missed an 'n' in 'then', then it compiled OK.

I will go with this solution for now and sort out the constraint error later as suggested. Thanks once again!
Re: Constraint restricting hierarchical use of table [message #417930 is a reply to message #417810] Wed, 12 August 2009 03:04 Go to previous message
bbergstrom74
Messages: 10
Registered: August 2009
Junior Member
Frank wrote on Tue, 11 August 2009 17:50
bbergstrom74 wrote on Tue, 11 August 2009 14:36
I don't expect that many users and not a lot of inserts/updates either but that could probably change a lot faster than I realise...

Very wise words!
The first part of the sentence make up for some very famous last words, but the latter part shows great insight!

Thanks! Smile

I'm glad I've learned something working in this line of business... Very Happy
Previous Topic: Cursors, locks, and Update
Next Topic: BLOB to TIF
Goto Forum:
  


Current Time: Sat Dec 10 08:55:23 CST 2016

Total time taken to generate the page: 0.10247 seconds