On Database Constraints in Oracle

So there was something confusing about a check constraint in one of my tables.

I've proven that the check constraint works as it should, but I failed to understand why.

Actually there were two constraints that sort of combine.

Let's say I have a table called "customers". And that there are two constraints at play:

• constraint check01 check (bankaccountnr is not null or creditcardnr is not null or paypalemail is not null)
• constraint check02 check (bankaccountnr > 0 and creditcardnr > 0 and length(paypalemail) > 0)

The first constraint is obvious, at least one of the fields must be filled in, in order to process orders.

It's the second constraint that is confusing.

I thought that, because of the and, the check02 constraint should fire if I try to enter only a bankaccountnr (as the other two conditions evaluate to false).

And I thought I verified that by using the query:

select * from customers where bankaccountnr > 0 and creditcardnr > 0 and length(paypalemail) > 0;

As I suspected it provided me ONLY the records that had ALL fields entered properly.

But apparently, in (check) constraints, if the condition is "unknown", for example in the case of NULL-values, it is accepted.

According to the following quote, found in [1] which I found via [2]:

A check constraint on a column or set of columns requires that a specified condition be true or unknown for every row. If DML results in the condition of the constraint evaluating to false, then the SQL statement is rolled back.

References

[1] Oracle - Data Integrity

https://docs.oracle.com/database/121/CNCPT/datainte.htm#CNCPT1660

[2] StackOverflow - Why is this check constraint not working when it checks length?

https://stackoverflow.com/questions/66031098/why-is-this-check-constraint-not-working-when-it-checks-length

SQL: Concat operator and Spaces

So I had a pressing need to query my database and concatenate several values from a row together (using either concat or ||), but with spaces in between.

But I don't want two spaces if the value in between is empty.

It is surprisingly difficult to do in SQL, but there are some options available.

First the data:

TITLE	FIRSTNAME	MIDDLENAME	FAMILYNAME
	Ichabod		Crane
Dr.	Thomas		Lancaster
	Philip	Martin	Brown

0. No solution

select TITLE || ' ' || FIRSTNAME || ' ' || MIDDLENAME || ' ' || FAMILYNAME from PERSON;

So, if you use the SQL statement above, you get the problem, extraneous spaces. For example "[ ]Ichabod[ ][ ]Crane". We wish to eliminate those.

1. COALESCE

select coalesce(TITLE || ' ','') || coalesce(FIRSTNAME || ' ','') || coalesce(MIDDLENAME || ' ','') || FAMILYNAME from PERSON;

This is a good try, but doesn't work, as the first expression of the coalesce is never NULL, because of the concat used with the space (|| ' ') used.

1. DECODE

select decode(TITLE, null, '', TITLE, TITLE || ' ') ||
       decode(FIRSTNAME, null, '', FIRSTNAME, FIRSTNAME || ' ') ||
       decode(MIDDLENAME, null, '', MIDDLENAME, MIDDLENAME || ' ') ||
       FAMILYNAME
       from PERSON;

This works but is a lot of code to do something very simple.

2. CASE

select (case
           when TITLE is null then ''
           else TITLE || ' ' end) ||
       (case
           when FIRSTNAME is null then ''
           else FIRSTNAME || ' ' end) ||
       (case
           when MIDDLENAME is null then ''
           else MIDDLENAME || ' ' end) ||
       FAMILYNAME
from PERSON;

This works but is a lot of code. Luckily the case statement, when indented properly, is quite readable and not at all bad.

3. NVL2

select nvl2(TITLE, TITLE || ' ', '') ||
       nvl2(FIRSTNAME, FIRSTNAME || ' ', '') ||
       nvl2(MIDDLENAME, MIDDLENAME || ' ', '') ||
       FAMILYNAME from PERSON;

This works but is hard to read. Requires knowledge of nvl2.

4. LTRIM

select ltrim(TITLE || ' ') ||
       ltrim(FIRSTNAME || ' ') ||
       ltrim(MIDDLENAME || ' ') ||
       FAMILYNAME from PERSON;

As far as I can tell, this is quite appropriate. The ltrim removes spaces at the beginning of the string. If the string contains only ' ', all ' ' are removed and we're home free.

It is short, but requires some knowledge of what ltrim does exactly.

Falls in the category of 'doing something clever'.

5. REGEXP_REPLACE

select regexp_replace(TITLE
        || ' ' || FIRSTNAME
        || ' ' || MIDDLENAME
        || ' ' || FAMILYNAME
        , ' +', ' ')
from person;

A colleague of mine came up with this little chestnut.

It's nice if you enjoy regular expressions. I do not.

Still, it's nice to be able to just concat everything together, and have the whole thing sorted out with one simple regular expression.

Addendum

The function wm_concat has been removed from Oracle PL/SQL and should not be used.

Which is a shame, as it does exactly what we want.

References

American Culture - Naming

https://culturalatlas.sbs.com.au/american-culture/american-culture-naming

Lalit Kumar B - Why not use WM_CONCAT function in Oracle?

https://lalitkumarb.wordpress.com/2015/04/29/why-not-use-wm_concat-function-in-oracle/

Updates: added regexp_replace solution

10 Common Mistakes Java Developers Make when Writing SQL

Just some references to very important articles about common mistakes Java Developers make when trying to write SQL statements.

Just putting it here, in case I forget.

Because let's be honest here, I've committed quite a few in my time.

References

JAVA, SQL AND JOOQ. - 10 Common Mistakes Java Developers Make when Writing SQL

https://blog.jooq.org/10-common-mistakes-java-developers-make-when-writing-sql/

JAVA, SQL AND JOOQ. - 10 More Common Mistakes Java Developers Make when Writing SQL

https://blog.jooq.org/10-more-common-mistakes-java-developers-make-when-writing-sql/

JAVA, SQL AND JOOQ. - Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)

https://blog.jooq.org/yet-another-10-common-mistakes-java-developer-make-when-writing-sql-you-wont-believe-the-last-one/

Entity-Relationship Diagrams

I tend to forget what the arrows mean in an entity-relationship diagram.

Example

In the diagram above, there's strong limitations on the values possible in the GuildMember table. Unfortunately, these cannot be expressed in Foreign Key Relationships.

For example:

• a Person can hold only one rank in a Guild.
• a Person can only be a GuildMember in a Guild once.

The way to fascilitate this, is to put a unique index on GuildMember regarding the columns GUILDNR and PERSONNR.

References

PlantUML - Entity Relationship Diagram

https://plantuml.com/ie-diagram

LucidChart - Wat is een Entity Relationship Diagram?

https://www.lucidchart.com/pages/nl/wat-is-een-entity-relationship-diagram

Join in SQL

There are lots of nice diagrams explaining the difference between the different joins in SQL.

References

Code Project for those who Code - Visual Representation of SQL Joins

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

MERGE SQL Statement

I recently had to do a insert on the database, if the records weren't there, and an update if it was.

Performance was a problem.

Colleague of mine had the same problem when using an insert and update statement with an (exists (select 1)) on 2.5 million records.

After 5 minutes, 5000 rows were done. It would have taken 41 hours to fix them all.

After a merge¹ statement, the entire thing was done in 2.5 minutes.

So, I had to get to grips with the merge statement as well. I hadn't used it before.

There's plenty of tutorials on how it works.

Oracle allows a DELETE statement in the MERGE statement, which seems not to be defined in the SQL Standard.

References

[1] Wikipedia - Merge(SQL)

https://en.wikipedia.org/wiki/Merge_(SQL)

[2] Oracle PL/SQL Documentation - MERGE

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

Comparing NULLs in SQL

Consider the following table "mm_mailtable", containing the following data:

id	name	toname	whensent	subject	haveread	newmail
27999	William	Joe	2018-03-13 12:41:25	Golf next week?	1	0
28000	William	Linda	2018-03-13 12:41:25	I'm out!	1	0
28001	Linda	William	2018-03-13 12:41:25	Okay!	1	0
28002	Joe	William	2018-03-13 12:41:25	Sure thing!	1	1
28003	Jim	William	2018-03-13 12:41:25	The house	1	NULL

Also consider the following query I found in our source code somewhere.

SELECT *
FROM mm_mailtable
WHERE toname = :NAME
AND newmail = COALESCE(:NEWMAIL, newmail);

Let's say we try the query out with "William" as NAME and 0 as NEWMAIL.

We get an old mail to William with "Okay!" as subject.

Let's say we try the query out with "William" as NAME and 1 as NEWMAIL.

We get a new mail to William with "Sure thing!" as subject.

Let's say we try the query out with "William" as NAME and NULL as NEWMAIL.

Now I would expect to get all three entries returned, but the one containing the NULL value for "newmail" is not returned.

It takes some getting used to, but comparing null values is not possible in most databases, as it is undefined (a.k.a.. NULL).

See for more and better explanations the references below.

P.S. in this case, in our database, the column "newmail" should have been defined as "NOT NULL" and given a "DEFAULT" value, to prevent this sort of thing. Apparently it was forgotten.

Rewriting the query

This should work:

SELECT * 
FROM mm_mailtable 
WHERE toname = :NAME
AND (:NEWMAIL is null OR :NEWMAIL = newmail);

References

StackOverflow - why is null not equal to null false

https://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false

Baron Schwartz's Blog - Why NULL never compares false to anything in SQL

https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

Removing HTML tags from Strings via SQL statements

I was just looking for a way to remove tags from strings in a database. MySQL has no support for regular expressions, so I fell back to the old way.

Just writing it down, as I think I might need it later too.

The first one is to determine which ones are to be changed. The second one changes nothing, but outputs the new result. The third one actually changed the data.

select id, adject3, adject1, adject2, adject2 from mm_items where name like '%<%' or adject1 like '%<%' or adject2 like '%<%' or adject3 like '%<%';

select adject3, concat(substring(adject3, 1, locate('<', adject3)-1), substring(adject3, locate('>', adject3) + 1)) from mm_items where adject3 like '%<%>%';

update mm_items set adject3 = concat(substring(adject3, 1, locate('<', adject3)-1), substring(adject3, locate('>', adject3) + 1)) where adject3 like '%<%>%';

Keyset pagination

In the past I have used the MySQL equivalent of pagination. In other words, the splitting up of a ResultSet into pages of a fixed number of entries, by means of using SQL¹.

It looks like the following:

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax, which I've used in the past.

Performance

Performance is a key point here, as MySQL requires the retrieval of the results in order to determine where the offset starts.

If the table is large, retrieval of pages at the end of the table are going to be extremely slow.

Solution

A better way to deal with this, is to not use an offset, but use the key of the last row of the previous page, and use that in the query for the next page.

Obviously this only works if the resultset is sorted.

For more references that explain this a lot better, see [2] and [3].

References

[1] MySQL 5.7 - 14.2.9. SELECT Syntax

https://dev.mysql.com/doc/refman/5.7/en/select.html

[2] Use the Index, Luke! - We need tool support for keyset pagination

http://use-the-index-luke.com/no-offset

[3] Use the Index, Luke! - Paging Through Results

http://use-the-index-luke.com/sql/partial-results/fetch-next-page