07.03.2026 08:58
👍 1
🔁 0
💬 0
📌 0
I think this was the first time I deliberately did it - muscle memory just puts in FROM DUAL every time 😇
Nope - not needed anymore 😁
But it’s still used even if I don’t write it. I should add a little explanation…
Assertions for data integrity on a less-than-ideal data model.
www.kibeha.dk/2026/03/asse...
Or "How I could have used assertions 25 years ago when I worked with Concorde XAL"...
#OrclDB #SQL #Assertions #OracleACE
Blog post coming up - with sample code.
These work fine too.
I'd hazard the guess that they can be checked and evaluated more efficiently.
Also it has the advantage of error messages specifying which assertion is violated.
create assertion Notes_RefRecId_MultiFK check ( not exists ( select null from Notes n where not exists ( select null from DebTab d where n.RefFileId = 11 and d.RecId = n.RefRecId ) and not exists ( select null from CreTab c where n.RefFileId = 15 and c.RecId = n.RefRecId ) and not exists ( select null from StoTab s where n.RefFileId = 27 and s.RecId = n.RefRecId ) ) );
Actually, this one works in a single assertion, turning it into NOT EXISTS with AND instead of EXISTS with OR.
But one assertion per parent table might be able to evaluate the checks faster and check only when needed. Whether that'd outweigh overhead of many assertions - who knows 😉
I had just now come to that conclusion too. 😁
Yes, with individual assertions with filter in ALL query, that should work. Will try it now.
create assertion Notes_RefRecId_MultiFK check ( all ( select n.RefFileId, n.RefRecId from Notes n ) nref satisfy ( exists ( select null from DebTab d where nref.RefFileId = 11 and d.RecId = nref.RefRecId UNION ALL select null from CreTab c where nref.RefFileId = 15 and c.RecId = nref.RefRecId UNION ALL select null from StoTab s where nref.RefFileId = 27 and s.RecId = nref.RefRecId ) ) );
create assertion Notes_RefRecId_MultiFK check ( all ( select n.RefFileId, n.RefRecId from Notes n ) nref satisfy ( exists ( select null where exists ( select null from DebTab d where nref.RefFileId = 11 and d.RecId = nref.RefRecId ) OR exists ( select null from CreTab c where nref.RefFileId = 15 and c.RecId = nref.RefRecId ) OR exists ( select null from StoTab s where nref.RefFileId = 27 and s.RecId = nref.RefRecId ) ) ) );
create assertion Notes_RefRecId_MultiFK check ( all ( select n.RefFileId, n.RefRecId from Notes n ) nref satisfy ( exists ( select null from dict d where d.FileId = nref.RefFileId and d.ColId = 0 and ( ( nref.RefFileId = 11 and exists ( select null from DebTab d where d.RecId = nref.RefRecId ) ) or ( nref.RefFileId = 15 and exists ( select null from CreTab c where c.RecId = nref.RefRecId ) ) or ( nref.RefFileId = 27 and exists ( select null from StoTab s where s.RecId = nref.RefRecId ) ) ) ) ) );
So I got my conditional/subset foreign key working.
But multi-parent foreign key I need to rethink how to phrase. So far my attempts have raised:
ORA-08663: Set operators are not supported.
ORA-08697: SYS owned tables are not supported.
ORA-08695: A subquery within an OR condition is not supported.
(This multi-parent foreign key I have not yet had time to try.)
I tried and got error:
ORA-08689: CREATE ASSERTION failed
ORA-08673: Equijoin "D"."FILEID"="N"."REFFILEID" found does not meet the criteria to do a FAST validation.
Yup - a foreign key to only some rows of another table. Problem is that the metadata table has pk of (FileId, ColumnId) - if we filter by "where ColumnId = 0", then FileId is unique among those rows - and that's what I want the conditional foreign key to refer to.
2. The "File" in FileId is actually table names. Some other tables have two columns RefFileId and RefRecId. The RefRecId is a foreign key to some other table (all tables have a RecId primary key) - the RefFileId decides to which table the RefRecId is a foreign key.
1. It had a "metadata" table with columns FileId and ColumnId and name.
For each FileId value there was 1 row with ColumnId = 0 for File name, rows ColumnId > 0 have Column names.
Other tables have RefFileId - I thought assertions could be "foreign key" to metadata table FileId where ColumnId = 0.
Oh, I had a couple ideas where I thought that assertions could help with improving data integrity of an ancient not-so-good datamodel used in legacy ERP system "Concorde XAL" that I worked a lot with 25-30 years ago.
I tried, but it couldn't be solved with assertions, sorry 😇
SQL statement: select use_cases from assertions where creator = 'Oracle ACE' order by real_world_applicability + complexity + novelty desc fetch first 3 rows only
We're still looking for interesting assertions use cases
Just over two weeks left for #OracleACE to get your entries in
Bounty closes 15th March
For full details read blogs.oracle.com/sql/assertio...
Aha.
Okay, where I discovered this was a customer that had migrated from on-premises 19c without extended strings to ExaCC 26ai that's autonomous and with extended strings. They never knew that now a VARCHAR2(4000 CHAR) could cause this to happen. They'll just change to VARCHAR2(4000 BYTE).
My google-fu must be weak - I didn't find anything about that?
TIL in 23/26ai with extended strings, you do NOT get "ORA-01450: maximum key length (6398) exceeded" at *create* index on VARCHAR2(4000 CHAR) - it succeeds => dba_ind_columns.column_length=16000
At *insert* you can get ORA-01450 !
blogs.oracle.com/sql/how-to-f...
Careful or #BugWaitingToHappen
Yup.
(I've always wondered why the NLS formats did not include something that matched the "Western Traditional" week numbering - WW doesn't, and I simply haven't found *any* place in the world using the rule that WW implements.)
Augh...
Just spotted that the WEEK calendar functions use YYYY and WW rather than IYYY and IW.
Worse - it's not even an option to use IYYY and IW.
I have no idea who is using WW - I have never found any usecase for a week numbering system where weeks begin on whatever weekday Jan 1 happens to be.
"Don't go nuts"
Sound advice any day.
Why I tend to write loooooooong pieces of text instead - but no use, people still misinterpret 😇
