Use of Constraint Name

What is the use of mentioning of constraint_name along with NOT NULL
Constraint _type while creating a table?

Questions by svp.kiran

Showing Answers 1 - 3 of 3 Answers

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


  Was this answer useful?  Yes


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

  Was this answer useful?  Yes

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
  1. 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.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions