Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: constraints
Take care with regular expressions
In an Office Hours session a couple of months back, I covered an important change that comes to regular expressions once you upgrade to 12c Release 2. You can see the video covering the issue here: but for the TL;DR brigade reading this post:…
DDL for constraints – subtle things
The DBMS_METADATA package is very cool. I remember the days of either hand-crafting DDL statements based on queries to the data dictionary, or many a DBA will be familiar with running “imp show=y” or “imp indexfile=…” in order to then laboriously extract the DDL…
The simplest things….can be risky
Java and Oracle expert Lukas Eder tweeted yesterday about a potential optimization that could be done when reviewing database SQL code. This looks to be a logical thing to do. Why scan the table T twice to perform an update, when the same job…
NULL’s vs NOT NULL’s and Performance
When it comes to giving the cost based optimiser the best possible chance to make the “right” decisions, many DBA’s are diligent in keeping statistics up to date, using histograms where appropriate, creating more indexes (or removing surplus indexes). However one often neglected area…
Please keep your foreign keys
I came across an interesting blog post the other day about whether databases should be (declaratively) enforcing the foreign key relationships between tables. The blog post discussed the issue of foreign keys being temporarily disabled to perform data loading, and then encountering the problem…
Duplicate constraints are impossible right ?
Here’s a very simple example of a table called PARENT being a (surprise surprise) parent in a referential integrity relationship to a (drum roll for my choice of name) CHILD table SQL> create table parent ( p int, constraint PAR_PK primary key (p) );…
Tightening up your data model
Having NOT NULL constraints declared on columns that are genuinely not nullable not only is good practice, but can yield performance gains. Here’s a little routine that lists those columns that probably need a NOT NULL constraint. It looks at all columns and check…
A justification for referential integrity constraints
Yes, I know what you’re thinking. “Here we go again. Another database dude about to get stuck into us telling us about the importance of foreign keys for data integrity” and yes, I could easily do that. But you’ve seen that before (and by…
Why I love working on AskTom
Today a question came in, and Swapnasis provided a nice simple test case, which makes my job so much easier. Here’s a snippet from it SQL> create table TTT( 2 testid integer not null, 3 value integer not null, 4 time timestamp not null,…
Partial uniqueness
I had an interesting request recently from a developer. “ I have a table created as per below create table C_TEST ( col_1 varchar2(3), col_2 varchar2(3), col_3 number ); The rows defined by col_1, col_2, col_3 must be unique but only when col_3 is…
