Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Question Unanswered: query in user_cons_columns table

    There are 'user_constraints' and 'user_cons_columns' tables for every user, listing the constraints used.
    In my database, I have a table which has one primary key and one foreign key (referencing some other table). Now, in the user_cons_columns table, both these columns are assigned a 'position' value of '1' (value in the column user_cons_columns.position is 1).
    My question is - Is it allowed to have two column names in a table to have same 'position' field in the user_cons_columns table...?

    The reason I am asking this is that I am getting foreign key error while inserting into the table.
    Thanks,
    Yogesh.

  2. #2
    Join Date
    Nov 2003
    Posts
    87
    I dont think there will've a problem with same position number.
    Please check your query.

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    It is Possible to have two columns in same 'position' field in the user_cons_columns table.
    SATHISH .

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: query in user_cons_columns table

    Originally posted by yogeshk
    My question is - Is it allowed to have two column names in a table to have same 'position' field in the user_cons_columns table...?
    A quick experiment to understand the meaning of 'position':

    SQL> create table yogeshk (x int, y int, z int);

    Table created.

    SQL> alter table yogeshk add constraint pk primary key (x,y);

    Table altered.

    SQL> alter table yogeshk add constraint uq unique (z) ;

    Table altered.

    SQL> select CONSTRAINT_NAME, POSITION, COLUMN_NAME from user_cons_columns where table_name = upper('yogeshk');

    CONS POSITION COLU
    ---- ---------- ----
    PK 1 X
    PK 2 Y
    UQ 1 Z

    'position' is the position of the column in the constraint (similar to the position in the index). If you reverse the column in the pk:

    SQL> alter table yogeshk drop primary key;

    Table altered.

    SQL> alter table yogeshk add constraint pk primary key (y,x);

    Table altered.

    SQL> select CONSTRAINT_NAME, POSITION, COLUMN_NAME from user_cons_columns where table_name = upper('yogeshk');

    CONS POSITION COLU
    ---- ---------- ----
    PK 2 X
    PK 1 Y
    UQ 1 Z

    HTH
    Alberto

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

    Re: query in user_cons_columns table

    Originally posted by yogeshk
    There are 'user_constraints' and 'user_cons_columns' tables for every user, listing the constraints used.
    In my database, I have a table which has one primary key and one foreign key (referencing some other table). Now, in the user_cons_columns table, both these columns are assigned a 'position' value of '1' (value in the column user_cons_columns.position is 1).
    My question is - Is it allowed to have two column names in a table to have same 'position' field in the user_cons_columns table...?

    The reason I am asking this is that I am getting foreign key error while inserting into the table.
    Thanks,
    Yogesh.
    It is imposible to have 2 or more column names with the position number because that number represent the position of the field in the
    conformation of the constraint.

    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

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

    Re: query in user_cons_columns table

    Originally posted by yogeshk
    There are 'user_constraints' and 'user_cons_columns' tables for every user, listing the constraints used.
    In my database, I have a table which has one primary key and one foreign key (referencing some other table). Now, in the user_cons_columns table, both these columns are assigned a 'position' value of '1' (value in the column user_cons_columns.position is 1).
    My question is - Is it allowed to have two column names in a table to have same 'position' field in the user_cons_columns table...?

    The reason I am asking this is that I am getting foreign key error while inserting into the table.
    Thanks,
    Yogesh.
    It is imposible to have 2 or more column names with the equal position number because that number represent the position of the field in the
    conformation of the constraint.

    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/doc...227.htm#1290598
    Joel Pérez

  7. #7
    Join Date
    Jan 2004
    Posts
    11
    Thanks friends.

    I tried it with Alberto's example and it seems there's nothing wrong with my position column now. That very much settles my doubt about the position column.

    So, the problem must be somewhere else. It is an interesting problem and I will post the details in a new thread.

    Thanks.

Posting Permissions

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