Home » SQL & PL/SQL » SQL & PL/SQL » assertions (sql)
assertions [message #576844] Fri, 08 February 2013 09:22 Go to next message
ghostrider
Messages: 3
Registered: February 2013
Location: India
Junior Member
what is the difference between assertions and check constraint?
Re: assertions [message #576845 is a reply to message #576844] Fri, 08 February 2013 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 22694
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>what is the difference between assertions and check constraint?

post URL to Oracle documentation where "assertion" is documented.

[Updated on: Fri, 08 February 2013 09:25]

Report message to a moderator

Re: assertions [message #576850 is a reply to message #576845] Fri, 08 February 2013 09:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1995
Registered: January 2010
Senior Member
BlackSwan wrote on Fri, 08 February 2013 10:24
post URL to Oracle documentation where "assertion" is documented.


DBMS_ASSERT

Oracle Database Search Results: assert

SY.

[Updated on: Fri, 08 February 2013 09:43]

Report message to a moderator

Re: assertions [message #576851 is a reply to message #576844] Fri, 08 February 2013 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None if you understand check constraint in its larger meaning.
But if you think about current implementations, check constraints are restricted to one row in one table and can't refer other rows while assertions are completely open and can contain any expression.
We can say that assertions are check constraints with no restriction.

Regards
Michel

[Updated on: Fri, 08 February 2013 09:45]

Report message to a moderator

Re: assertions [message #576852 is a reply to message #576851] Fri, 08 February 2013 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 22694
Registered: January 2009
Senior Member
http://www.rhinocerus.net/forum/databases/338792-rdbms-supports-create-assertion-statement.html
Re: assertions [message #576853 is a reply to message #576852] Fri, 08 February 2013 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, you can have a look at Toon Kopelaars and missing Lex de Haan's RuleGen (go to Product->RuelGen by example) for an implementation of (some) assertions in Oracle.

Regards
Michel
Re: assertions [message #576854 is a reply to message #576853] Fri, 08 February 2013 10:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1995
Registered: January 2010
Senior Member
It always makes me sceptical when vendor provides product examples that can be easily implemeted without that product:

SQL> create unique index emp_one_president
  2    on emp(
  3           case job
  4             when 'PRESIDENT' then -1
  5             else empno
  6           end
  7          )
  8  /

Index created.

SQL> update emp
  2     set job = 'PRESIDENT'
  3   where ename = 'SMITH'
  4  /
update emp
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EMP_ONE_PRESIDENT) violated


SQL> 


SY.
Re: assertions [message #576856 is a reply to message #576854] Fri, 08 February 2013 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first example is just there to make people understand the principle.
I didn't think about it but it is maybe more difficult to implement the second example: "Departments that employ a manager, should also employ an administrator."

If you are interested in the theory, I recommend you their book: Applied Mathematics for Database Professionals (subtitled "Learn to use set theory and logic to design databases and their business rules effectively, and to communicate precisely about those designs with other stakeholders".

As Toon specifies: "This book is about the mathematical foundation of relational databases; it demonstrates how you can use logic and set theory as tools to formally specify database designs, including data integrity constraints(a main topic of this book). Don't let the mention of math scare you off; Lex and I explain the required mathematical concepts with many examples and believe the book is accessible to the regular database professional. We only assume that you are familiar with designing a database."

I can confirm that the mathematical part does not need high skills or experience, and many exercises help to understand.
And the many examples of data integrity they give cannot be implemented by a simple FBI. Wink

(Note: I have no relation with the authors, it is a free opinion.)

Regards
Michel

Re: assertions [message #576859 is a reply to message #576856] Fri, 08 February 2013 11:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1995
Registered: January 2010
Senior Member
Michel,

I am not negating need for assertions. There are many cases where it can't be or can't be easily achieved with standard Oracle features. And in many cases it obviously simplifies implementation. Now that second example can be also implemented using standard Oracle features, although with certain level of "pain":

drop table emp1 purge
/
create table emp1
  as
    select  *
      from  emp
      where 1 = 2
/
alter table emp1
  add constraint emp1_pk
    primary key(empno)
/
alter table emp1
  add admin number
/
alter table emp1
  add constraint emp1_chk1
  check(
           (
                job = 'MANAGER'
            and
                admin is not null
           )
        or
           (
                nvl(job,'X') != 'MANAGER'
            and
                admin is null
           )
       )
/
alter table emp1
  add admin_job varchar2(9)
/
alter table emp1
  add constraint emp1_chk2
  check(
           (
                job = 'MANAGER'
            and
                admin_job = 'ADMIN'
           )
        or
           (
                nvl(job,'X') != 'MANAGER'
            and
                admin_job is null
           )
       )
/
alter table emp1
  add constraint emp1_uk1
    unique(
           deptno,
           empno,
           job
          )
/
alter table emp1
  add constraint emp1_fk1
    foreign key(
                deptno,
                admin,
                admin_job
               )
    references emp1(
                    deptno,
                    empno,
                    job
                   )
/


Now we can't insert insert manager before inserting administrator:

SQL> insert
  2    into emp1
  3    select  empno,          
  4            ename,
  5            job,
  6            mgr,
  7            hiredate,
  8            sal,
  9            comm,
 10            deptno,
 11            null,
 12            null
 13      from  emp
 14      where job = 'MANAGER'
 15  /
insert
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.EMP1_CHK1) violated


SQL> insert
  2    into emp1
  3    select  empno,          
  4            ename,
  5            job,
  6            mgr,
  7            hiredate,
  8            sal,
  9            comm,
 10            deptno,
 11            empno,
 12            'ADMIN'
 13      from  emp
 14      where job = 'MANAGER'
 15  /
insert
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.EMP1_FK1) violated - parent key not
found


SQL>  


So we insert administrators for each department first:

insert
  into emp1
  select  empno,          
          ename,
          'ADMIN',
          mgr,
          hiredate,
          sal,
          comm,
          deptno,
          null,
          null
    from  emp
    where ename in (
                    'MILLER',
                    'SMITH',
                    'TURNER'
                   )
/

3 rows created.

SQL> 


Now:

insert
  into emp1
  select  empno,          
          ename,
          job,
          mgr,
          hiredate,
          sal,
          comm,
          deptno,
          (
           select  empno
             from  emp1 e2
             where e2.deptno = e1.deptno
               and e2.job = 'ADMIN'
          ),
          'ADMIN'
    from  emp e1
    where job = 'MANAGER'
/

3 rows created.

SQL> select  *
  2    from  emp1
  3    order by deptno
  4  /

EMPNO ENAME      JOB         MGR HIREDATE    SAL COMM DEPTNO ADMIN ADMIN_JOB
----- ---------- --------- ----- --------- ----- ---- ------ ----- ---------
 7782 CLARK      MANAGER    7839 09-JUN-81  2450          10  7934 ADMIN
 7934 MILLER     ADMIN      7782 23-JAN-82  1300          10
 7369 SMITH      ADMIN      7902 17-DEC-80   800          20
 7566 JONES      MANAGER    7839 02-APR-81  2975          20  7369 ADMIN
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850          30  7844 ADMIN
 7844 TURNER     ADMIN      7698 08-SEP-81  1500    0     30

6 rows selected.

SQL> insert
  2    into emp1
  3    select  empno,          
  4            ename,
  5            'ADMIN',
  6            mgr,
  7            hiredate,
  8            sal,
  9            comm,
 10            deptno,
 11            null,
 12            null
 13      from  emp
 14      where ename not in (select ename from emp1)
 15  /

8 rows created.

SQL> select  *
  2    from  emp1
  3    order by deptno
  4  /

EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO ADMIN ADMIN_JOB
----- ---------- --------- ----- --------- ----- ----- ------ ----- ---------
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10  7934 ADMIN
 7839 KING       ADMIN           17-NOV-81  5000           10
 7934 MILLER     ADMIN      7782 23-JAN-82  1300           10
 7369 SMITH      ADMIN      7902 17-DEC-80   800           20
 7566 JONES      MANAGER    7839 02-APR-81  2975           20  7369 ADMIN
 7788 SCOTT      ADMIN      7566 19-APR-87  3000           20
 7876 ADAMS      ADMIN      7788 23-MAY-87  1100           20
 7902 FORD       ADMIN      7566 03-DEC-81  3000           20
 7499 ALLEN      ADMIN      7698 20-FEB-81  1600   300     30
 7521 WARD       ADMIN      7698 22-FEB-81  1250   500     30
 7654 MARTIN     ADMIN      7698 28-SEP-81  1250  1400     30

EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO ADMIN ADMIN_JOB
----- ---------- --------- ----- --------- ----- ----- ------ ----- ---------
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30  7844 ADMIN
 7844 TURNER     ADMIN      7698 08-SEP-81  1500     0     30
 7900 JAMES      ADMIN      7698 03-DEC-81   950           30

14 rows selected.

SQL>  


SY.
P.S. FK emp1_fk1 should be made as deferrable so we can insert employees in no particular order (within same transaction).

[Updated on: Fri, 08 February 2013 12:15]

Report message to a moderator

Re: assertions [message #576860 is a reply to message #576859] Fri, 08 February 2013 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that RuleGen only uses standard features.
I didn't analyse your implementation, as you are experienced I trust you but if we change just a bit of the requirements or add some rules ((like a manager cannot be an administrator or the president, or a manager cannot manage more than 2 departments), will it be easy to adapt it?
The purpose of RuleGen is not to say it is the only possible implementation, it is to give a frame to generate mathematically proved solution for (almost) any case (they are many limits the site, I think, enumerate them).

You don't need any skill in development in RuleGen, as you don't need any skill to declare a check constraint in CREATE or ALTER TABLE.
I don't know if you spent 1 hour and half full time to write this solution but how much time to prove it works in any case in mutlti-user environment? With RuleGen you do it just in few minutes as soon as you know how to write your rules in SELECT statements and you don't need any man power to test it.

This is not an advert for RuleGen and, once again, I do not say you cannot find other implementations, I just like the fact this tool generates proved code for rules... waiting Oracle implements assertions in its core engine.

Regards
Michel
Re: assertions [message #616931 is a reply to message #576860] Mon, 23 June 2014 04:57 Go to previous messageGo to next message
nakhatekk
Messages: 2
Registered: June 2014
Location: Bangalore
Junior Member
Hi,

As Oracle Rules Manager and expression filter is supported only in Oracle - 10g / 11g versions. Will this support in Oracle 12c? (or) Do we need to migrate to - Oracle Business Rules, a component of Oracle Fusion Middleware - to support on Oracle 12c versions. Please confirm.

Reference: http://docs.oracle.com/cd/E16655_01/server.121/e17642/deprecated.htm#BABFJIHA

Kindly clarify.

Regards,
Kiran Nakhate
Re: assertions [message #616935 is a reply to message #616931] Mon, 23 June 2014 05:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2099
Registered: May 2013
Location: World Wide on the Web
Senior Member
nakhatekk wrote on Mon, 23 June 2014 15:27
Will this support in Oracle 12c? (or) Do we need to migrate to - Oracle Business Rules, a component of Oracle Fusion Middleware - to support on Oracle 12c versions.

Reference: http://docs.oracle.com/cd/E16655_01/server.121/e17642/deprecated.htm#BABFJIHA



The link to docs you posted clearly says :

Quote:

Starting with Oracle Database 12c, the Expression Filter (EXF) and Database Rules Manager (RUL) features are desupported. If you are using Rules Manager, Oracle recommends that you consider migrating to Oracle Business Rules, a component of Oracle Fusion Middleware. The Continuous Query Notification feature of Oracle Database replaces Expression Filter.


Quote:
Please confirm.

Isn't that a confirmation? Shocked
Re: assertions [message #616940 is a reply to message #616935] Mon, 23 June 2014 06:59 Go to previous messageGo to next message
nakhatekk
Messages: 2
Registered: June 2014
Location: Bangalore
Junior Member
Hi Lalit,

We are using Oracle 11G Enterprise, with Oracle Rules Manager & Expression filter. Can we apply scripts in 12C environment - to start using Oracle Rules Manager. Solutions are already built on 11G with Rules Manager & customer is using it for the past one year. Migrating to new version with the different feature - will be a rework, cost impact.

Can we use Oracle Rules Manager in the 12c version of Oracle? Can you please clarify & suggest. Thanks.

Note: Features provided in one version can't be deprecated so fast.

Regards,
Kiran Nakhate
Re: assertions [message #616942 is a reply to message #616940] Mon, 23 June 2014 07:11 Go to previous message
John Watson
Messages: 4490
Registered: January 2010
Location: Global Village
Senior Member
The code to create the rules objects is all there in OH/rdbms/admin: catrule.sql, rulpbs.sql, and so on. So you can try to run them. You are on your own, though.
Previous Topic: Procedure to read mails from mailbox and insert into table
Next Topic: convert char to date
Goto Forum:
  


Current Time: Sat Aug 23 14:41:41 CDT 2014

Total time taken to generate the page: 0.10477 seconds