see_the_c

• Oct 25th, 2011

 

The database server implements the constraint as an index.

Whenever you use the single- or multiple-column constraint format to place a data restriction on a column, but without declaring a constraint name, the database server creates a constraint and adds a row for that constraint in the sysconstraints system catalog table.
The database server also generates an identifier and adds a row to the sysindexes system catalog table for each new primary-key, unique, or referential constraint that does not share an index with an existing constraint.

Even if you declare a name for a constraint, the database server generates the name that appears in the sysindexes table.


If you want, you can specify a meaningful name for the constraint. The name must be unique among the names of constraints and indexes in the database.

Constraint names appear in error messages having to do with constraint violations. You can use this name when you use the DROP CONSTRAINT clause of the ALTER TABLE statement.

Ex:
DROPPING OF CONSTRAINTS:
ALTER TABLE EMP
DROP CONSTRAINT

jimmy.katiyar11

 Profile Answers by jimmy.katiyar11 

• Nov 29th, 2011

 

Hi,

Mainly use constraints for a businessmen rule that restrict enter the record in a table. IN oracle there are 5 type of constraints --- 1) Primary key,
2) foreign key
3) Unique key
4) Not NUll
5) Check

Not Null constraint restrict the user can't null the column record means must be value every records in a column

In Oracle we check the constraint by the help of these query see below--

Select * from user_constraints,

--- If show the which column and which table column constraints by the help ---


Select * from user_cons_constraints;

Thanks

naizy_dba

 Profile Answers by naizy_dba 

• Feb 18th, 2012

 

Constraint are easy to reference if you give them a meaningful name. It is not necessary to assign name to constraint. If you do not assign any name to constraint Oracle will assign a name to constraint which will like sys_c(any six digit number).

To check constraints applied on your table, simply apply this query :

Code
SELECT constraint_name,constraint_type,column_name,STATUS FROM user_constraints u1 JOIN user_cons_columns USING(constraint_name) WHERE u1.table_name=name of TABLE IN capital letters;

Here, constraint name is name given to any constraint by you or Oracle.

Constraint type is type of constraint like not null, check, unique etc.

Column name on which constraint is applied
status of constraint whether it is enabled or disabled.

user_constraints and user_cons_columns are two data dictionary views.
u1 is the alias given to user_constraints.