Home » SQL & PL/SQL » SQL & PL/SQL » assertions (sql)
| assertions [message #576844] |
Fri, 08 February 2013 09:22  |
 |
ghostrider
Messages: 3
Registered: February 2013
Location: India
|
Junior Member |
|
|
|
what is the difference between assertions and check constraint?
|
|
|
|
|
|
|
|
| Re: assertions [message #576851 is a reply to message #576844] |
Fri, 08 February 2013 09:43  |
 |
Michel Cadot
Messages: 68643
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 #576854 is a reply to message #576853] |
Fri, 08 February 2013 10:15 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
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 #576859 is a reply to message #576856] |
Fri, 08 February 2013 11:59 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
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 |
|
Michel Cadot
Messages: 68643
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 #616942 is a reply to message #616940] |
Mon, 23 June 2014 07:11 |
John Watson
Messages: 8930
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.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 02:01:03 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
