➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
To disable constraint in the Oracle database, we have to use ALTER … DROP with constraint name. The constraint name can be a pre-defined name generated by the Oracle database or a user-defined name.
Syntax to disable the constraint in Oracle,
ALTER TABLE tablename
DISABLE CONSTRAINT constraint-name;
Since there can be only one primary key in a table, so we can also disable the primary key constraint in Oracle using the below syntax,
ALTER TABLE tablename
DISABLE PRIMARY KEY;
In the Oracle database, whenever we are creating constraint then the Oracle server internally automatically generates a unique identification number for identifying a constraint uniquely in the format of SYS_Cn, this is also called as pre-defined constraint name. Example:-
SQL> CREATE TABLE test (
sno NUMBER(10) UNIQUE,
name VARCHAR2(10) UNIQUE
);
Table created.
SQL> INSERT INTO test VALUES(1, 'abc');
1 row created.
SQL> INSERT INTO test VALUES(1, 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014086) violated
SQL> INSERT INTO test VALUES(2, 'abc');
ORA-00001: unique constraint (SCOTT.SYS_C0014087) violated
Here SYS_C0014086 and SYS_C0014087 are the Oracle generated unique identification number of constraint applied on sno and name columns of the test table. We can get this information from user_cons_columns
.
SQL> DESC user_cons_columns;
Name Null? Type
----------------- -------- --------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
POSITION NUMBER
SQL> SELECT column_name, constraint_name
FROM user_cons_columns
WHERE table_name = 'TEST';
COLUMN_NAME CONSTRAINT_NAME
----------- ---------------
SNO SYS_C0014086
NAME SYS_C0014087
Note that in the above query table name TEST
is given in the capital letter. Oracle table name, column names are not case sensitive but records of a table are case sensitive, and here TEST is a record of user_cons_columns.
When Constraint is enabled at table level then the pre-defined constraint name will be same of those columns.
Example of disable the constraint having pre-defined name,
SQL> ALTER TABLE test
DISABLE CONSTRAINT SYS_C0014086;
Table altered.
SQL> SELECT column_name, constraint_name
FROM user_cons_columns
WHERE table_name = 'TEST';
COLUMN_NAME CONSTRAINT_NAME
----------- ---------------
NAME SYS_C0014087
While enabling/disabling/droping the constriants, pre-defined constraint name creates confusion for others and just by seeing pre-defined constraint name, we can’t identify which constraint is enabled on this column.
In place of pre-defined constraint name we can also assign our own name by using CONSTRAINT keyword by using following syntax. This is called user defined constraint name.
Syntax:-CONSTRAINT user-definedName constriantType
Example:-
SQL> CREATE TABLE test1 (
sno NUMBER(10) CONSTRAINT unique_sno UNIQUE,
name VARCHAR2(10)
);
Table created.
SQL> SELECT column_name, constraint_name
FROM user_cons_columns
WHERE table_name = 'TEST1';
COLUMN_NAME CONSTRAINT_NAME
-------------- --------------
SNO UNIQUE_SNO
Note:- User-defined constraint name must be unique within a user else Oracle gives an error.
Example of disable the constraint having user-defined name,
SQL> ALTER TABLE test1
DISABLE CONSTRAINT unique_sno;
Table altered.
Now, constraint is disabled and we can insert any value into the column.
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!
Also Learn,
I have read a few good stuff here, definitely price bookmarking for revisiting. I wonder how much attempt you set to create this kind of great informative website.