Are you working with someone else's schema and they haven't declared nice names for all their constraints? Unfortunately, it is all too easy to create a table like this: CREATE TABLE order1 ( order_id NUMBER(18) NOT NULL PRIMARY KEY ); Or like this: CREATE TABLE order2 ( order_id NUMBER(18) NOT NULL, PRIMARY KEY (order_id) ); … Continue reading How to Quickly Rename all Primary Keys in Oracle
Tag: constraints
How Adding a UNIQUE Constraint on a OneToOne Relationship Helps Performance
A lot of people use SQL constraints mainly to enforce data integrity, and that's already a very good thing. A UNIQUE constraint, for instance, makes sure that there is at most one instance of any possible value (or tuple, in the case of a composite constraint) in a table. For instance: CREATE TABLE x ( … Continue reading How Adding a UNIQUE Constraint on a OneToOne Relationship Helps Performance
Have You Ever Wondered About the Difference Between NOT NULL and DEFAULT?
When writing DDL in SQL, you can specify a couple of constraints on columns, like NOT NULL or DEFAULT constraints. Some people might wonder, if the two constraints are actually redundant, i.e. is it still necessary to specify a NOT NULL constraint, if there is already a DEFAULT clause? The answer is: Yes! Yes, you … Continue reading Have You Ever Wondered About the Difference Between NOT NULL and DEFAULT?
Awesome SQL Trick: Constraints on Views
CHECK constraints are already pretty great when you want to sanitize your data. But there are some limitations to CHECK constraints, including the fact that they are applied to the table itself, when sometimes, you want to specify constraints that only apply in certain situations. This can be done with the SQL standard WITH CHECK … Continue reading Awesome SQL Trick: Constraints on Views
Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
(Sorry for that click-bait heading. Couldn't resist ;-) ) We're on a mission. To teach you SQL. But mostly, we want to teach you how to appreciate SQL. You'll love it! Getting SQL right or wrong shouldn't be about that You're-Doing-It-Wrong™ attitude that can be encountered often when evangelists promote their object of evangelism. Getting … Continue reading Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
Serious SQL: A “convex hull” of “correlated tables”
Now THIS is an interesting, and challenging question on the jOOQ user group: https://groups.google.com/d/topic/jooq-user/6TBBLYt9eR8/discussion Say you have a big database with lots of tables and foreign key references. Now you would like to know all tables that are somehow inter-connected by their respective foreign key relationship "paths". You could call this a "convex hull" around … Continue reading Serious SQL: A “convex hull” of “correlated tables”
How schema meta data impacts Oracle query transformations
I was recently wondering about some issue I had encountered between two tables. If tables undergo a lot of INSERT / UPDATE / DELETE statements, it may appear to be better to remove some constraints, at least temporarily for the loading of data. In this particular case, the foreign key relationship was permanently absent and … Continue reading How schema meta data impacts Oracle query transformations