CODEWITHSUNDEEP
sqlsql-serveroracle-databaseconstraints
Jerry213
Jerry213

Reputation: 81

Why would someone need to enable/disable constraints?

Just starting to learn basics of SQL. In some versions of SQL (Oracle, SQL server etc.) there are enable/disable constraints keywords. What is the difference between these and add/drop constraints keywords? Why do we need it?

Upvotes: 1

Views: 1234

Answers (3)

The Impaler
The Impaler

Reputation: 48770

I [almost] never disable constraints during the normal operation of the application. The point of the constraints is to preserve data quality.

Now, during maintenance, I can disable them temporarily while adding or removing massive amounts of data. Once they data is loaded I make sure they are enabled again before restarting the application.

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

I use disabled constraints in a special situation. I have an application with many tables (around 1000). The records in these table have "natural keys", i.e. identifiers and relations which are given by external source. Some tables use even different natural keys as foreign key references to different tables.

But I like to use common surrogate keys as primary key and for foreign references.

Here is one example (not 100% sure about correct syntax):

CREATE TABLE T_BTS (
   OBJ_ID number constraint BTS_PK (OBJ_ID) PRIMARY KEY,
   BTS_ID VARCHAR2(20) CONSTRAINT BTS_UK (BTS_ID) UNIQUE,
   some more columns);

CREATE TABLE T_CELL (
   OBJ_ID number constraint BTS_PK (OBJ_ID) PRIMARY KEY,
   OBJ_ID_PARENT number,
   BTS_ID VARCHAR2(20),
   CELL_ID VARCHAR2(20) CONSTRAINT CELL_UK (BTS_ID, CELL_ID) UNIQUE,
   some more columns);

ALTER TABLE T_CELL ADD CONSTRAINT CELL_PARENT_FK
   FOREIGN KEY (OBJ_ID_PARENT)
   REFERENCES T_BTS (OBJ_ID);

ALTER TABLE T_CELL ADD CONSTRAINT CELL_PARENT
   FOREIGN KEY (BTS_ID)
   REFERENCES T_BTS (BTS_ID) DISABLE;

In all my tables the primary key column is always OBJ_ID and the key to parent table is always OBJ_ID_PARENT, not matter how the natural key is defined. This makes me easier to have common PL/SQL procedures and compose dynamic SQL Statements.

One example: In order to set OBJ_ID_PARENT after insert, following update would be needed

UPDATE T_CELL cell SET OBJ_ID_PARENT = 
   (SELECT OBJ_ID 
   FROM T_BTS bts
   WHERE cell.BTS_ID = bts.BTS_ID)

I am too lazy to write 1000+ such individual statements. By using views USER_CONSTRAINTS and USER_CONS_COLUMNS I am able to link the natural keys and the surrogate keys and I can execute these updates via dynamic SQL.

All my keys and references are purely defined by constraints. I don't need to maintain any extra table where I track relations or column names. The only limitation in my application design is, I have to utilize a certain naming convention for the constraints. But the countervalue for this is almost no maintenance is required to keep the data consistent and have good performance.

In order to use all above, some constrains needs to be disabled - even permanently.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

Constraint validation has a performance penalty when performing a DML operation. It's common to disable a constraint before a bulk insert/import of data (especially if you know that data is "OK"), and then enable it after the bulk operation is done.

Upvotes: 2

Related Questions