When we need to enforce uniqueness on a combination of table columns *only if* a certain condition is true, Oracle helps us implement this using a function-based index.
constraints
Unique Constraint vs Unique Index in Oracle
A unique constraint and a unique index in Oracle, on the face of it, look very alike. Both enforce uniqueness, and the unique index seems to piggyback on the constraint when the constraint is created or dropped.
Replace Duplicates in ID Column with Unique Values
A common scenario: in the development environment, a table’s ID column is missing its unique constraint, and developers have been creating their own data for testing. Suddenly someone realizes that the ID column has duplicates. The DBA is summoned. "Enable the unique key", the DBA is told, "but do not delete our data."
Can the DBA escape this catch-22? For sure, says this post — with an approach of replacing *only* the duplicate IDs with unique values, and letting everything else stay untouched.
How to Delete Old Data from a Database Schema
How would you delete 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?
NOCOPY Parameter Hint in PL/SQL
Before we get to understanding the NOCOPY parameter hint in PL/SQL, it would help to first look at PL/SQL parameter modes and the ways in which they pass values in and out of a subprogram.
PL/SQL subprograms have three parameter modes:
10 Important Lessons in Data Modeling
Are you data modeling for a custom OLTP application starting from scratch, with business requirements as the only initial input?
No matter what the size, domain or location of your project, the following lessons will help to ensure that your physical data modeling process is smooth and that you end up with a database design that is accurate, usable and scalable.
Drop and Recreate All Foreign Keys on Oracle Table
The last post showed a workaround for the error “ORA-02449: unique/primary keys in table referenced by foreign keys“, which blocks any attempt to drop an Oracle table if there are foreign keys from other tables referring to it.
The caveat: if the dropped table has to be recreated, the dropped foreign keys must be recreated as well.
How will you determine which foreign keys are to be recreated, and how will you quickly create them? Read on for an easy solution.
ORA-02449: Solving the DROP TABLE Dilemma
Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449:
SQL> drop table agreement;
drop table agreement
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this problem, and from which tables.
Luckily, there is a quick workaround to ORA-02449, provided you are ready to heed the risks of the approach along with it.
How to Add Table Column at Specific Position
In a perfect world, the database design would be firm and final before development starts. But we do not live in a perfect world – changes can and do happen. You forgot a column in a table, or your client has a change request – for various reasons, you may find that you need to add a column to an existing table.
Oracle allows you to alter a table easily for adding a column, but what if you want the new column added at a specific position in the table? Now you’re in a spot. There is no command to “alter table add column at position 2”; Oracle simply places the column after all the existing columns.
20 Things You Should Know About Oracle Views
-
A view is like a virtual table. It takes the output of a query and treats it like a table.
-
A view can be based on one or more tables or other views. These tables/views are called base tables.
-
A view takes up no storage space other than for the definition of the view in the data dictionary.