Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Smile Unanswered: COLUMN equalent of Constraints ?

    hi ,

    Please find the following query.

    SQL> select CONSTRAINT_NAME
    2 from user_constraints
    3 where TABLE_NAME = 'TBSD_VAL_CR_EXP_ELEMENTS'
    4 and CONSTRAINT_TYPE ='R';

    CONSTRAINT_NAME
    ------------------------------
    SYS_C0020663
    SYS_C0020664

    Is there any way to know the column names equalent to the constraints
    and the related table name also?

    thanx in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: COLUMN equalent of Constraints ?

    USER_CONS_COLUMNS tells you the columns, and USER_CONSTRAINTS.R_CONSTRAINT_NAME tells you the name of the primary key constraint referenced. Look up that constraint to get the table name.

  3. #3
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: COLUMN equalent of Constraints ?

    ALL_CONS_COLUMNS
    ALL_CONS_COLUMNS describes columns that are accessible to the current user and that are specified in constraints.

    Related Views
    DBA_CONS_COLUMNS describes all columns in the database that are specified in constraints.
    USER_CONS_COLUMNS describes columns that are owned by the current user and that are specified in constraints.

    Column Datatype NULL Description
    OWNER
    VARCHAR2(30)
    NOT NULL
    Owner of the constraint definition

    CONSTRAINT_NAME
    VARCHAR2(30)
    NOT NULL
    Name of the constraint definition

    TABLE_NAME
    VARCHAR2(30)
    NOT NULL
    Name of the table with constraint definition

    COLUMN_NAME
    VARCHAR2(4000)
    Name of the column or attribute of the object type column specified in the constraint definition

    Note: If you create a constraint on a user-defined REF column, the system creates the constraint on the attributes that make up the REF column. Therefore, the column names displayed in this view are the attribute names, with the REF column name as a prefix, in the following form:

    "REF_name"."attribute"

    POSITION
    NUMBER
    Original position of column or attribute in the definition of the object

    http://download-west.oracle.com/docs...27.htm#1290598
    Joel Pérez

  4. #4
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: COLUMN equalent of Constraints ?

    ALL_CONSTRAINTS
    ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user.

    Related Views
    DBA_CONSTRAINTS describes all constraint definitions in the database.
    USER_CONSTRAINTS describes constraint definitions on tables in the current user's schema.

    Column Datatype NULL Description
    OWNER
    VARCHAR2(30)
    NOT NULL
    Owner of the constraint definition

    CONSTRAINT_NAME
    VARCHAR2(30)
    NOT NULL
    Name of the constraint definition

    CONSTRAINT_TYPE
    VARCHAR2(1)
    Type of constraint definition:

    C (check constraint on a table)
    P (primary key)
    U (unique key)
    R (referential integrity)
    V (with check option, on a view)
    O (with read only, on a view)

    TABLE_NAME
    VARCHAR2(30)
    NOT NULL
    Name associated with the table (or view) with constraint definition

    SEARCH_CONDITION
    LONG
    Text of search condition for a check constraint

    R_OWNER
    VARCHAR2(30)
    Owner of table referred to in a referential constraint

    R_CONSTRAINT_NAME
    VARCHAR2(30)
    Name of the unique constraint definition for referenced table

    DELETE_RULE
    VARCHAR2(9)
    Delete rule for a referential constraint (CASCADE or NO ACTION)

    STATUS
    VARCHAR2(8)
    Enforcement status of constraint (ENABLED or DISABLED)

    DEFERRABLE
    VARCHAR2(14)
    Whether the constraint is deferrable

    DEFERRED
    VARCHAR2(9)
    Whether the constraint was initially deferred

    VALIDATED
    VARCHAR2(13)
    Whether all data obeys the constraint (VALIDATED or NOT VALIDATED)

    GENERATED
    VARCHAR2(14)
    Whether the name of the constraint is user or system generated

    BAD
    VARCHAR2(3)
    A YES value indicates that this constraint specifies a century in an ambiguous manner. To avoid errors resulting from this ambiguity, rewrite the constraint using the TO_DATE function with a four-digit year.

    See Also: the TO_DATE function in Oracle9i SQL Reference and Oracle9i Application Developer's Guide - Fundamentals

    RELY
    VARCHAR2(4)
    Whether an enabled constraint is enforced or unenforced.

    See Also: the constraints in Oracle9i SQL Reference

    LAST_CHANGE
    DATE
    When the constraint was last enabled or disabled

    INDEX_OWNER
    VARCHAR2(30)
    Name of the user owning the index

    INDEX_NAME
    VARCHAR2(30)
    Name of the index

    http://download-west.oracle.com/docs...29.htm#1290750
    Joel Pérez

  5. #5
    Join Date
    Nov 2003
    Posts
    87
    thanx.

    SQL> select R_CONSTRAINT_NAME, CONSTRAINT_TYPE
    2 from USER_CONSTRAINTS
    3 where table_name = 'TCBN_DEPARTMENT'
    4 and constraint_type = 'R'
    5 /

    R_CONSTRAINT_NAME CONSTRAINT-TYPE
    ------------------------------ ----------------------
    SYS_C0032040 R
    SYS_C0020710 R
    SYS_C0020689 R

    How can i know which table these key's references?

    For eg:

    EMP
    ----
    EID
    ENAME
    DEPTID

    DEPT
    ------
    DID
    DNAME

    here i know that DID related to DEPTID..

    If i donno there is any way to know that?
    Hope u got my qstn.

    thanx.

  6. #6
    Join Date
    Nov 2003
    Posts
    87
    never mind.

    I got the required query.

    SELECT uc.r_constraint_name AS pk_constraint,
    uccp.table_name AS parent_table,
    uccp.column_name AS parent_column,uc.constraint_name AS fk_constraint, uccf.table_name AS ch
    FROM user_constraints uc,
    user_cons_columns uccp,
    user_cons_columns uccf
    WHERE uc.table_name = UPPER ('&tablename')
    AND uc.constraint_type = 'R'
    AND uc.r_constraint_name = uccp.constraint_name
    AND uc.constraint_name = uccf.constraint_name
    /

    :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •