constraints

Thumbnail image for Conditional Unique Index on Multiple Columns

Conditional Unique Index on Multiple Columns

Oracle’s function-based indexes can be used to build conditional uniqueness on table columns. Here’s a working example of a conditional unique index on multiple columns.

Read the Full Article →
Thumbnail image for Unique Constraint vs Unique Index in Oracle

Unique Constraint vs Unique Index in Oracle

Unique constraints and unique indexes, on the surface, seem very alike. Dig deeper, and you will see that they differ. Let’s see how.

Read the Full Article →
Thumbnail image for Replace Duplicates in ID Column with Unique Values

Replace Duplicates in ID Column with Unique Values

A solution to the requirement of replacing only the duplicate IDs with unique values in a database table, and letting everything else stay untouched.

Read the Full Article →
Thumbnail image for How to Delete Old Data from a Database Schema

How to Delete Old Data from a Database Schema

DELETE script or CTAS DROP-RENAME script: ways to purge old data from a database schema so that it retains only the current year’s rows in all the tables in the schema, and the rest of the data is erased.

Read the Full Article →
Thumbnail image for NOCOPY Parameter Hint in PL/SQL

NOCOPY Parameter Hint in PL/SQL

NOCOPY parameter hint in PL/SQL suggests to the compiler to pass OUT and IN OUT parameters by reference instead of by value. The use of NOCOPY saves on the processing and memory overhead of copying data from subprogram to calling program.

Read the Full Article →
Thumbnail image for 10 Important Lessons in Data Modeling

10 Important Lessons in Data Modeling

Follow these ten data modeling guidelines to make sure that your application database design is accurate, usable and scalable.

Read the Full Article →
Thumbnail image for Drop and Recreate All Foreign Keys on Oracle Table

Drop and Recreate All Foreign Keys on Oracle Table

You might need to reistate a dropped table in Oracle, and in doing so, restore the foreign keys as well. Here’s how to drop and recreate all foreign keys on an Oracle table.

Read the Full Article →
Thumbnail image for ORA-02449: Solving the DROP TABLE Dilemma

ORA-02449: Solving the DROP TABLE Dilemma

Quick workaround to error ORA-02449. When you want to drop and recreate an Oracle table, but Oracle does not let you do so due to foreign key references.

Read the Full Article →
Thumbnail image for How to Add Table Column at Specific Position

How to Add Table Column at Specific Position

In Oracle, the ALTER TABLE command to add column appends the column to the end of all existing columns. What if you want the new column at a specific position in the table, not at the end? Here’s how to add a new column at a specific position.

Read the Full Article →
Oracle Views

20 Things You Should Know About Oracle Views

This one just might fail the Duck Test. It looks like a table, acts like a table, responds to SQLs like a table – but it is NOT a table. Learn about features and best practices for dealing with Oracle views.

Read the Full Article →