• RELEVANCY SCORE 4.23

    DB:4.23:Find Constraint Name Given Column Names And Table Name dz




    Hi,

    Does anybody know how to create a sql query that will return a constraint name from user_constraints and user_cons_columns given a table name, a list of columns and the constraint_type.

    For example, I would like to find out a UNIQUE constraint's name on a table T1. This constraint is on columns ('C1', 'C2', 'C3') in table T1.

    Any help is appreciated!

    Thank you.

    DB:4.23:Find Constraint Name Given Column Names And Table Name dz

    select * from user_constraints where table_name='table_name'
    /

    select * from user_cons_columns where table_name='table_name'
    /

  • RELEVANCY SCORE 3.56

    DB:3.56:Find Column Name From Constraint Name k3




    How to fetch the column name from a constraint name to which the constraint is applied?.

    I only know the name of the constraint and can get the table name from "dba_constraints". How can I know to which column in the table this constraint is applied.

    Thanks

    DB:3.56:Find Column Name From Constraint Name k3

    What about this?

    satyaki
    satyaki
    satyakidesc 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

    satyaki
    satyakiRegards.

    Satyaki De.

  • RELEVANCY SCORE 3.55

    DB:3.55:Primary Keys k9




    hi every body
    execuse me guide me to this
    why in any table which consists of a column with employees' names
    why i have to create a nother column beside that column which contains numbers to each name and constraint it as a primary key
    what will happen if i created just the column of the employee name and constraint is (not null) ?
    why i need the numbers column and why i need to constraint it a primary key?
    thanks

  • RELEVANCY SCORE 3.48

    DB:3.48:Unique Columns And Logical Deletion 8d


    In tables where reporting or historical information needs to be maintained I find that logical deletion of rows is better than actually deleting the row and any FK relationships.  However when one of the columns in the table must be unique this introduces a problem because uniqueness only matters for active rows.  I'm hoping someone can provide some advice on how to best handle this situation.
     
    Here is an example to work from.  Suppose we have a table that contains job positions.  The table contains an IDENTITY column for the PK (to avoid duplicating position names), a Name column that must be unique and an IsActive column that is set to 0 when the position has been deleted.  This setup allows for historical reporting to properly pick up the position information even though it might no longer be used in a company.  You can substitute your own scenarios here as there are quite a few.
     
    Now if the Name column is marked as a unique column then the DB will enforce integrity which is what we want.  However we will want to be able to delete a position but later add a new position with the same name.  One could argue that we should just undelete the original position but that is not often a good idea so assume undelete is not an option.  Given the unique constraint a new position could not be created if an existing position (active or otherwise) already existed with the same name.  We could set up the unique constraint to include the Name and IsActive columns but now we are prevented from ever deleting two positions with the same name.
     
    My initial thought is to create a new UniqueName column that is unique and remove the uniqueness constraint on the Name column.  The UniqueName column is set (via a trigger) to the Name column when the position is active.  When the position is deleted the trigger changes the UniqueName to include a timestamp or something to ensure uniqueness.  This is handled by the system and not exposed to clients.
     
    Any advice or alternatives on this approach (preferably with advantages and disadvantages)?  Thanks.
     
    Michael Taylor - 5/31/07
    http://p3net.mvps.org
     

    DB:3.48:Unique Columns And Logical Deletion 8d

    For data that doesn't change often an archive table adds more work than it benefits.

  • RELEVANCY SCORE 3.46

    DB:3.46:Fk Constraint 38


    Hello ..
    I had this problem before .. but for some reason .. I am still missing foreign keys which need disabling before I can extend a PK to include an extra column in Ora 9.2.

    I am running the following to pick up all FK's that reference my table ..

    select 'alter table '||table_name||' disable constraint '||constraint_name||';' from all_constraints where owner = 'ADM_USER' and constraint_type = 'R' and r_constraint_name in
    ( select constraint_name from all_constraints where table_name = 'REF_CURRENCY');

    and then enable them after tinkering with the PK..

    PK is presently just one column iso_code .. so I also make a unique index on iso_code after dropping the Pk to satisfy the existing data.

    What FK's am I missing?

    Here is the listing .. .
    -- disable all foreign keys
    alter table CLN_ACCOUNT disable constraint FK_ACCT_CRCY_ALT;
    alter table CLN_ACCOUNT disable constraint FK_ACCT_CRCY_PORTFOLIO;
    alter table CLN_ACCOUNT disable constraint FK_ACCT_CRCY_ACCOUNT;
    alter table CLN_CREDIT disable constraint FK_CRDT_CRCY_CAPITAL;
    alter table CLN_FACILITY_FOR_ACCOUNT disable constraint FCAC_CRCY_AVAILABILITY;
    alter table CLN_FACILITY_FOR_ACCOUNT disable constraint FK_FCAC_CRCY_LIMIT;
    alter table CLN_FACILITY_FOR_ACCOUNT disable constraint FK_FCAC_CRCY_UTILISATION;
    alter table CLN_MANDATE disable constraint FK_MNDT_CRCY_ALT;
    alter table CLN_MANDATE disable constraint FK_MNDT_CRCY_CREDIT_LIMIT;
    alter table CLN_MANDATE disable constraint FK_MNDT_CRCY_MANDATE;
    alter table CLN_MANDATE disable constraint FK_MNDT_CRCY_CREDIT_UTIL;
    alter table CLN_PORTFOLIO disable constraint FK_PTFL_CRCY_ALT;
    alter table CLN_PORTFOLIO disable constraint FK_PTFL_CRCY_CREDIT_LIMIT;
    alter table CLN_PORTFOLIO disable constraint FK_PTFL_CRCY_PORTFOLIO;
    alter table CLN_PORTFOLIO disable constraint FK_PTFL_CRCY_CREDIT_UTIL;
    alter table CLN_POSITION disable constraint FK_POSN_CRCY_ASSET;
    alter table CLN_POSITION disable constraint FK_POSN_CRCY_PTF;
    alter table CLN_UTILISATION_FOR_ACCOUNT disable constraint FK_UTAC_CRCY_UTILISATION;
    alter table FTN_CASH_MOVEMENT disable constraint FK_CMVT_CRCY_ACCOUNT;
    alter table FTN_LIFE_ASSURANCE disable constraint FK_LASS_CCY;
    alter table FTN_LIFE_ASSURANCE_POSITION disable constraint FK_LASSP_ASSCCY;
    alter table FTN_LIFE_ASSURANCE_POSITION disable constraint FK_LASSP_CCY;
    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_ACCOUNT;
    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_CLIENT;
    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_SETTELEMNT;
    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_QUOT_ASSET;
    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_ASSET;
    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_ACCT;
    alter table PAY_PAYMENT_FX disable constraint FK_PAY_FX_CCY_FROM;
    alter table REF_ASSET disable constraint FK_ASST_CRCY_LOCAL;
    alter table REF_ASSET disable constraint FK_ASST_CRCY_PRICE;
    alter table REF_CREDIT_AUTHORITY disable constraint FK_CRAU_RCURR;

    -- disable PK Constraint on REF_CURRENCY

    alter table REF_CURRENCY drop constraint PK_CURRENCY;

    -- Create new PK for REF_CURRENCY
    alter table REF_CURRENCY ADD constraint PK_CURRENCY PRIMARY KEY (ISO_CODE, ANNULLED_IND);

    -- Create new constraint for FK's to refer to
    alter table REF_CURRENCY ADD constraint ISO_CODE_U UNIQUE (ISO_CODE);

    -- enable all foreign keys
    alter table CLN_ACCOUNT enable constraint FK_ACCT_CRCY_ALT;
    alter table CLN_ACCOUNT enable constraint FK_ACCT_CRCY_PORTFOLIO;
    alter table CLN_ACCOUNT enable constraint FK_ACCT_CRCY_ACCOUNT;
    alter table CLN_CREDIT enable constraint FK_CRDT_CRCY_CAPITAL;
    alter table CLN_FACILITY_FOR_ACCOUNT enable constraint FCAC_CRCY_AVAILABILITY;
    alter table CLN_FACILITY_FOR_ACCOUNT enable constraint FK_FCAC_CRCY_LIMIT;
    alter table CLN_FACILITY_FOR_ACCOUNT enable constraint FK_FCAC_CRCY_UTILISATION;
    alter table CLN_MANDATE enable constraint FK_MNDT_CRCY_ALT;
    alter table CLN_MANDATE enable constraint FK_MNDT_CRCY_CREDIT_LIMIT;
    alter table CLN_MANDATE enable constraint FK_MNDT_CRCY_MANDATE;
    alter table CLN_MANDATE enable constraint FK_MNDT_CRCY_CREDIT_UTIL;
    alter table CLN_PORTFOLIO enable constraint FK_PTFL_CRCY_ALT;
    alter table CLN_PORTFOLIO enable constraint FK_PTFL_CRCY_CREDIT_LIMIT;
    alter table CLN_PORTFOLIO enable constraint FK_PTFL_CRCY_PORTFOLIO;
    alter table CLN_PORTFOLIO enable constraint FK_PTFL_CRCY_CREDIT_UTIL;
    alter table CLN_POSITION enable constraint FK_POSN_CRCY_ASSET;
    alter table CLN_POSITION enable constraint FK_POSN_CRCY_PTF;
    alter table CLN_UTILISATION_FOR_ACCOUNT enable constraint FK_UTAC_CRCY_UTILISATION;
    alter table FTN_CASH_MOVEMENT enable constraint FK_CMVT_CRCY_ACCOUNT;
    alter table FTN_LIFE_ASSURANCE enable constraint FK_LASS_CCY;
    alter table FTN_LIFE_ASSURANCE_POSITION enable constraint FK_LASSP_ASSCCY;
    alter table FTN_LIFE_ASSURANCE_POSITION enable constraint FK_LASSP_CCY;
    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_ACCOUNT;
    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_CLIENT;
    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_SETTELEMNT;
    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_QUOT_ASSET;
    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_ASSET;
    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_ACCT;
    alter table PAY_PAYMENT_FX enable constraint FK_PAY_FX_CCY_FROM;
    alter table REF_ASSET enable constraint FK_ASST_CRCY_LOCAL;
    alter table REF_ASSET enable constraint FK_ASST_CRCY_PRICE;
    alter table REF_CREDIT_AUTHORITY enable constraint FK_CRAU_RCURR;

    OUTPUT..
    -- disable all foreign keys
    alter table CLN_ACCOUNT disable constraint FK_ACCT_CRCY_ALT
    Table altered

    alter table CLN_ACCOUNT disable constraint FK_ACCT_CRCY_PORTFOLIO
    Table altered

    alter table CLN_ACCOUNT disable constraint FK_ACCT_CRCY_ACCOUNT
    Table altered

    alter table CLN_CREDIT disable constraint FK_CRDT_CRCY_CAPITAL
    Table altered

    alter table CLN_FACILITY_FOR_ACCOUNT disable constraint FCAC_CRCY_AVAILABILITY
    Table altered

    alter table CLN_FACILITY_FOR_ACCOUNT disable constraint FK_FCAC_CRCY_LIMIT
    Table altered

    alter table CLN_FACILITY_FOR_ACCOUNT disable constraint FK_FCAC_CRCY_UTILISATION
    Table altered

    alter table CLN_MANDATE disable constraint FK_MNDT_CRCY_ALT
    Table altered

    alter table CLN_MANDATE disable constraint FK_MNDT_CRCY_CREDIT_LIMIT
    Table altered

    alter table CLN_MANDATE disable constraint FK_MNDT_CRCY_MANDATE
    Table altered

    alter table CLN_MANDATE disable constraint FK_MNDT_CRCY_CREDIT_UTIL
    Table altered

    alter table CLN_PORTFOLIO disable constraint FK_PTFL_CRCY_ALT
    Table altered

    alter table CLN_PORTFOLIO disable constraint FK_PTFL_CRCY_CREDIT_LIMIT
    Table altered

    alter table CLN_PORTFOLIO disable constraint FK_PTFL_CRCY_PORTFOLIO
    Table altered

    alter table CLN_PORTFOLIO disable constraint FK_PTFL_CRCY_CREDIT_UTIL
    Table altered

    alter table CLN_POSITION disable constraint FK_POSN_CRCY_ASSET
    Table altered

    alter table CLN_POSITION disable constraint FK_POSN_CRCY_PTF
    Table altered

    alter table CLN_UTILISATION_FOR_ACCOUNT disable constraint FK_UTAC_CRCY_UTILISAT
    Table altered

    alter table FTN_CASH_MOVEMENT disable constraint FK_CMVT_CRCY_ACCOUNT
    Table altered

    alter table FTN_LIFE_ASSURANCE disable constraint FK_LASS_CCY
    Table altered

    alter table FTN_LIFE_ASSURANCE_POSITION disable constraint FK_LASSP_ASSCCY
    Table altered

    alter table FTN_LIFE_ASSURANCE_POSITION disable constraint FK_LASSP_CCY
    Table altered

    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_ACCOUNT
    Table altered

    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_CLIENT
    Table altered

    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_SETTELEMNT
    Table altered

    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_QUOT_ASSET
    Table altered

    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_ASSET
    Table altered

    alter table FTN_SECURITY_MOVEMENT disable constraint FK_SMVT_CRCY_ACCT
    Table altered

    alter table PAY_PAYMENT_FX disable constraint FK_PAY_FX_CCY_FROM
    Table altered

    alter table REF_ASSET disable constraint FK_ASST_CRCY_LOCAL
    Table altered

    alter table REF_ASSET disable constraint FK_ASST_CRCY_PRICE
    Table altered

    alter table REF_CREDIT_AUTHORITY disable constraint FK_CRAU_RCURR
    Table altered

    -- disable PK Constraint on REF_CURRENCY

    alter table REF_CURRENCY drop constraint PK_CURRENCY
    ORA-02273: this unique/primary key is referenced by some foreign keys
    -- Create new PK for REF_CURRENCY
    alter table REF_CURRENCY ADD constraint PK_CURRENCY PRIMARY KEY (ISO_CODE, ANNUL
    ORA-02260: table can have only one primary key
    -- Create new constraint for FK's to refer to
    alter table REF_CURRENCY ADD constraint ISO_CODE_U UNIQUE (ISO_CODE)
    ORA-02261: such unique or primary key already exists in the table
    -- enable all foreign keys
    alter table CLN_ACCOUNT enable constraint FK_ACCT_CRCY_ALT
    Table altered

    alter table CLN_ACCOUNT enable constraint FK_ACCT_CRCY_PORTFOLIO
    Table altered

    alter table CLN_ACCOUNT enable constraint FK_ACCT_CRCY_ACCOUNT
    Table altered

    alter table CLN_CREDIT enable constraint FK_CRDT_CRCY_CAPITAL
    Table altered

    alter table CLN_FACILITY_FOR_ACCOUNT enable constraint FCAC_CRCY_AVAILABILITY
    Table altered

    alter table CLN_FACILITY_FOR_ACCOUNT enable constraint FK_FCAC_CRCY_LIMIT
    Table altered

    alter table CLN_FACILITY_FOR_ACCOUNT enable constraint FK_FCAC_CRCY_UTILISATION
    Table altered

    alter table CLN_MANDATE enable constraint FK_MNDT_CRCY_ALT
    Table altered

    alter table CLN_MANDATE enable constraint FK_MNDT_CRCY_CREDIT_LIMIT
    Table altered

    alter table CLN_MANDATE enable constraint FK_MNDT_CRCY_MANDATE
    Table altered

    alter table CLN_MANDATE enable constraint FK_MNDT_CRCY_CREDIT_UTIL
    Table altered

    alter table CLN_PORTFOLIO enable constraint FK_PTFL_CRCY_ALT
    Table altered

    alter table CLN_PORTFOLIO enable constraint FK_PTFL_CRCY_CREDIT_LIMIT
    Table altered

    alter table CLN_PORTFOLIO enable constraint FK_PTFL_CRCY_PORTFOLIO
    Table altered

    alter table CLN_PORTFOLIO enable constraint FK_PTFL_CRCY_CREDIT_UTIL
    Table altered

    alter table CLN_POSITION enable constraint FK_POSN_CRCY_ASSET
    Table altered

    alter table CLN_POSITION enable constraint FK_POSN_CRCY_PTF
    Table altered

    alter table CLN_UTILISATION_FOR_ACCOUNT enable constraint FK_UTAC_CRCY_UTILISATI
    Table altered

    alter table FTN_CASH_MOVEMENT enable constraint FK_CMVT_CRCY_ACCOUNT
    Table altered

    alter table FTN_LIFE_ASSURANCE enable constraint FK_LASS_CCY
    Table altered

    alter table FTN_LIFE_ASSURANCE_POSITION enable constraint FK_LASSP_ASSCCY
    Table altered

    alter table FTN_LIFE_ASSURANCE_POSITION enable constraint FK_LASSP_CCY
    Table altered

    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_ACCOUNT
    Table altered

    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_CLIENT
    Table altered

    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_SETTELEMNT
    Table altered

    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_QUOT_ASSET
    Table altered

    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_ASSET
    Table altered

    alter table FTN_SECURITY_MOVEMENT enable constraint FK_SMVT_CRCY_ACCT
    Table altered

    alter table PAY_PAYMENT_FX enable constraint FK_PAY_FX_CCY_FROM
    Table altered

    alter table REF_ASSET enable constraint FK_ASST_CRCY_LOCAL
    Table altered

    alter table REF_ASSET enable constraint FK_ASST_CRCY_PRICE
    Table altered

    alter table REF_CREDIT_AUTHORITY enable constraint FK_CRAU_RCURR
    Table altered

    Regards
    Jag

    DB:3.46:Fk Constraint 38

    Thanks .. yep I knew it was something that simple ..

  • RELEVANCY SCORE 3.35

    DB:3.35:How To Remove Double Quotes In Scripts? c1


    Hi,I'm running Oracle SQL Developer 4.0. When I script out a table, the owner, table name, column name, constraint name and etc are enclused with double quotes. I looked through Tools - Preferencesand was not able to find any option to turn it off. Does anyone know how to script a table without these quotes?Thank you

    DB:3.35:How To Remove Double Quotes In Scripts? c1

    Hi,I'm running Oracle SQL Developer 4.0. When I script out a table, the owner, table name, column name, constraint name and etc are enclused with double quotes. I looked through Tools - Preferencesand was not able to find any option to turn it off. Does anyone know how to script a table without these quotes?Thank you

  • RELEVANCY SCORE 3.30

    DB:3.30:Error Using Fully-Qualified Column Names pa


    Given a query "select * from x,y where . . .", I am getting a 17006 error "invalid column name" when I try to retrieve fields using

    rs.getXXX("x.col1");

    However,

    rs.getXXX("col1");

    works fine. Why isn't the field name qualified by the table recognized as valid?

    DB:3.30:Error Using Fully-Qualified Column Names pa

    Given a query "select * from x,y where . . .", I am getting a 17006 error "invalid column name" when I try to retrieve fields using

    rs.getXXX("x.col1");

    However,

    rs.getXXX("col1");

    works fine. Why isn't the field name qualified by the table recognized as valid?

  • RELEVANCY SCORE 3.29

    DB:3.29:Generating New Repository af


    We are using Designer9i and have an existing repository. We need to generate a new repository from the existing repository with the following modifications.

    1. For table names, add "NEW_" prior to the table name.
    2. Add a new column named with "N_" prior to the column if any column is NULLable.(Example: If ENAME column has NULL constraint in existing repository, then we need to bring over the ENAME column and also add one more NOT NULL column named as N_ENAME to the new repository)

    Is it possible with Designer API?

    Thank you,
    Prasad

    Please suggest me.

    DB:3.29:Generating New Repository af

    We are using Designer9i and have an existing repository. We need to generate a new repository from the existing repository with the following modifications.

    1. For table names, add "NEW_" prior to the table name.
    2. Add a new column named with "N_" prior to the column if any column is NULLable.(Example: If ENAME column has NULL constraint in existing repository, then we need to bring over the ENAME column and also add one more NOT NULL column named as N_ENAME to the new repository)

    Is it possible with Designer API?

    Thank you,
    Prasad

    Please suggest me.

  • RELEVANCY SCORE 3.23

    DB:3.23:Create Column Constraint m1


    How can i create a column constraint (Primary, Foreign, Not Null ... etc) Identified by a specific name in SQL ?

    Create table Test (
    EmpNo Number(10) Primary Key);

    How can i Identify this constraint with a spesific name ?

    DB:3.23:Create Column Constraint m1

    Instead of creating the constraints at
    column levels you can specify at table level
    and can give names for identification.
    e.g.
    Create table Test (
    EmpNo Number(10),
    constraint Test_PK Primary Key(EmpNo));

    Here Test_PK is the name of the constraint.
    I think you will get it.

    null

  • RELEVANCY SCORE 3.21

    DB:3.21:Help Me In Solving This m9


    i have two columns one column contains names and another customer nos, in name column names arerepeated.
    my task is to put the single names i.e. unique names in another table names column and customer nos of dat names in a comma delimited form
    names customer no
    sumith 12456,987456,148852,148524
    rasul 456879,45645,415646
    i have sorted the names and compared it ,if i find names are equal i have inserted it in target table but i am unable to insert nos in comma delimited form.
    plz help me

    DB:3.21:Help Me In Solving This m9

    Try
    select C.[Name], stuff((select ',' convert(varchar(10), [Customer No]) from Customers C2 where C2.[Name] = C.[Name]

    order by [Customer No] FOR XML PATH('')),1,1,'') as [Customer Nos]

    from Customers C GROUP BY [Name]

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

  • RELEVANCY SCORE 3.10

    DB:3.10:Foriegn Key mc


    Hi ,

    Am creating a table based on some integrity constraints, but it's not working.

    CREATE TABLE member
    (
    member_id NUMBER(10),
    last_name VARCHAR2(25) NOT NULL,
    first_name VARCHAR2(25),
    address VARCHAR2(100),
    city VARCHAR2(30),
    phone VARCHAR2(15),
    join_date DATE
    );

    alter table member add constraint mem_pk primary key(member_id);

    desc member

    CREATE TABLE title
    (
    title_id NUMBER(10),
    title VARCHAR2(60) NOT NULL,
    description VARCHAR2(400) NOT NULL,
    rating VARCHAR2(4)CONSTRAINT rat_chk CHECK(rating IN('G','PG','R','NC17','NR')),
    category VARCHAR2(20) CONSTRAINT cat_chk CHECK(category IN('DRAGMA','COMEDY','ACTION','CHILD','SCIFI','DOCUMENTRY')),
    release_date DATE
    );

    alter table title add constraint titid_pk primary key(title_id);

    desc title

    CREATE TABLE title_copy
    (
    copy_id NUMBER(10),
    title_id NUMBER(10),
    status VARCHAR2(15)NOT NULL CONSTRAINT stat_chk CHECK(status IN('AVAILABLE','DESTROYED','RENTED','RESERVED')),
    CONSTRAINT cpytitid_pk PRIMARY KEY(copy_id,title_id),
    CONSTRAINT tit_fk FOREIGN KEY(title_id) REFERENCES title(title_id)
    )

    desc title_copy

    CREATE TABLE rental
    (
    book_date DATE DEFAULT sysdate,
    member_id NUMBER(10),
    copy_id NUMBER(10),
    act_ret_date DATE,
    exp_ret_date DATE DEFAULT sysdate+2,
    title_id NUMBER(10),
    CONSTRAINT bkmemcpytit_pk PRIMARY KEY(book_date,member_id,copy_id,title_id),
    CONSTRAINT memcpytit_fk FOREIGN KEY(member_id) REFERENCES member(member_id)),
    CONSTRAINT memcpytt_fk FOREIGN KEY(copy_id) REFERENCES title_copy(copy_id),
    CONSTRAINT memcpyttt_fk FOREIGN KEY(title_id) REFERENCES title_copy(title_id)
    )

    I am struggle with rental table .. Please help me.

    Error:

    Error report:
    SQL Error: ORA-02270: no matching unique or primary key for this column-list
    02270. 00000 - "no matching unique or primary key for this column-list"
    *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
    gives a column-list for which there is no matching unique or primary
    key constraint in the referenced table.
    *Action: Find the correct column names using the ALL_CONS_COLUMNS
    catalog view

    DB:3.10:Foriegn Key mc

    One more thing - you can just use the TABLE NAME in the foreign key definition like below..

    But I would prefer using column names also..

    CREATE TABLE rental
    (
    book_date date default sysdate,
    member_id number(10),
    copy_id number(10),
    act_ret_date date,
    exp_ret_date date default sysdate+2,
    title_id number(10),
    constraint bkmemcpytit_pk
    primary key(book_date,member_id,copy_id,title_id),
    constraint memcpytit_fk
    foreign key(member_id)
    references member,
    constraint memcpytt_fk
    foreign key(copy_id,title_id)
    references title_copy
    );

  • RELEVANCY SCORE 3.10

    DB:3.10:Foreign Key Constraint Problem That I Can't Understand m3


    when i add foreign key constraint to table using toad or sqlplus or any other tool the constraint is added then when i open toad or any other thing to view the constraint i find the reference column not exist

    this is what happen
    when i write this statement

    --
    -- Foreign Key Constraints for Table CARRIER
    --
    ALTER TABLE MOCAPROJECT_AG.CARRIER ADD (
    CONSTRAINT CARRIER_ACTIVITY_TYPE_FK
    FOREIGN KEY (CR_TYPE_ID)
    REFERENCES MOCAPROJECT_AG.LOOKUP_AG_ACTVITY_TYPE(ACTIVITY_TYPE_ID));

    and after the statement executes with no errors and open toad or sql to view the constraint i find that

    --
    -- Foreign Key Constraints for Table CARRIER
    --
    ALTER TABLE MOCAPROJECT_AG.CARRIER ADD (
    CONSTRAINT CARRIER_ACTIVITY_TYPE_FK
    FOREIGN KEY (CR_TYPE_ID)
    REFERENCES MOCAPROJECT_AG.);

    u will see in the last line that *.LOOKUP_AG_ACTVITY_TYPE(ACTIVITY_TYPE_ID))* table name(column name) are deleted i don;t know why
    is this because this string is too long and if this is true what is the solution
    the two tables that i use to create the constraint are with no data

    thanks in advance

    DB:3.10:Foreign Key Constraint Problem That I Can't Understand m3

    thanks a lot for reply the problem is in toad . ops
    i searched a lot but didn't guess that

    thanks a lot for reply .

  • RELEVANCY SCORE 3.09

    DB:3.09:Problem Create Table With Primary Key Still In Trouble ! Please Help! a7



    Hi there !

    I use the orcle 8i, and i don't know why i can't create table with any primary key EXample:

    SQL CREATE TABLE O_caisses
    2 (No_caisse NUMBER(3) constraint caisses_pk PRIMARY KEY,
    3 NB_BILLETS NUMBER(5)
    4 )
    5 /
    CREATE TABLE O_caisses
    *
    ERROR at line 1:
    ORA-18008: cannot find OUTLN schema

    ***********some Debuger show me this way: *********************

    Well there r certain point u got to notice when creating a table with constraints.
    1) U can create table with COLUMN level constraint.
    2) U can create table with TABLE level constraint.
    3) In COLUMN level constraint u can't give a constraint a name
    but only mention the type of constraint.
    4) In TABLE level constraint u can give a name to constraint.

    Following are the examples of both

    --COLUMN LEVEL
    CREATE TABLE O_caisses
    (No_caisse NUMBER(3) PRIMARY KEY,
    NB_BILLETS NUMBER(5));

    --TABLE LEVEL
    CREATE TABLE O_caisses
    (No_caisse NUMBER(3),
    NB_BILLETS NUMBER(5),
    constraint pk_caisse primary key (No_caisse));

    ********************And this is another one:*****************

    SQLgrant create any outline to username;

    **************************************************************

    BUT the problem is still present, i don't know what to do now !
    Please could some body help me !

    Thanks alot!
    Luong.

    DB:3.09:Problem Create Table With Primary Key Still In Trouble ! Please Help! a7

    The clue is in the error message: the OUTLN schema is missing.

    This is something Oracle 8i introduced to help manage the CBO (or soemthing equally geeky and internal). For some reason your database no longer has this user. It ought to be created automatically during installation (or upgrade) but catproct may not have completed probably or some over zealous admin type has dropped it.

    Solution is to re-install (or re-upgrade) as you cannot create this user on their own. Alas.

    HTH, APC

  • RELEVANCY SCORE 3.07

    DB:3.07:Ora-2270 Error While Adding Foreign Key Constraint On Top A View 3k


    While adding a constraint, referencing a foreign key column from a view raises error. However, it works fine over a table.

    Here's the sample script:
    create table t_temp (
    sample_id number,
    text varchar2(40))
    ;

    alter table t_temp add constraint temp_pk
    primary key (sample_id);

    create view tempvw as select sample_id,text from t_temp;

    create table t_sample (
    uniq_id number,
    sample_id number,
    sample_text varchar(40));

    =========
    ALTER TABLE t_sample ADD CONSTRAINT FK_sample_temp
    FOREIGN KEY (sample_id) REFERENCES t_temp (sample_id);

    This works fine.
    =========

    ==============
    The following raises ERROR....

    ALTER TABLE t_sample ADD CONSTRAINT FK_sample_temp1
    FOREIGN KEY (sample_id) REFERENCES tempvw (sample_id);

    Error starting at line 1 in command:
    ALTER TABLE t_sample ADD CONSTRAINT FK_sample_temp1
    FOREIGN KEY (sample_id) REFERENCES tempvw (sample_id)
    Error report:
    SQL Error: ORA-02270: no matching unique or primary key for this column-list
    02270. 00000 - "no matching unique or primary key for this column-list"
    *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
    gives a column-list for which there is no matching unique or primary
    key constraint in the referenced table.
    *Action: Find the correct column names using the ALL_CONS_COLUMNS
    catalog view

    DB:3.07:Ora-2270 Error While Adding Foreign Key Constraint On Top A View 3k

    I didn't know about the limitation on Views. Thanks for your answers!

  • RELEVANCY SCORE 3.06

    DB:3.06:Can We Drop Check Constraint That Are Declared With Out Name 91


    Hi,
    I created table as follow
    CREATE TABLE ONE
    (
    ID INT,
    NAME VARCHAR(10)
    )
    ALTER TABLE ONE
    ADD CHECK (ID=1)
    Now can I drop this CHECH constraint on ID column?

    DB:3.06:Can We Drop Check Constraint That Are Declared With Out Name 91

    You may mark the thread as answered please. This would help others who reach here for a quick resolution.Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

  • RELEVANCY SCORE 3.05

    DB:3.05:Sql-Tab / Refresh Button: Does Not Take Into Account Renamed Constraint. mp


    I discovered a small bug:
    1) find or create a table with system generated constraint names (SYS_C007289)
    2) open that table in SQL Developer (I'm using 3.0.04.34). On the constraints tab, there will be the SYS% constraints. On the SQL-tab, the constraints will be shown without a name (simply "not null" for example).
    3) Return to the Constraints tab and use Actions / Constraints / Rename single to rename a constraint.
    4) return to the SQL tab and press the Refresh button. Unexpectedly, the new constraint name is still not in the SQL!
    5) close the entire window/tab for the table
    6) re-open that table.
    7) go to the SQL-tab: now the name of the constraint is in the code.

    As shown above, the workaround is easy, so this is a very minor bug-let.

    DB:3.05:Sql-Tab / Refresh Button: Does Not Take Into Account Renamed Constraint. mp

    I discovered a small bug:
    1) find or create a table with system generated constraint names (SYS_C007289)
    2) open that table in SQL Developer (I'm using 3.0.04.34). On the constraints tab, there will be the SYS% constraints. On the SQL-tab, the constraints will be shown without a name (simply "not null" for example).
    3) Return to the Constraints tab and use Actions / Constraints / Rename single to rename a constraint.
    4) return to the SQL tab and press the Refresh button. Unexpectedly, the new constraint name is still not in the SQL!
    5) close the entire window/tab for the table
    6) re-open that table.
    7) go to the SQL-tab: now the name of the constraint is in the code.

    As shown above, the workaround is easy, so this is a very minor bug-let.

  • RELEVANCY SCORE 3.05

    DB:3.05:Re: Foreign Keys j8


    Foreign key must refer unique or primary key from referenced table .

    ORA-02270: no matching unique or primary key for this column-list
    Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.

    Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view

    DB:3.05:Re: Foreign Keys j8

    So how would I go about dropping table 2 for example with a foreign key and primary key?

    when you say drop constaint constraint_name DBA is "constraint" an actual command? and what is the constraint_name?

    thanks

  • RELEVANCY SCORE 3.00

    DB:3.00:Is Data Loader Case Sensitive For Table/Column Names? pf


    Hi,

    I'm using APEX 4.1.1. I have simple table:

    CREATE TABLE "person"
    ("pkPerson" NUMBER,
    "name" NVARCHAR2(255) NOT NULL ENABLE,
    "email" NVARCHAR2(255) NOT NULL ENABLE,
    CONSTRAINT "pe__pkey" PRIMARY KEY ("pkPerson") ENABLE,
    CONSTRAINT "pe_name_idx" UNIQUE ("name") ENABLE,
    CONSTRAINT "pe_email_idx" UNIQUE ("email") ENABLE,
    CONSTRAINT "pe_name_email_idx" UNIQUE ("name", "email") ENABLE
    )

    I made data loader with APEX wizard to allow users to upload content of table. But any time I try to press 'Next' button on second page (mapping of input columns to table columns), I receive error:
    ORA-00904: "EMAIL": invalid identifier

    I investigated the problem and I found that when I create same table, but without case sensitive names of table and columns, same import pages are working well. When I checked shared component Data Load Tables, it shows case of letters correctly - name and email or NAME and EMAIL - depends on case of table.
    So my idea is that data loader doesn't support lower case names (preserve cases).
    Did anyone touch same situation or do anyone using data loader with lower case names?

    Sincerely yours,

    Jirka Novak

    DB:3.00:Is Data Loader Case Sensitive For Table/Column Names? pf

    Hello,

    Thanks for the details provided above, I just reviewed this and realized that this is definitely a deficiency and I filed a bug# 14061436. This will be fixed in coming release.

    Regards,
    Patrick

  • RELEVANCY SCORE 2.99

    DB:2.99:I Am Assuming That With Sql Server, If I Change The Name Of A Table That Has A Column That Is Constrained In Another Table, It Will Change Also In The Constraint, Right? cz


    If I have two tables, one is tbl_Users with a constraint to a column in another table called tbl_Users_Addresses. If I change the name of the table from tbl_Users_Addresses to UsersAddresses, will the constraint still work. In other words, if I change the
    name of a table with that is part of a constraint of another table, till the constraint still work?
    Thanks

    DCSSR

    DB:2.99:I Am Assuming That With Sql Server, If I Change The Name Of A Table That Has A Column That Is Constrained In Another Table, It Will Change Also In The Constraint, Right? cz

    Hi DCSSR,
    Yes the constraint will still work, Please try yourself by following script.
    CREATE TABLE tbl_Users (id INT PRIMARY KEY)

    CREATE TABLE tbl_Users_Addresses (
    id INT PRIMARY KEY
    ,Userid INT REFERENCES tbl_users(id)
    ,address CHAR(10)
    )

    Insert into tbl_Users values(1) --Will insert record
    Insert into tbl_Users_Addresses values(1,1,'abc') -- Will insert record
    Insert into tbl_Users_Addresses values(2,2,'def') -- Will generate error
    --Constraint Names
    Table Name of constraint

    tbl_Users, PK__tbl_User__3213E83F1367E606
    tbl_Users_Addresses PK__tbl_User__3213E83F173876EA,FK__tbl_Users__Useri__1920BF5C

    Renaming table.
    sp_rename tbl_Users_Addresses, UsersAddresses

    Insert into UsersAddresses values(2,1,'abc') -- Will insert record
    Insert into UsersAddresses values(2,2,'def') -- Will generate error

    --Constraint Names after renaming
    Table Name of constraint

    tbl_Users, PK__tbl_User__3213E83F1367E606
    UsersAddresses PK__tbl_User__3213E83F173876EA,FK__tbl_Users__Useri__1920BF5C

    Html Thanks
    Manish
    -----------------------------------------
    Please use Marked as Answer if my post solved your problem and use
    Vote if a post was useful Html

  • RELEVANCY SCORE 2.98

    DB:2.98:Table Names s3



    Hi,

    I want Table name where I can get the supplier name if supplier id is given and other table where Material name is retrieved if material group is given.

    Regards,

    Lakshmi

    DB:2.98:Table Names s3


    Hi

    for product category look in comm_prprdcatr, for supplier (Biznes Partner on document) look into crmd_partner.

    Regards

    Radek

  • RELEVANCY SCORE 2.97

    DB:2.97:Msg 2714, Duplicate Object Error f3


     
    Existing database (upgrade to SS2k5) has four tables with same primary key columns (UserID).
     
    Using CREATE TABLE with a specified [databasename].[dbo].[table1] schema is giving the following error message:
     

    Msg 2714, Level 16, State 4, Line 12
    There is already an object named 'UserID' in the database.
    Msg 1750, Level 16, State 0, Line 12
    Could not create constraint. See previous errors.
     
    I have duplicated the error with the following script:
     
    USE [testing]
    IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
    DROP TABLE [testing].[dbo].[users1]
    CREATE TABLE [testing].[dbo].[users1] (
    [UserID] bigint NOT NULL,
    [Name] nvarchar(25) NULL,
    CONSTRAINT [UserID] PRIMARY KEY (UserID)
    )
    IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
    DROP TABLE [testing].[dbo].[users2]
    CREATE TABLE [testing].[dbo].[users2] (
    [UserID] bigint NOT NULL,
    [Name] nvarchar(25) NULL,
    CONSTRAINT [UserID] PRIMARY KEY (UserID)
    )
    IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
    DROP TABLE [testing].[dbo].[users3]
    CREATE TABLE [testing].[dbo].[users3] (
    [UserID] bigint NOT NULL,
    [Name] nvarchar(25) NULL,
    CONSTRAINT [UserID] PRIMARY KEY (UserID)
    )
     
    I have searched the 2714 duplicate error msg, but have found references to duplicate table names, rather than multiple field names or column name duplicate errors within a database.
     
    I think that the schema is only allowing a single UserID primary key.
     
    How do I fix this?
     
    TIA

    DB:2.97:Msg 2714, Duplicate Object Error f3

    It's a duplicate constraint name, that must be unique -- got it.
     
    I don't take the internet for granted. I woke up this morning, checked my posts and there was the answer.
    How cool is that?
     
    My thanks to everyone for their help.

  • RELEVANCY SCORE 2.97

    DB:2.97:Foreign Key Naming Collision aa


    During an upgrade I needed to drop all the foreign keys references to a table, change the length of a column in that table and then reassociate the foreign keys. When trying to reassociate the foreign keys of a particular table, I got the following error:

    ALTER TABLE bwas.user_auth_ftr ADD FOREIGN KEY (user_uid) REFERENCES bwas.as_user (user_uid);

    907: Unique constraint (INDEXES.TBLID.IXNAME) violated at Rowid 0x00000000008260b8
    2213: Named index already exists on specified table

    Given that TimesTen names the foreign keys, how am I supposed to prevent this from happening? Is this a known issue?

    I looked in the sys.indexes table and the user_auth_ftr table had the following rows associated with it:

    SELECT RTRIM(A.tblowner), RTRIM(A.tblname), RTRIM(B.ixowner), RTRIM(B.ixname), B.ixid, B.isprimary FROM sys.tables A, sys.indexes B WHERE A.tblid = B.tblid AND A.tblname = 'USER_AUTH_FTR';

    BWAS, USER_AUTH_FTR, BWAS, IDXUUSER_AUTH_FTR01, 8638840, 00
    BWAS, USER_AUTH_FTR, BWAS, TTFOREIGN_80, 8638808, 00
    BWAS, USER_AUTH_FTR, BWAS, TTFOREIGN_81, 8638824, 00
    BWAS, USER_AUTH_FTR, BWAS, USER_AUTH_FTR, 8638792, 01

    There were other foreign key that were created before and after this particular one and I noticed that they were name TTFOREIGN_80 (before) and TTFOREIGN_82 which leads me to believe that it was attempting to use TTFOREIGN_81 as the name. Also I see that the sys.indexes table has a unique constraint on TBLID, IXNAME and IXOWNER.

    Any guidance would be greatly appreciated. I don't see how I am supposed to avoid this problem.

    DB:2.97:Foreign Key Naming Collision aa

    We have opened an issue with support and they are currently looking at it. Thanks.

  • RELEVANCY SCORE 2.97

    DB:2.97:Problem With Constraint Names zx


    Hi to all, I have a problem with the constraint names. I need to delete the table name that is used like prefix in the constraint name.

    Example:
    - Actual : DBO_MYDATABASE_FK_Ho_Ka
    - Desired : FK_Ho_Ka

    The constraint names are FK_Ho_Ka in the creation database script, but if I import the database schema, I have DBO_MYDATABASE_FK_Ho_Ka, but I only need the constraint name.

    Thanks so much :)

    DB:2.97:Problem With Constraint Names zx

    Thanks for your time :)

    I did a migration from Microsoft SQL Server 2005 to Oracle 11g database. Then, I need to use the same Entity Framework model to use with Sql Server and Oracle. Then, I need that the constraint names must be the same in the two databases.

    I did the migration with Oracle Sql Developer and all works fine. The resulted migration script has the constraint name like FK_UJ_UK (that it's OK) but when I import the database schema or do the Entity Framework model, I have this constraint name DBO_MYDATABASE_FK_UJ_UK.

    Edited by: Bluegene on Dec 11, 2008 9:35 AM

  • RELEVANCY SCORE 2.96

    DB:2.96:Table Name - Lower, Upper Case 7f


    Hi,

    Thank you for reading my post.
    --------------------------------------------------------------------------------------------------------------------------------
    1) I created a table:

    CREATE TABLE radios(name varchar2(20), frequency varchar2(20));

    Notice that the table name is lower case ("radios").
    --------------------------------------------------------------------------------------------------------------------------------
    2) I added a constraint on the "name" column:

    ALTER TABLE radios ADD CONSTRAINT name_pk PRIMARY KEY(name);
    --------------------------------------------------------------------------------------------------------------------------------
    3) Now I want to list the existing constraints for the table "radios".

    3.a) If I use the lower case table name, the result is empty:

    SQL SELECT constraint_name, constraint_type
    FROM user_constraints
    WHERE table_name = 'radios';

    no rows selected

    3.b) Now, if I use the upper case table name, I get what I am looking for:

    SQL SELECT constraint_name, constraint_type
    FROM user_constraints
    WHERE table_name = 'RADIOS';

    CONSTRAINT_NAME C
    NAME_PK P
    --------------------------------------------------------------------------------------------------------------------------------
    Can you explain me that phenomenon?
    I mean: I created a table name lower case so why does it work
    only with the upper case formulation?

    Do we have to create upper case table names consistently?
    How do you create table names: do you create them upper case?

    Thank you for your help.
    Sincerely,
    --
    Lmhelp

    DB:2.96:Table Name - Lower, Upper Case 7f

    I am sorry :/: I thought I did it but I forgot, you know... to do the whole procedure.
    I just clicked the "Correct" button and thought it was done.
    I did it too hastily I guess.
    Now it is done, thanks again.
    --
    Lmhelp

  • RELEVANCY SCORE 2.96

    DB:2.96:Drop System Generated Constraint s8


    Hi All,

    In a table I am having primary key called abc_pk for column. Mistakenly i have given the same name for foreign key as abc_pk and executed. As of me if same name for constraint then oracle will generate own constraint name such as sys_co4537.. if it is wrong correct me...

    if oracle itself generated constraint name then how to find constraint correct constraint name....

    case 1: if it single database i can find it in corresponding table with reference key..as (sys_co4537)
    case 2: If i am having more than 2 database then it difficult to find because each database will create different name...

    pls suggest me how to drop sys_* constraint from different database..

    Thanks Regards
    Sami

    DB:2.96:Drop System Generated Constraint s8

    Hi All,

    select * from USER_CONSTRAINTS
    where table_name=table_name

    by getting the constraint name, you can drop the constraint by

    alter table table name drop constraint constraint name;By using above statments user needs to select the constraint name, after that needs to drop it. Instead of that user needs to run as script.. so i have created as block script

    as

    DECLARE
    V_CONSTRAINT_NAME VARCHAR2(30);
    BEGIN
    SELECT CONSTRAINT_NAME INTO V_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'ABC' AND R_CONSTRAINT_NAME='ABC_PK';

    EXECUTE IMMEDIATE 'ALTER TABLE DEPOSIT_ACCOUNT_INTEREST DROP CONSTRAINT '|| V_CONSTRAINT_NAME;

    EXECUTE IMMEDIATE 'ALTER TABLE DEPOSIT_ACCOUNT_INTEREST ADD CONSTRAINT ABC_FK FOREIGN KEY (XXX_ID) REFERENCES DEPOSIT_ACCOUNT XXX_ID) DEFERRABLE INITIALLY DEFERRED';

    END;I think this might me usefull to some one..

    Thanks Regards
    Sami.

  • RELEVANCY SCORE 2.95

    DB:2.95:How To Find Primary Constraint Column mc


    How can i get the column names of the primary keys if i know the CONSTRAINT_NAME, and CONSTRAINT_TYPE = "P" in the user constraints of the table name.
    thanx in advance
    utsab

  • RELEVANCY SCORE 2.95

    DB:2.95:Lowcase Table Name And Column Name 3k


    In Oracle 10g, can i create a table with lowcase the table name and column names?

    Thanks!

    DB:2.95:Lowcase Table Name And Column Name 3k

    You can do it in Oracle earlier versions too.

    Jaffar

  • RELEVANCY SCORE 2.94

    DB:2.94:Referencing A Second Listobject Cell In Same Row By Column Name From A One Cell Range In Another Column? cz


    Seems simple but I'm not getting there trying lots of different syntax nor finding it via Google.
    I start with a one cell range found in a ListObject table:
    Set rngFoundCell = Range(Table1[KeyCalue]).Find(LookForValue), , xlFormulas, xlWhole)
    Now, given rngFoundCell, how do I find the value, or, more generally, a reference to the one cell Range, in some other column inrngFoundCell.Row by the other column's name? (I'm hoping for something moreelegant andsimpler than something
    like a Cell(0,offset) based on knowing the column numbers when coding or figuring them out by looking up column names in the ListObject?)
    I.e., what I'm looking for is the Object Model Rangeobject equivalent of worksheet structured reference formula =[@[ColumnX]] or =[[#ThisRow][ColumnX]] or plain old intersection =ColumnX.
    Thanks in advance for any assistance with this.

    DB:2.94:Referencing A Second Listobject Cell In Same Row By Column Name From A One Cell Range In Another Column? cz

    I haven't yet gotten to performance tuning the calling code where there are opportunities as you point out. I was more wondering about which method of getting to the othervaluesof the same row, by column name, was faster. So I tried some test
    cases.
    case 3:
    (calling object method)
    ...
    ret = Range(rngFoundCell, rngFoundCell(0, bench.AttrOffs(ColumnX)))
    ...
    (object method)
    ...
    AttrOffs = lstobjPriv.ListColumns(ColName).Index
    ...
    case 4:
    (calling object method)
    ...
    ret = bench.AttrIntersect(rngFoundCell, ColumnX)
    ...
    (object method)
    ...
    Set AttrIntersect = Intersect(lstobjPriv.ListColumns(ColName).DataBodyRange, rngKey.EntireRow)
    ...
    result of 10 million calls:
    Case 3 00:01:12.1212
    Case 4 00:02:02.022
    Interestingly, I tried a case 6 without all the object wrapping but fully qualified:
    ...
    ret = Intersect(ThisWorkbook.Worksheets(1).ListObjects(1).ListColumns(ColumnX).DataBodyRange, rngFoundCell.EntireRow)
    ...
    same 10 million calls:
    Case 6 00:02:44.4444

  • RELEVANCY SCORE 2.94

    DB:2.94:Changing The Labels Of The Column Name For Display xx


    --
    I have created a data frame using info bus data wizard and
    selected grid view .....the column names displayed for the grid
    control are those given at the time of creating tables ....I
    want to change the column name ie display a meaning full name to
    the columns and not those given at time of designing
    table ...how do I go about ...

    your sugesstion comments will be highly appreciated
    null

    DB:2.94:Changing The Labels Of The Column Name For Display xx

    Changing a column name for display is a basic requirement for any real system.

    If Oracle was in the car business, It wouldnt surprise me to see cars with no visable steering wheels or gas pedals. Oh..they would be on the car somewhere...just read the documentation!

  • RELEVANCY SCORE 2.94

    DB:2.94:Adding Custom Property In Catalog 11


    Hi all,

    trying to add a new property in the product and SKU level. I have added the below code in custom catalog.xml.

    And the below SQL script, i have got by invoking the following like in . In dyn/admin , atg/commerce/catalog/productcatalog , generate SQL link...

    but i am getting the following error when executing the SQL script.

    Pls let me know the best way to add a custom property in catalog, n generate SQL for same
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    item-descriptor name="product" id-space-name="product" xml-combine="append"
    table name="abc_product" type="auxiliary" id-column-name="product_id"
    property name="isABC" data-type="boolean" default="false" column-name="abc"
    category-resource="SIM" display-name-resource="abc"
    attribute name="propertySortPriority" value="-1"/
    /property
    /table

    /item-descriptor

    -------------------------------------------------------Error when executing script----------------------

    Error starting at line 1 in command:
    CREATE TABLE abc_product (
    product_id varchar2(254)NOT NULL REFERENCES dcs_product(product_id),
    abc number(1)NULL,
    CHECK (abc IN (0, 1)),
    PRIMARY KEY(product_id)
    )
    Error at Command Line:2 Column:61
    Error report:
    SQL Error: ORA-02270: no matching unique or primary key for this column-list
    02270. 00000 - "no matching unique or primary key for this column-list"
    *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
    gives a column-list for which there is no matching unique or primary
    key constraint in the referenced table.
    *Action: Find the correct column names using the ALL_CONS_COLUMNS
    catalog view

    DB:2.94:Adding Custom Property In Catalog 11

    Bravo

    Your are confusing me a bit here.
    First of all, i m not sure why all primary key constraints on your non versioned schema are not there. This is a problem. Maybe with the way you generated your schema. You need to look back once.

    Second i m not sure why you are trying to create dcs_product table in publishing schema. its OOB table??

  • RELEVANCY SCORE 2.94

    DB:2.94:Alias Scope Problem In Union xs


    I am using 11.2.0. I need to create a select to get a set of objects along with data from related tables for a dashboard-like display.

    Here are simple tables that demonstrate the problem:

    CREATE table operation
    (
    operation_key NUMBER(10) CONSTRAINT op_nn1 NOT NULL,
    name VARCHAR2(30),
    order_key number(10),
    CONSTRAINT pkO PRIMARY KEY(operation_key)
    );

    CREATE table oper_part
    (
    operation_key NUMBER(10) CONSTRAINT opp_nn1 NOT NULL,
    part_key NUMBER(10) CONSTRAINT opp_nn2 NOT NULL,
    CONSTRAINT pkOP PRIMARY KEY(operation_key, part_key),
    CONSTRAINT fkOP FOREIGN KEY(operation_key) REFERENCES operation(operation_key) ON DELETE CASCADE
    );

    create table step
    (
    step_key NUMBER(10) CONSTRAINT stp_nn1 NOT NULL,
    name VARCHAR2(30),
    operation_key number(10) CONSTRAINT stp_nn2 NOT NULL,
    CONSTRAINT pkS PRIMARY KEY(step_key),
    CONSTRAINT fkS FOREIGN KEY(operation_key) REFERENCES operation(operation_key) ON DELETE CASCADE
    );

    CREATE table step_part
    (
    step_key NUMBER(10) CONSTRAINT stpp_nn1 NOT NULL,
    part_key NUMBER(10) CONSTRAINT stpp_nn2 NOT NULL,
    CONSTRAINT pkS PRIMARY KEY(step_key, part_key),
    CONSTRAINT fkSP FOREIGN KEY(step_key) REFERENCES step(step_key) ON DELETE CASCADE
    );

    Here is a select that fails to parse:

    SELECT a.operation_key, a.name,
    (SELECT COUNT(*) FROM
    (SELECT op.part_key
    FROM oper_part op
    WHERE op.operation_key = a.operation_key
    UNION
    SELECT sp.part_key
    FROM step stp
    INNER JOIN step_part sp ON sp.step_key = stp.step_key
    WHERE stp.operation_key = a.operation_key)) bom_count
    FROM operation a
    WHERE a.order_key = 2
    ORDER BY a.operation_key;

    The error I get is:
    ORA-00904: "A"."OPERATION_KEY": invalid identifier
    00904. 00000- "%s: invalid identifier"
    *Cause:
    *Action:
    Error at line: 5 Column: 36

    I do not see how that can be out of scope. The alias is defined in the WHERE clause, and the column is valid. Is this from scoping rules that prohibit what I'm trying to do, or is it a bug? If from scoping rules, where can I find that documented, and is there a work-around?

    Thanks in advance for your help.

    Sincerely,

    Jim Brandley

    DB:2.94:Alias Scope Problem In Union xs

    Thanks for the example. I tried to produce one but just confused myself more that I had been.

  • RELEVANCY SCORE 2.94

    DB:2.94:How To Get The Column Name And Table Name With Value k1


    Hi All

    I have one difficult requirement

    I have some column values and they have given some alias column names but i need to find the correct column name and table name from the database.

    For example value is "SRI" and i dont know the table and exact column name so is there any possibilities to find the column name and table name for the given value

    Thanks Regards
    Srikkanth.M

    DB:2.94:How To Get The Column Name And Table Name With Value k1

    Thanks for all your kind response, ill have find it.

    They have given a value as ULC ref 254 this means agreement id in my application from that i need to find another 5 column form database so i tried by getting the primary and foreign key concept.

    Thanks Regards
    Srikkanth.M

  • RELEVANCY SCORE 2.93

    DB:2.93:Fillschema Picks Too Many Primary Key Columns sf


    I don't know whether this is an ODP.NET error or a Microsoft error.

    Oracle9i Release 9.2.0.1.0
    ODP.NET 9.2.0.4
    .NET Framework 1.1

    Create a table with one primary key column and one unique column and name the primary key column like the unique column with the suffix "_ID":

    CREATE TABLE t_bib_uebertrag_kap(
    nbsp kapazitaet_id NUMBER(8) NOT NULL
    nbspnbsp CONSTRAINT pk_kapa primary key,
    nbsp kapazitaet VARCHAR2(15) NOT NULL
    nbspnbsp CONSTRAINT a_un_kapa unique,
    nbsp geschwindigkeit NUMBER(12) NOT NULL,
    nbsp bemerkung VARCHAR2(255)
    );

    After calling FillSchema on this table the PrimaryKey property of the DataTable contains 2 columns: KAPAZITAET_ID and KAPAZITAET.

    The same happens with other tables and similar column names

    R. Lthke

    DB:2.93:Fillschema Picks Too Many Primary Key Columns sf

    I don't know whether this is an ODP.NET error or a Microsoft error.

    Oracle9i Release 9.2.0.1.0
    ODP.NET 9.2.0.4
    .NET Framework 1.1

    Create a table with one primary key column and one unique column and name the primary key column like the unique column with the suffix "_ID":

    CREATE TABLE t_bib_uebertrag_kap(
    nbsp kapazitaet_id NUMBER(8) NOT NULL
    nbspnbsp CONSTRAINT pk_kapa primary key,
    nbsp kapazitaet VARCHAR2(15) NOT NULL
    nbspnbsp CONSTRAINT a_un_kapa unique,
    nbsp geschwindigkeit NUMBER(12) NOT NULL,
    nbsp bemerkung VARCHAR2(255)
    );

    After calling FillSchema on this table the PrimaryKey property of the DataTable contains 2 columns: KAPAZITAET_ID and KAPAZITAET.

    The same happens with other tables and similar column names

    R. Lthke

  • RELEVANCY SCORE 2.93

    DB:2.93:Info Of Fk 1f


    how can i retrieve referenced table and corresponding column with a given constraint_name which is a freign key constraint by SQL?

    DB:2.93:Info Of Fk 1f

    SET LINESIZE 100
    ACCEPT name_of_constraint -
    PROMPT 'Enter constraint name: '
    SELECT parent_table,
    parent_column,
    child_table,
    child_column
    FROM (SELECT SUBSTR (b.table_name, 1, 20) parent_table,
    d.position,
    SUBSTR (d.column_name, 1, 20) parent_column
    FROM all_constraints a,
    all_constraints b,
    all_cons_columns d
    WHERE a.r_constraint_name = b.constraint_name
    AND b.constraint_name = d.constraint_name
    AND a.constraint_name = UPPER ('name_of_constraint')) x,
    (SELECT SUBSTR (a.table_name, 1, 20) child_table,
    c.position,
    SUBSTR (c.column_name, 1, 20) child_column
    FROM all_constraints a, all_cons_columns c
    WHERE a.constraint_name = c.constraint_name
    AND a.constraint_name = UPPER ('name_of_constraint')) y
    WHERE x.position = y.position
    ORDER BY x.position
    /
    UNDEFINE name_of_constraint

    The script above will produce output like:

    PARENT_TABLE PARENT_COLUMN CHILD_TABLE CHILD_COLUMN
    ------------- ------------- ------------- -------------

  • RELEVANCY SCORE 2.90

    DB:2.90:Hibernate - Spring - Problem With Mapping (Many-To-Many) sf


    Hello,

    I want to map the following situation. I have a table called EDUCATION and a table called SCHOOLS. Between those tables I have an associative table called EDUCATION_SCHOOLS. The (usefull) fields:

    EDUCATION:
    id (long) - PK
    name (varchar)
    versionNr (long)

    SCHOOLS:
    id (long) - PK
    name (varchar)
    versionNr (long)

    EDUCATION_SCHOOLS:
    id (long) - PK
    education_id (long) (FK to EDUCATION.id)
    school_id (long) (FK to SCHOOLS.id)
    name (varchar)
    versionNr (long)

    Their is a Unique Constraint between EDUCATION_SCHOOLS.education_id and EDUCATION_SCHOOLS.school_id.

    What I want to be able to do:
    EDUCATION: select, update, insert
    SCHOOLS: select, update, insert
    EDUCATION_SCHOOLS: select, update (only the non-FK fields), insert

    I never want to delete anything in those tables. (and it's never ever going to be an option either)

    Hibernate version:
    Hibernate-Version: 3.0.5

    Mapping documents:
    Education:
    hibernate-mapping
    class name="##.Education" table="EDUCATION"
    id name="id" column="ID" type="java.lang.Long"
    generator class="sequence"
    param name="sequence"EDUCATION_SEQ/param
    /generator
    /id

    version name="versionNr" column="VERSIONNR" type="long"/

    property name="name" column="NAME" type="string" /

    set name="SCHOOLS" table="EDUCATION_SCHOOLS"
    key column="EDUCATION_ID" /
    many-to-many class="##.Schools" column="SCHOOL_ID" lazy="false" /
    /set
    /hibernate-mapping

    Schools:
    hibernate-mapping
    class name="##.Schools" table="SCHOOLS"
    id name="id" column="ID" type="java.lang.Long"
    generator class="sequence"
    param name="sequence"SCHOOLS_SEQ/param
    /generator
    /id

    version name="versionNr" column="VERSIONNR" type="long"/

    property name="name" column="NAAM_NAME" type="string" /

    set name="educations" table="EDUCATION_SCHOOLS" inverse="true" cascade="none"
    key column="SCHOOL_ID" /
    many-to-many class="##.Schools" column="SCHOOL_ID" lazy="proxy"/
    /set
    /hibernate-mapping

    Education_schools:
    hibernate-mapping
    class name="##.EducationSchools" table="EDUCATION_SCHOOLS"
    id name="id" column="ID" type="java.lang.Long" unsaved-value="0"
    generator class="sequence"
    param name="sequence"SEQ_EDUCATION_SCHOOLS/param
    /generator
    /id

    version name="versionNr" column="VERSIONNR" type="long" /

    many-to-one name="education" class="##.Education" cascade="none" lazy="proxy"
    column="EDUCATION_ID" not-null="true"/
    many-to-one name="schools" class="##.Schools" cascade="none" lazy="proxy"
    column="SCHOOL_ID" not-null="true"/
    /hibernate-mapping Name and version of the database you are using:
    Oracle XE 10g

    I am able to:
    EDUCATION: select, insert, update
    SCHOOLS: select, insert, update
    EDUCATION_SCHOOLS: select

    Problems:
    EDUCATION_SCHOOLS: when I try to insert, I sometimes get unique constraint violations. (when I should get them, thus I'm trying to insert something that already exists .. but how do I stop Hibernate from Inserting?)

    EDUCATION_SCHOOLS: when I try to update, sometimes it works, but often I get:

    23:03:55,484 [http-8081-1] ERROR be.vlaanderen.lne.vea.epb.ui.struts.EpbExceptionHandler - org.springframework.orm.hibernate3.HibernateOptimisticLockingFailureException: Object of class [##.EducationSchools] with identifier [null]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [##.EducationSchools#null]
    ex.getMessage() Object of class [##.EducationSchools] with identifier [null]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [##.EducationSchools#null]
    org.springframework.orm.hibernate3.HibernateOptimisticLockingFailureException: Object of class [##.EducationSchools] with identifier [null]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [##.EducationSchools#null]
    Caused by: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect):As you can see from the stacktrace I use Spring for the transactionManager: org.springframework.orm.hibernate3.HibernateTransactionManager in which I use e sessionFactory: org.springframework.orm.hibernate3.LocalSessionFactoryBean

    In my DAO, I try to save with the regular this.getHibernateTemplate().saveOrUpdate that has always worked for me.

    Another problem I have:
    when i update "name" in EDUCATION, the records with that ID are delete from EDUCATION_SCHOOLS ...

    As I am experiencing 3 different problems, I'm pretty sure something is wrong in the mapping files .. however I fail to find out what .. Any input would be greatly appreciated.

    (I translated some class/table-names, that's what the ## cause)

    Edited by: Bart_Blommaerts on Jul 29, 2008 11:53 PM

    DB:2.90:Hibernate - Spring - Problem With Mapping (Many-To-Many) sf

    dev2000: thank you for your input. Meanwhile I have solved the problem.

    If anyone ever finds this topic:

    - ConstraintFailedException
    Hibernate doesn't check constraints. You have to check yourself if a duplicate exists before you insert a new record. This can be done easily by selecting the record you want to insert.

    - Delete when updating
    Put inverse="true" in EDUCATION and SCHOOLS. No delete happens.

    - HibernateOptimisticLockingFailureException
    When I try to update, I select the record I try to update first. Probably not the best solution, but it's a working solution.

  • RELEVANCY SCORE 2.90

    DB:2.90:How To Get The Constraintlist And Column Name In A Table? xd


    Hi,

    can anyone please tell me how to get the constraint type ,and it's imposed on columns names in a table.

    Thanks,
    Deekay

    DB:2.90:How To Get The Constraintlist And Column Name In A Table? xd

    Hi,

    use both tables:

    select * from all_constraints

    select * from ALL_CONS_COLUMNSsorry SY, you were faster than me...

    Edited by: user11268895 on Aug 19, 2010 1:27 PM

  • RELEVANCY SCORE 2.89

    DB:2.89:Error Importing Table Metadata With Quoted Column Names z7


    Hey folks,

    I have a strange problem with the table metadata import wizard. It refuses to import column names that are quoted. I am using OWB 11.1.0.6 on Oracle 11g/Linux

    For example, given this table
    create table mytest (id integer, "Name" varchar2(50))

    OWB imports the ID column just fine but chokes on the "Name" column ("Created with second class object failure").

    However if I define the table as
    create table mytest (id integer, Name varchar2(50))

    everything goes smoothly. Does anyone know if this is a bug or a feature? Can't find any information on this, and it's highly annoying as I'm importing definitions of automatically generated tables that contain tons of these quoted column names :(.

    I appreciate your help!!

    Thanks,
    Stefan

    DB:2.89:Error Importing Table Metadata With Quoted Column Names z7

    Hey folks,

    I have a strange problem with the table metadata import wizard. It refuses to import column names that are quoted. I am using OWB 11.1.0.6 on Oracle 11g/Linux

    For example, given this table
    create table mytest (id integer, "Name" varchar2(50))

    OWB imports the ID column just fine but chokes on the "Name" column ("Created with second class object failure").

    However if I define the table as
    create table mytest (id integer, Name varchar2(50))

    everything goes smoothly. Does anyone know if this is a bug or a feature? Can't find any information on this, and it's highly annoying as I'm importing definitions of automatically generated tables that contain tons of these quoted column names :(.

    I appreciate your help!!

    Thanks,
    Stefan

  • RELEVANCY SCORE 2.89

    DB:2.89:Create Table Duplicate Object/Duplicate Field Name Error Msg 2714 a9


    Hello Everyone:
     
    I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
     
    When I try to create the tables, for the query, I am getting the following error:
     

    Msg 2714, Level 16, State 4, Line 12
    There is already an object named 'UserID' in the database.
    Msg 1750, Level 16, State 0, Line 12
    Could not create constraint. See previous errors.
     
    I have duplicated this error with the following script:
     
    USE [testing]
    IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
    DROP TABLE [testing].[dbo].[users1]
    CREATE TABLE [testing].[dbo].[users1] (
    [UserID] bigint NOT NULL,
    [Name] nvarchar(25) NULL,
    CONSTRAINT [UserID] PRIMARY KEY (UserID)
    )
    IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
    DROP TABLE [testing].[dbo].[users2]
    CREATE TABLE [testing].[dbo].[users2] (
    [UserID] bigint NOT NULL,
    [Name] nvarchar(25) NULL,
    CONSTRAINT [UserID] PRIMARY KEY (UserID)
    )
    IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
    DROP TABLE [testing].[dbo].[users3]
    CREATE TABLE [testing].[dbo].[users3] (
    [UserID] bigint NOT NULL,
    [Name] nvarchar(25) NULL,
    CONSTRAINT [UserID] PRIMARY KEY (UserID)
    )
     
    I have searched the 2714 duplicate error msg, but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
     
    I think that the schema is only allowing a single UserID primary key.
     
    How do I fix this?
     
    TIA
     

    DB:2.89:Create Table Duplicate Object/Duplicate Field Name Error Msg 2714 a9


    /****** Object: StoredProcedure [dbo].[NIC_OA_GetPatientXRayReportsOut] Script Date: 03/27/2012 18:18:31 ******/
    Please assist me i am having the same kind of error

    IF

    EXISTS(SELECT
    *
    FROM
    sys.objects
    WHERE
    object_id
    =
    OBJECT_ID(N'[dbo].[NIC_OA_GetPatientXRayReportsOut]')
    AND
    type
    in(N'P',
    N'PC'))

    DROP

    PROCEDURE [dbo].[NIC_OA_GetPatientXRayReportsOut]

    GO

    /****** Object: StoredProcedure [dbo].[NIC_OA_GetPatientXRayReportsOut] Script Date: 03/27/2012 18:18:31 ******/

    SET

    ANSI_NULLS
    ON

    GO
    SET

    QUOTED_IDENTIFIER
    ON

    GO

    create

    PROCEDURE [dbo].[NIC_OA_GetPhysicianXRayReportsOut]
    @lPhysician INT,

    @Page

    INT=1,

    @RecsPerPage

    INT=50

    AS

    SET
    TRANSACTION
    ISOLATION
    LEVEL
    READ
    COMMITTED

    SET
    NOCOUNT
    ON



    CREATE
    TABLE #TempItems

    (

    ID

    INT
    IDENTITY,

    lID

    INT
    NOT
    NULL

    )


    INSERT
    INTO #TempItems

    (lID)

    --SELECT lID FROM tblItem

    SELECT a.lid

    FROM [PhysicianXRayRequisitions] a

    INNER
    JOIN [Map_XRayRequisitionToProgressNote] b

    ON a.lID
    = b.lXRayRequisition

    INNER
    JOIN [ProgressNote] c

    ON c.lID
    = b.lProgressNote

    INNER
    JOIN patient d

    ON c.lPatient
    = d.lID

    WHERE a.lPhysician
    = @lPhysician

    AND a.nRecordStatus
    = 1

    AND a.bReportReceived
    = 0

    AND a.bReportRemoved
    = 0

    AND a.szLastPrintedBy
    IS
    NOT
    NULL

    ORDER
    BY a.dDateOrdered
    ASC


    -- Find out the first and last record we want

    DECLARE @FirstRec
    INT,

    @LastRec

    INT


    SELECT @FirstRec
    =(
    @Page - 1
    )
    * @RecsPerPage


    SELECT @LastRec
    =(
    @Page * @RecsPerPage
    1
    )




    SELECT
    Isnull(b.szLaboratory,
    'non selected')
    AS szLaboratory,

    a

    .lID,

    a

    .bDiagnosticMammogram,

    a

    .bScreeningMammogram,

    a

    .dDateOrdered,

    a

    .dCheckForReport,

    p

    .szLast,

    p

    .szFirst,

    a

    .lPhysician,

    p

    .szFirst
    AS szPhysicianFirst,

    p

    .szLast
    AS szPhysicianLast,

    szLastPrintedBy

    ,

    pat

    .szFirst
    AS szPatFirst,

    pat

    .szLast
    AS szPatLast,

    pat

    .lid
    AS lPatient,

    Rtrim(Isnull(DIform.szFileName,
    ''))
    AS szDIFormFileName,

    a

    .lDIform_data,

    DIform_data

    .lDIform,

    a

    .szListRequisition_FreeForm,

    Isnull(f.FormLiteFormID,
    0)
    AS FormLiteFormID,

    f

    .FormLiteSnapshotID,

    TotalRecords

    =(SELECT
    Count(*)
    FROM #TempItems TI)

    FROM #TempItems

    Inner
    join [PhysicianXrayRequisitions] a

    ON a.lid
    = #TempItems.lID

    LEFT
    JOIN [Laboratory] b

    ON b.lID
    = a.lLaboratory

    LEFT
    JOIN FormLiteSnapshotMap f

    ON a.lid
    = f.lPhysicianXRayRequisitions

    INNER
    JOIN [Map_XRayRequisitionToProgressNote] c

    ON c.lXRayRequisition
    = a.lID

    INNER
    JOIN [ProgressNote] d

    ON c.lProgressNote
    = d.lID

    INNER
    JOIN [Patient] pat

    ON d.lPatient
    = pat.lID

    INNER
    JOIN [Physician] p

    ON p.lid
    = a.lPhysician

    LEFT
    JOIN DIform_data

    ON DIform_data.lid
    = a.lDIform_data

    LEFT
    JOIN DIform

    ON DIform.lid
    = DIform_data.lDIform

    WHERE #TempItems.ID
    @FirstRec

    AND #TempItems.ID
    @LastRec

    ORDER
    BY #TempItems.ID

    SET
    NOCOUNT
    OFF

    k

  • RELEVANCY SCORE 2.89

    DB:2.89:Oracle 9i Primary And Foreign Key Constraint 1d


    I had created table in the following way

    create table dept(deptno number,dname varchar2(20));
    create table emp(empno number primary key,ename varchar2(25), deptno number references dept(deptno));

    One can get constraint name and constraint type from user_constraints table. But I want to know name of the column of table from which column is used as foreign key in another table. How to get such information?

    DB:2.89:Oracle 9i Primary And Foreign Key Constraint 1d

    You need to join with user_constraints:
    SELECT *
    FROM user_constraints a, user_cons_columns b
    WHERE a.constraint_name = b.constraint_name
    AND a.constraint_type = 'R'
    AND b.table_name = :my_table
    AND b.column_name = :my_columnUrs

  • RELEVANCY SCORE 2.89

    DB:2.89:Re: What Am I Doing Wrong On This Sql File??? d8


    in oracle constraints are a separate object in itself. and you cannot have 2 different objects with the same name.

    in your first create table statement you have given the name for the primary key constraint as pk_cust. and the same name is given in the second create table statement also, which is causing the error.

    give unique names for each constraint.

    DB:2.89:Re: What Am I Doing Wrong On This Sql File??? d8

    Thanks guys. I will make revisions and run again.

    Canaan

  • RELEVANCY SCORE 2.88

    DB:2.88:Renaming Column jp


    Hi,

    I want to rename a column in a table and at the same time I also want to change the check constraint
    of the same column to add the option 2 also.

    below is my rename column query:-

    alter table C2UD_CLIENT_H rename column b_restructured to restructured_opt_id

    In the above query, I also want to include the changed check constraint.

    The old check constraint is as given below :-
    add check (b_restructured in (0,1));

    The new constraint required is:-
    (b_restructured in (0,1,2)

    How can i achieve the above ?

    DB:2.88:Renaming Column jp

    Hi,

    I have a table t_test as

    SQL create table t_test(abc number);

    Table created.

    I added a constraint in it

    SQL alter table
    2 t_test
    3 add constraint
    4 c_t
    5 CHECK
    6 (abc IN ( 1,2));

    Table altered.

    I rename the column abc to xyz

    SQL alter table t_test rename column abc to xyz;

    Table altered.

    Now, as per your scenerio if I try to modify the consraint and rename the column simeltanuoesly I get following error

    SQL alter table
    2 t_test
    3 rename column xyz to abc,
    4 modify constraint
    5 c_t
    6 CHECK
    7 (abc IN ( 1,2,3));
    alter table
    *
    ERROR at line 1:
    ORA-23290: This operation may not be combined with any other operationThis is because both the operations are mutually exclusive. Hence need to follow them step wise.

    Twinkle

  • RELEVANCY SCORE 2.88

    DB:2.88:How Do I Get The Names (Given Titles) To Statistic Created On A Db m9


    I'm trying to figure out how to get the name of statistics created on the AdventureWorks sample database.
    For example, if someone other than myself created a statistic against a column in let's say the HumanReasources.Employee table, how would I find out the name of that statistic?

    Example: CREATE STATISTIC st_MaritalStatus ON HumanReasources.Employee (MaritalStatus) WITH FULLSCAN;

    How can I find out the name st_MaritalStatus

    Rob

    DB:2.88:How Do I Get The Names (Given Titles) To Statistic Created On A Db m9

    Thanks guys,
    That did the trick. I appreciate your guys time and expertise. Hope I can be as good as you one day.

    Rob

  • RELEVANCY SCORE 2.88

    DB:2.88:Migration Microsoft Sql Sever 2005 To Oracle 11g Constraint Names Problem 3p


    Hi, I have a little problem migrating a Sql Server to Oracle database. Oracle Sql Developer formats the constraint names putting the table space name like prefix.

    Example:
    - Original constraint name : FK_Ho_Ka
    - Formatted constraint name : DBO_MYDATABASE_FK_Ho_Ka

    Could somebody help me to avoid this? Thanks so much :)

    DB:2.88:Migration Microsoft Sql Sever 2005 To Oracle 11g Constraint Names Problem 3p

    Hi,

    Unfortunately , this is not in this forum you can get an answer to this problem.
    Please reformulate your problem in a new hint because there is no link with migration.
    What you seem to reproduce is often due to an option which allows to prefix the objects with schema name.
    Goal is to find the option...

    Regards

    Mireille

  • RELEVANCY SCORE 2.87

    DB:2.87:How To Get Constraint Name And Table And Column Names pk


    Hi Team,

    I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint.

    I used dba_constraints view. Please find below details.

    SQL select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints
    2 where TABLE_NAME='DSET_PRODUCT_S';

    OWNER CONSTRAINT_NAME C TABLE_NAME
    ------------------------------ ------------------------------ - ----------------------------------------
    SCOTT SYS_C10202456 C EMPLOYEE
    SCOTT SYS_C234576 C DEPT

    Please suggest any one.

    Regards,

  • RELEVANCY SCORE 2.87

    DB:2.87:Accessing Multi-Column-Name Value In Java kx


    In the below table, I need to access 'card_key' in java file. Is it possible?item-descriptor name="user" default="true" table name="usr_tbl" type="primary" id-column-names="id" property name="id" data-type="string"/ property name="name" column-names="nam_col" data-type="string"/ property name="age" column-names="age_col" data-type="int"/ /table table name="credit_card_tbl" type="multi" id-column-names="id" multi-column-name="card_key" property name="card_num" column-names="card_num" data-type="map" component-data-type="string"/ /table /item-descriptor

    DB:2.87:Accessing Multi-Column-Name Value In Java kx

    With the xml you provided. TABLE "usr_tbl" is a primary table, with primary key "id" along with additional columns "name" and "age".TABLE "credit_card_tbl" will have a concatenated primary key of "id" (FK) and "card_key" along with an additional column "card_num".When you interpret in Java:Item-descriptor name "user" is the name of your Java Object. having properties - id,name,age,card_num.RepositoryItem userInfo = getProfile().getPropertyValue("user"); //Say you get repositoryItem of user from profileMap cardNumber = (Map)userInfo.getPropertyValue("card_num"); //Once u have the "user" object, get property card_num from it."cardNumber" is a map. Get the "card_key" from it. Map contains: card_key (key) and card_num (value).Mark answers as helpful/correct for other user's future reference. Thanks!

  • RELEVANCY SCORE 2.87

    DB:2.87:Recognizing / Creating Constraints On Table. 9a


    Hello,
    i am wondering why when i write:

    create table xxx (
    id varchar2(40)
    );

    alter table XXX add constraint XXX_ID_NN check (ID is not null);

    and:

    create table xxx (
    id varchar2(40) constraint XXX_ID_NN not null
    );

    aren't the same in DDL, i know that both of DDL realize the same,
    but i see differences when i type
    desc XXX

    how can i create column constraint via ALTER TABLE to see
    that one column can't be null, when i type desc MY_TABLE_NAME?

    second,
    how can i recognize that one constraint (check) in database was created
    by alter table or by column constraint?

    i am writting DDL extractor for logged user and i have no idea
    how to solve problem of duplicated constraints clausule, when i determine
    that column can't be null i add constraint to column (colum_constraint),
    and then when i want to create DDL for table constraint's (check)
    for other cases that column_not is not null i got all constrains inluding
    constraints defined in column_constraints.

    example:

    CREATE TABLE COMPANY (
    ID NUMBER(18) CONSTRAINT xx_1 NOT NULL,
    NAME VARCHAR2(70) CONSTRAINT xx_2 NOT NULL,
    STREET VARCHAR2(70) CONSTRAINT xx_3 NOT NULL,
    NR VARCHAR2(20),
    POSTCODE VARCHAR2(18) CONSTRAINT xx_4 NOT NULL,
    CONSTRAINT COMPANY_PK PRIMARY KEY (ID) USING INDEX TABLESPACE "INDX"
    ) TABLESPACE "USERS";

    -- consttraints for table type 'C'
    ALTER TABLE COMPANY ADD CONSTRAINT xx_1 CHECK ("ID" IS NOT NULL);
    ALTER TABLE COMPANY ADD CONSTRAINT xx_2 CHECK ("NAME" IS NOT NULL);
    ALTER TABLE COMPANY ADD CONSTRAINT xx_3 CHECK ("STREET" IS NOT NULL);
    ALTER TABLE COMPANY ADD CONSTRAINT xx_10 CHECK ("CITY" IS NOT NULL);

    ALTER TABLE COMPANY ADD CONSTRAINT xx_10 CHECK (other constraint, ie. when citty filled then require postcode or something like that );

    and so on.

    DB:2.87:Recognizing / Creating Constraints On Table. 9a

    Does this help:

    SQL create table test (testcol varchar2(10));

    Table created.

    SQL alter table test modify testcol not null;

    Table altered.

  • RELEVANCY SCORE 2.87

    DB:2.87:Table Level And Column Level Constraint am


    Hi,
    which is more efficient n recommended? column level or table level constraint?

    thanks

    DB:2.87:Table Level And Column Level Constraint am

    Please refer the link i provided above in my earlier post, it will make you think.

    Thanks,
    Jithendra

  • RELEVANCY SCORE 2.87

    DB:2.87:Viewing The Check Constraint Details Given In A Table sf


    How to view the detail of a check constraint.

    To make t clear... I have a column where i have given a check constraint with condition between 0 and 100.From which table i can view the details of the condition given after igetting the constraint name from the table all_cons_columns

    DB:2.87:Viewing The Check Constraint Details Given In A Table sf

    Or,

    select * from all_constraints
    where owner = 'SCOTT'
    and table_name = 'EMP';Regards.

    Satyaki De.

  • RELEVANCY SCORE 2.86

    DB:2.86:Problem Simply Stated 89


    Hi,
    I just need to know how to move from node 'table-name' to node 'data-element', to node 'column-name'.
    My purpose here is to get the nodevalue of 'column-name' when i know the 'table-name' nodevalue(simply put- i need the column names of a table)

    table-namehist_client/table-name
    data-element
    column-namecamp_id/column-name
    column-nameccl_id/column-name
    /data-element

    Help would be so appreciated!
    Thanx

    DB:2.86:Problem Simply Stated 89

    this is unfortunate that the XML DTD is so poorly designed.

    the following structure would make much more sense and would simplify your job:

    table name="hist_client"
    data-element
    column-namecamp_id/column-name
    column-nameccl_id/column-name
    /data-element
    /table

  • RELEVANCY SCORE 2.86

    DB:2.86:Column - Table Name Requied Paxineag Form ac


    HI ALL,

    i want to find out the column and table names which store Summary Amounts, Net Revenue, Allocated, Not baselined and Invoiced Amount fields in Agreements ( PAXINEAG)

    Record history show ap_agreements_v and examine gives the field name, but those are not present in ap_agreements_v

    Please suggest

    Regards,
    SK

    DB:2.86:Column - Table Name Requied Paxineag Form ac

    Hi John Dickey

    Yes this indeed helped me out
    Thanks !!

    I was wondering if you could let me know the table linking the Main Invoice and corresponding the TDS standard invoices generated who payment is done to the authority.
    I want to get the list of all the main invoices whos TDS generated invoice payment is done. Main voices I fetch from ap_invoices_v

    Please suggest.

    Thanks
    SK

  • RELEVANCY SCORE 2.86

    DB:2.86:How Can I Modify The Check Constraint In The Table zs


    How can i modify the check constraint in the table. This table containts check constraint, condition is code_value between 1 and 4. codevalue is column name.
    Please suggest me.

    DB:2.86:How Can I Modify The Check Constraint In The Table zs

    Go to tahiti.oracle.com, pick version browse reference manual and see the alter table statement
    Also read the goal of this forum.
    Your question is more appropriate to Database general forum
    General Database Discussions

    Gints Plivna
    http://www.gplivna.eu

  • RELEVANCY SCORE 2.86

    DB:2.86:Double Quotes Around Values km


    Is there any option to exclude the double quotes in the create table syntax around the schema name, table name, column names, constraint names, in the comment syntax around the schema and object name and so on? I had hoped to find that as a preference but I don't see it.

    thanks for your help.

    DB:2.86:Double Quotes Around Values km

    Is there any option to exclude the double quotes in the create table syntax around the schema name, table name, column names, constraint names, in the comment syntax around the schema and object name and so on? I had hoped to find that as a preference but I don't see it.

    thanks for your help.

  • RELEVANCY SCORE 2.86

    DB:2.86:Sql Server Ce 3.5 Sp2 Beta: Problem With Table Which Has Identity Column k3


    I created following table (thru SQL Server 2008 R2 Nov CTP):
    CREATE TABLE ReportPeriod
    (
    ReportPeriodId int IDENTITY (1, 1),
    Month tinyint NOT NULL,
    Year smallint NOT NULL,
    CONSTRAINT PK_ReportPeriodId PRIMARY KEY (ReportPeriodId),
    CONSTRAINT UQ_Year UNIQUE (Year)
    );
    and tried to insert following data:
    INSERT INTO ReportPeriod (ReportPeriodId, Month, Year) VALUES (1, 2010);
    However, SQL Server CE says:
    Major Error 0x80040E14, Minor Error 25502 INSERT INTO ReportPeriod (ReportPeriodId, Month, Year) VALUES (1, 2010)The count of column names and source expressions do not match. [ Column name count = 3,Source expression count = 2 ]Is it bug?
    There is no knowledge that is not power.

    DB:2.86:Sql Server Ce 3.5 Sp2 Beta: Problem With Table Which Has Identity Column k3

    Thanks! It works! By the way, why do you escape Month andYear? These one are neither reserved words, nor functions.There is no knowledge that is not power.

  • RELEVANCY SCORE 2.86

    DB:2.86:How I Can Find All Relationships From A Given Tables Collection? pp


    I have Tables collection. It defined as following:
    public class Tables:ListTable
    {
    //...
    }

    public class Table
    {
    public GUID ID{get;set;}
    public string Name{get;set;}
    public Columns Columns{get;set;}
    public Int RowsCount{get;set;}
    }
    and i have Column class:
    public class Column
    {
    public GUID ID{get;set;}
    public string Name{get;set;}
    public DataType Type{get;set;}
    }
    Columns class is defined as:
    public class Columns:ListColumn

    {

    }

    I must find all relationships between tables by using only table names, column names and tables rows count!?
    For example:
    1)If First column Table name and second column name differences with (|_|-| )(id|pk|index|code|key|num)and column's data type are same. Then
    First column is primery key column;
    For example: [product_class].[ID] and [product].[product_class_id]
    2)First column Table name and second column name differences with (_|-| )(id|pk|index|code|key|num)and column's names and data types
    same. Then First column is primery key column;
    For example: [customer].[customer_id] and [department].[customer_id]

    DB:2.86:How I Can Find All Relationships From A Given Tables Collection? pp

    But, i need find all variants tables relationship...
    It's important for us what this means. Please rephrase your question.
    From the database viewpoint: Use INFORMATION_SCHEMA (the ANSI-compatible views).
    From the .NET viewpoint: D'uh? Good coding requires conventions. Thus you need to implement those. On the otherhand, this kind of information should be already reflected in your class model.
    From the semantic/relational viewpoint: It's not really possible.
    Cause
    1) column names may differ entirely in different tables, but refer the same domain.

    2) Even when they have a commen naming pattern and the same domain, the may denote a different semantic meaning. Thus there is nor relationship.
    3) Relationships may be based on candidate keys, thus are based on multiple columns.

  • RELEVANCY SCORE 2.86

    DB:2.86:Adding A Column jj


    I need to add a column to a table and I need to include a check constraint. I do not know the DDL. Here is the requirement:

    Existing Table Name: ACQ.REAL_ESTATE_LOAN
    New Column Name : STATE_CITY_HIGH_COST_PRE_EMPTION_IND
    Type : Varchar(2)
    Length : 1
    Nullable : Yes
    Check Constraint :'Y' or 'N'

    Here is what I have so far:

    ALTER table ACQ.REAL_ESTATE_LOAN add (
    STATE_CITY_HIGH_COST_PRE_EMPTION_IND Varchar2(1));

    Could someone please modify it to show adding the check constraint? thanks.

    DB:2.86:Adding A Column jj

    Try this:
    ALTER table ACQ.REAL_ESTATE_LOAN add (
    STATE_CITY_HIGH_COST_PRE_EMPTION_IND Varchar2(1) CHECK (STATE_CITY_HIGH_COST_PRE_EMPTION_IND in ('Y', 'N')) );

  • RELEVANCY SCORE 2.86

    DB:2.86:How To Remove Not Null Constraint z7


    Hi,
    when i was creating table i given NOT NULL constraint. now i would like to
    remove that constraint, i dont know constraint name. How can i do this.

    venkat

    DB:2.86:How To Remove Not Null Constraint z7

    possible to disable NOT NULL constraint Sure:

    SQL create table t (a integer constraint t_not_null_const not null)
    2 /

    Table created.

    SQL desc t
    Name Null? Type
    ----------------------- -------- ----------------
    A NOT NULL NUMBER(38)

    SQL
    SQL alter table t disable constraint t_not_null_const
    2 /

    Table altered.

    SQL desc t
    Name Null? Type
    ----------------------- -------- ----------------
    A NUMBER(38)

  • RELEVANCY SCORE 2.85

    DB:2.85:Orcale 9i Primary And Foerign Key Constraint as


    I had created table in the following way

    create table dept(deptno number,dname varchar2(20));
    create table emp(empno number primary key,ename varchar2(25), deptno number references dept(deptno));

    One can get constraint name and constraint type from user_constraints table. But I want to know name of the column of table from which column is used as foreign key in another table. How to get such information?

    DB:2.85:Orcale 9i Primary And Foerign Key Constraint as

    user11180154 wrote:
    I had created table in the following way

    create table dept(deptno number,dname varchar2(20));
    create table emp(empno number primary key,ename varchar2(25), deptno number references dept(deptno));

    One can get constraint name and constraint type from user_constraints table. But I want to know name of the column of table from which column is used as foreign key in another table. How to get such information?Please post your administration related questions under correct forum - [Database-General|http://forums.oracle.com/forums/forum.jspa?forumID=61]

  • RELEVANCY SCORE 2.85

    DB:2.85:Renaming A Constraint On A Table 7a


    What is the syntax for renaming a default constraint and Foregion key constraint in sql server 2005.with the below tableschema : dboTable : CustomerDefault const name : D_date on column Date_UsedFk Const name : F_custvalue on column Customer_value
    ThanksNaga

    DB:2.85:Renaming A Constraint On A Table 7a

    create table test(id int constraint pktest Primary Key)
    exec sp_rename pktest,newpk
    Abdallah, PMP, MCTS

  • RELEVANCY SCORE 2.85

    DB:2.85:How Do You Get The Column Names For A Given Table From An Sql Localdb Programmatically In Visual Basic. jc


    Just new to this and unable to find answers

    DB:2.85:How Do You Get The Column Names For A Given Table From An Sql Localdb Programmatically In Visual Basic. jc


    Dim SQLString As String = SELECT * FROM [ _TableName ]

    With this you will query all data from the table just to get the column names; Visakh and my solution are much mor efficent.Olaf Helper

    [ Blog] [ Xing] [ MVP]

  • RELEVANCY SCORE 2.85

    DB:2.85:How To Find Primary Key Column From System Catalog For A Given Table. c1


    Hi:
    If I have the name of a table, which system table from the system catalog would tell me what the primary key column of that table is?
    Thank you.Serene Sky

    DB:2.85:How To Find Primary Key Column From System Catalog For A Given Table. c1

    That may work in some cases, but for those who name CONSTRAINTs otherwise, it would not be correct.

    For example:
    CREATE TABLE A
    (
    Id INT CONSTRAINT Id PRIMARY KEY(Id),
    PK INT CONSTRAINT pk UNIQUE(PK)
    )

    select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    where constraint_name like 'pk%'and table_name='a'

    The query lists the wrong COLUMN. To do this in all cases, INFORMATION_SCHEMA.TABLE_CONSTRAINTS needs to be queried as well.

  • RELEVANCY SCORE 2.85

    DB:2.85:Adf Model, Constraint Names And Db Synchronization 7j


    Hi,

    Is the created model still valid when deployed to a production database?
    We name most of database constraints, example foreign key on table SITE that references table COUNTRY, constraint name SITE_COUNTRY_FK.
    We don't give explicit names to NOT NULL constraints, so the names are given by the database.
    Now if you drop the table and you recreate it or migrate to another schema (production), those constraints will have different names.
    In JDeveloper, when you activate the synchronize with Db option on a Entity Object after recreating a table, the tool suggest to remove those constraints and to recreate them. Is this required?
    Does that mean that the constraint names are hardcoded are written somewhere in the model and that the model won't work with the recreated table?
    In that case, all database constraint names must be named explicitely when business rules are defined at the BC4J level.
    Could Oracle confirm this.
    Thanks
    Frederic

  • RELEVANCY SCORE 2.84

    DB:2.84:Check Constraint 7s


    alter table table name add constraint constraint name check (dateofbirth = '01-01-1966')

    dateofbirth is a column name with date datatype.

    it shows error like date or system variable wrongly specified any suggestions or hints.

    DB:2.84:Check Constraint 7s

    ... or use ANSI date literal:
    check (dateofbirth date '1965-12-31')

  • RELEVANCY SCORE 2.84

    DB:2.84:I Got 2 Tables... pc


    I got a table.... Uthyrning...

    In this table i have a column:

    medlems_nr NUMBER(10) CONSTRAINT cmmednr_umednr REFERENCES medlemmar(medlems_nr),

    question is, how do i find the Medlemmar(medlems_nr) that my table reference to, in a SYSTEM ,user, OR DBA table if not... how can i join it...

    There is no information in user_constraints thats says that my Fk constraint reference to the table Medlemmar
    or the columns medlems_nr)

    but it shows the name of the pkconstraint R_CONSTRAINT_NAME

    how do i find the info about what table, what column my Fk constraint refer to?

    if not, how do i join it?

    DB:2.84:I Got 2 Tables... pc

    See this one

    How to find out master tables and concern child tables

    Bye Alessandro

  • RELEVANCY SCORE 2.84

    DB:2.84:How To Alter Foreign Constraint And Give Index To It md


    hi,
    we can alter unique key like this

    ALTER TABLE [table name]
    ADD CONSTRAINT [constraint name] UNIQUE( [column name] ) USING INDEX [index name];

    how we alter the foreign key.
    actualy i want to make user defined foreign key name and want to put it in to a user defined index

    DB:2.84:How To Alter Foreign Constraint And Give Index To It md

    you have to create the constraint and index separately, like,

    ALTER TABLE [table_name] ADD
    CONSTRAINT [constraint_name] FOREIGN KEY [col1, col2]
    REFERENCES [reference_table_name];

    CREATE INDEX [index_name] ON [table_name] [col1, col2];

  • RELEVANCY SCORE 2.84

    DB:2.84:?? Ssms Object Explorer Details Cant Find Constraints ?? 7d



    Hi everyone,
    I'm using SQL Server Management Studio 2008 R2.
    As you know, using the Object Explorer Details window (pressing F7) you can see details related to the item selected in Object Explorer. For example, (assuming you have Northwind installed), select the Northwind database in Object Explorer and press
    F7.
    Now, in the Search bar at the top of theObject Explorer Window, let's search for the DF_Orders_Frieght constraint defined in the Orders table. Just type, DF_Orders_Freight in the Search box and press Enter. For some reason, SSMS cannot find the
    constraint, or any constraint, for that matter. There seems to be a similar problem is searching for column names.
    The Search box is certainly a nice feature but why doesn't it find column names or constraint names? It's especially annoying when a constraint fails and we need to find out where that constraint is defined.
    Thanks much

    DB:2.84:?? Ssms Object Explorer Details Cant Find Constraints ?? 7d

    Hi Tom Baxter,

    Thanks for your post.
    If you think this feature is useful, you could provide Microsoft feedback at
    https://connect.microsoft.com/SQLServer so that our product
    team could hear your voice and improve our product in the future.Regards,
    Tom Li

  • RELEVANCY SCORE 2.83

    DB:2.83:Sql Constraints 3f


    I had created a composite primary key with two columns having constraint name PK_HEARING. Due to some requirement I needed to include a third column in the composite primary key. So I used ALTER TABLE HEARING DROP CONSTRAINT PK_HEARING to drop the earlier constraint. Then I used ALTER TABLE HEARING ADD CONSTRAINT PK_HEARING_NEW PRIMARY KEY (COL1, COL2, COL3). The Command created a new composite primary key, but the earlier constraint PK_HEARING is still existing which does not allow insertion. On insertion ORA-0001 saying violated unique constraint SCHEMANAME.PK_HEARING. I checked all constraints on the table HEARING in USER_CONSTRAINTS, ALL_CONSTRAINTS, DBA_CONSTRAINTS, USER_CONS_COLUMNS, but am not able to find any constraint on the table with this name. Kindly Help.

    DB:2.83:Sql Constraints 3f

    in V10 alter table xy drop primary key KEEP INDEX is the default.They must have changed it back again: ;)

    SQL select * from v$version where rownum = 1
    /
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    1 row selected.

    SQL create table hearing (a integer constraint pk_hearing primary key)
    /
    Table created.

    SQL select index_name, uniqueness from user_indexes where table_name = 'HEARING'
    /
    INDEX_NAME UNIQUENES
    ------------------------------ ---------
    PK_HEARING UNIQUE
    1 row selected.

    SQL alter table hearing drop primary key
    /
    Table altered.

    SQL select index_name, uniqueness from user_indexes where table_name = 'HEARING'
    /
    no rows selected.

    SQL drop table hearing
    /
    Table dropped.

  • RELEVANCY SCORE 2.83

    DB:2.83:Find Table Column Names And Max Column Length? 9j


    Hi,
    Am looking for a script that would list all column names in a given table and corresponding
    Maximum column length i.e. MAX(LEN(LTRIM(ColumnName)))
    Example:
    Table_Name
    Column_Name Max_Length
    DataType Width
    CarMaster
    Vehicle_Id
    10
    int
    NULL
    CarMaster
    FirstName
    20 varchar
    50
    CarMaster
    Surname
    30 varchar
    50
    CarMaster
    Address
    41
    varchar 60
    Cheers

    DB:2.83:Find Table Column Names And Max Column Length? 9j

    Cheers Jeff : have schema other than dbo in our current DB, so a very helpful tip.

  • RELEVANCY SCORE 2.83

    DB:2.83:Inserting Values In A Table From A View Report In Application Express 88


    Hello.

    I have several tables with the sql bellow:

    Employee
    CREATE TABLE "Employee"
    ("Employee_ID" NUMBER,
    "Name" VARCHAR2(12) CONSTRAINT "NAME_EMPLOYEE_NN" NOT NULL ENABLE,
    "Surname" VARCHAR2(10) CONSTRAINT "SURNAME_EMPLOYEE_NN" NOT NULL ENABLE,
    "Address" VARCHAR2(50) CONSTRAINT "ADDRESS_EMPLOYEE_NN" NOT NULL ENABLE,
    "Telephone" VARCHAR2(10),
    "Personal_ID_Number" VARCHAR2(13),
    "Date_of_Employment" VARCHAR2(10) CONSTRAINT "DATA_NN" NOT NULL ENABLE,
    "Salary" NUMBER(6,0),
    CONSTRAINT "Employee_ID_con" PRIMARY KEY ("Employee_ID") ENABLE,
    CONSTRAINT "Employee_CON" UNIQUE ("Personal_ID_Number", "Telephone") ENABLE
    )
    /

    Patient
    CREATE TABLE "PATIENT"
    ("Patient_ID" NUMBER,
    "Name_Patient" VARCHAR2(13) CONSTRAINT "NAME_PAT_NN" NOT NULL ENABLE,
    "Surname_Patient" VARCHAR2(10) CONSTRAINT "SURNAME_PAT_NN" NOT NULL ENABLE,
    "ADSRESS_PATIENT" VARCHAR2(50),
    "TELEPHONE_PATIENT" VARCHAR2(10) CONSTRAINT "TEL_PAT_NN" NOT NULL ENABLE,
    "EMPLOYEE_ID" NUMBER CONSTRAINT "DENTIST_PAT_NN" NOT NULL DISABLE,
    CONSTRAINT "PK_PATIENT" PRIMARY KEY ("PATIENT_ID") ENABLE,
    CONSTRAINT "PACIENT_CON" UNIQUE ("TELEFON_PACIENT") ENABLE
    )
    /
    ALTER TABLE "PATIENT" ADD CONSTRAINT "PATIENT_CON3" FOREIGN KEY ("Employee_ID")
    REFERENCES "Dentist" ("Employee_ID") ON DELETE SET NULL ENABLE
    /

    Appointment

    CREATE TABLE "Appointment"
    ("Appointment_ID" NUMBER,
    "Employee_ID" NUMBER CONSTRAINT "NN_Dentist_Appointment" NOT NULL ENABLE,
    "Patient_ID" NUMBER CONSTRAINT "NN_PATIENT_Appointment" NOT NULL ENABLE,
    "Who_Made" NUMBER,
    "Who_Changed" NUMBER,
    "Who_Canceled" NUMBER,
    "Content" VARCHAR2(1000),
    "DATE" DATE,
    CONSTRAINT "PK_Appointment" PRIMARY KEY ("Appointment_ID") ENABLE
    )
    /
    ALTER TABLE "Appointment" ADD CONSTRAINT "Appointment_CON" FOREIGN KEY ("Patient_ID")
    REFERENCES "PATIENT" ("Patient_ID") ENABLE
    /
    ALTER TABLE "Appointment" ADD FOREIGN KEY ("Employee_ID")
    REFERENCES "Dentist" ("Employee_ID") ENABLE
    /
    ALTER TABLE "Appointment" ADD FOREIGN KEY ("Who_Made")
    REFERENCES "Employee" ("Employee_ID") ENABLE
    /
    ALTER TABLE "Appointment" ADD FOREIGN KEY ("Who_Changed")
    REFERENCES "Employee" ("Employee_ID") ENABLE
    /
    ALTER TABLE "Appointment" ADD FOREIGN KEY ("Who_Canceled")
    REFERENCES "Employee" ("Employee_ID") ENABLE
    /

    Dentist

    CREATE TABLE "Dentist"
    ("Employee_ID" NUMBER,
    "Qualifications" VARCHAR2(50),
    CONSTRAINT "RB_VRAB_STOMATOLOG_PK" PRIMARY KEY ("Employee_ID") ENABLE
    )
    /
    ALTER TABLE "Employee_ID" ADD CONSTRAINT "Dentist_CON" FOREIGN KEY ("Employee_ID")
    REFERENCES "Employee" ("Employee_ID") ON DELETE CASCADE ENABLE
    /

    I'm trying to make an application in Oracle Application Express and I've tried making an SQL report using the following SQL query:

    CREATE OR REPLACE FORCE VIEW "Appointment_REPORT" ("DOCTOR", "PATIENT", "Content", "Date") AS
    select concat(concat(v.name,' '),v.surname) as doctor, concat(concat(pa.name_patient,' '),pa.surname_patient) as pactent, p.content, p.date
    from Appointment p, Amployee v, Patient pa
    where
    v.employee_id=p.employee_id
    and p.patient_id=pa.patient_id
    /

    The SQL works fine and gives me lovely results in my report. However, I don't know how to link the SQL to my actual Appointment table. Deleting, updating or creating new appointments through this view is impossible because I get the following error Unexpected error, unable to find item name at application or page level.

    I understand how to make a form on the Appointment table but given how that table contains lots of primary keys (numbers) it's not preferable for the user to have to deal with that.

    Can I have some help with linking the two and making it functional?

    Thanks in advance.

    DB:2.83:Inserting Values In A Table From A View Report In Application Express 88

    1. My original tables aren't in English therefore i copied the code and edited it by translating it but I apologise for not preserving the case, I didn't think it was that relevant for you to understand what my tables and queries are about.

    2. Since the column names aren't in English my column name isn't Date but Datum. Again, a change due to translation.

    3. I understand that tables have one primary key. What I meant was foreign keys, but I typed primary keys because the values Employee_ID and Patient_ID are primary keys in different tables (Employee and Patient respectively).

    4. "The two" are my actual report and the form. Since the report is a result of the given sql query I doubt it's update-able and this is the actual problem.

    5. The functionality I'm looking for is that I see the report according to my SQL query. However if I can't update appointments through the SQL query (the source of the report) and also delete appointments then I can't update the actual table which is necessary for actual change in the report.

    I hope this helps.

  • RELEVANCY SCORE 2.83

    DB:2.83:To Check The Constraints Of A Table In Oracle pp


    i need to check the constraints which each column in a table has.the problem is user_constraints table doesnt contain column name it only has table name.is there any way to link the column name with the constraint in the table?

    DB:2.83:To Check The Constraints Of A Table In Oracle pp

    Hi,

    Check DBA_CONS_COLUMNS
    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1035.htm#sthref1156

    Nicolas.

  • RELEVANCY SCORE 2.83

    DB:2.83:Table Column Order 89


    hi all,

    I was given a task in which i have to rearrange or reorganize table column likewise in table A in have fields (NAME char,AGE INT,ID PK,comments varchar2(23));
    i have to rearrange them in order
    Table A (ID PK,AGE INT,NAME CHAR,Comment varhcar2(23));
    I used to rearrange them by:
    1.create new table
    2.inserting rows using VIEWS
    3.Delete previous table
    4.REname new table

    IS THERE ANY OTHER POSSIBLE WAY TO REARRANGE?
    How does applying constraint help in performance?

    Thanks ,
    Neerav

  • RELEVANCY SCORE 2.83

    DB:2.83:Table Lookup ma


    Hi,

    How do I find out all the name and structure (i.e. column names) of all the tables created on my database?

    Thanks.

    Dhruv

    DB:2.83:Table Lookup ma

    select table_name from user_tables.
    describe table_name
    select table_name,column_name from user_tab_columns order by table_name,column_position
    /

  • RELEVANCY SCORE 2.82

    DB:2.82:Find The Column Name For A Constraint 1j


    Hi Guru,I know the constraint name, now i need the column name and table name for the constraint?Thanks

    DB:2.82:Find The Column Name For A Constraint 1j

    Query the USER_CONS_COLUMNS then you will find the column name and table NameSQL Select * from USER_CONS_COLUMNS where Constraint_Name='Your_constraint_Name';

  • RELEVANCY SCORE 2.82

    DB:2.82:List A Constraint For A Given Attribute In A Table - Describe? 38


    Hello,

    Given a real simple table that has a CONSTRAINT on one of the attributes, I'm trying to find out he NAME of the constraint so that I can alter it.

    When I perform the DESCRIBE command for the table of interest, the following output occurs:

    DESCRIBE Room;

    Name Null? Type
    ----------------------------------------- -------- -------------
    ROOMNO NOT NULL NUMBER(4)
    HOTELNO NOT NULL NUMBER(6)
    TYPE CHAR(1)
    PRICE NUMBER(5,2)

    I know that the TYPE and PRICE attributes have CONSTRAINTS, so how do I find out those CONSTRAINTS?

    Thank you

  • RELEVANCY SCORE 2.82

    DB:2.82:Calendar Display Column Content Ordering kp


    I would like to order the items returned to be displayed for a day by their values or a different column? The executing SQL wraps my source and adds an order by for the date. I don't see a way to modify that in 3.1.

    e.g.
    select date, name, name_sort from table order by date, name_sort
    becomes
    select * from (select date, name, name_sort from table order by date, name_sort) where date range test order by date

    I can have 0 to 88 different names for any given date. I want to display the name, but order the names by the name_sort column.

    Thanks,
    Ed

    DB:2.82:Calendar Display Column Content Ordering kp

    Ed,

    Your query for the calendar could look like this

    SELECT ename, hiredate + rownum / 24 / 60
    FROM (SELECT ename, hiredate
    FROM emp
    ORDER BY ename)which will sort the values in ascending order.

    SELECT ename, hiredate + 1 / rownum / 24 / 60
    FROM (SELECT ename, hiredate
    FROM emp
    ORDER BY ename)See this example:

    http://htmldb.oracle.com/pls/otn/f?p=31517:124

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://htmldb.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------

  • RELEVANCY SCORE 2.81

    DB:2.81:Delete Constraint Without Knowing Its Name ?? d9


    In SQL Server 2005, I hava a client where I do not have access to their SQL Server.  I update the database structure by giving them scripts which they run.  As I update the structures I occasionally need to delete a constraint, then typically recreate it later.  Usually I use this type of snippet:IF  EXISTS (SELECT * FROM sys.default_constraints    WHERE object_id = OBJECT_ID(N'[dbo].[ConstraintName]')    AND parent_object_id = OBJECT_ID(N'[dbo].[tablename]')) ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [ConstraintName]This assumes I know the constraint name.  A lot of the older constraints have random type names which I assume are different on their server than mine; they were not explicitly given names when created, so they would heve been given names by SQL Server.Is there a way to delete a constraint without knowing its name? For example, delete all constraints associated with a field, or all constraints ssociated with a table?Management Studio is an option, but for my client that involves a higher degree of paperwork and permission seeking than just running a script.Many thanks,Mike Thomas

  • RELEVANCY SCORE 2.81

    DB:2.81:Error While Alter The Table mm


    HiPlease let me know reasons for this below errorError starting at line 32 in command:ALTER TABLE DEVELOPMENT.BRANDING_STRINGSADD CONSTRAINT frk_strings_brandingsdetailsFOREIGN KEY (CUSTOMIZATION_ID) REFERENCES BRANDING_DETAILS (CUSTOMIZATION_ID)Error report:SQL Error: ORA-02270: no matching unique or primary key for this column-list02270. 00000 - "no matching unique or primary key for this column-list"*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.*Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view

    DB:2.81:Error While Alter The Table mm

    According to the above error ,It seems the column CUSTOMIZATION_ID does not exist in table BRANDING_DETAILS. Message was edited by: BluShadow

    Begging for points removed by moderator.

  • RELEVANCY SCORE 2.81

    DB:2.81:Table Name Is Automatically Changed From "User" To "Xuser" 1x



    Env: CMP 4.0.0 with MySQL 4.0.12I want to create CMP bean (UserEJB) mapped to the USER table; (worked fine in 3.x).However, after I ported the application to JBoss 4.0, the table name is automatically changed to XUSER instead of USER, ignoring my settings from jbosscmp-jdbc.xml (I have the same problem with 2 other table names: "TRIGGER" and "VIEW", translated into "XTRIGGER" and "XVIEW")...Do you have any ideea why am I getting this? The jbosscmp-jdbc.xml file looks ok to me...Note: I am able to manually create a table named USER, so the db seems to be ok...Thank you,Dragos- jbosscmp-jdbc.xml----------?xml version="1.0" encoding="UTF-8"?!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 4.0//EN" "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_4_0.dtd"jbosscmp-jdbc java:/DefaultDS datasource-mappingmySQL/datasource-mapping create-tabletrue/create-table remove-tablefalse/remove-table read-onlyfalse/read-only read-time-out900/read-time-out pk-constrainttrue/pk-constraint preferred-relation-mappingforeign-key/preferred-relation-mapping read-ahead on-find /read-ahead enterprise-beans ejb-nameUserEJB/ejb-name table-nameUSER/table-name cmp-field field-nameid/field-name column-nameID/column-name /cmp-field......................................

    DB:2.81:Table Name Is Automatically Changed From "User" To "Xuser" 1x


    Ops, next time I'll research more before posting...Thank you a lot, Dragos.

  • RELEVANCY SCORE 2.81

    DB:2.81:Error While Creating Foreign Key xp


    Hi!

    I have one table name Package. In that table I have a columns like packagenumber, invoicenumber, ordernumber, sequencenumber, bidnumber, width, discription, length.
    I have another table named UserField. In that table i have a columns like masterbillnumber, billnumber, pkgsequencenumber, ordernumber, ufcAssoctype. Now I want to create foreign key on column pkgsequencenumber on Userfield table reference to packagenumber
    column in Package table.
    So, would it be possible. I am just wondering because the name of the column(packagenumber and pkgsequencenumber (both field contains data for package number.)) When I try to create the foreign key it gives me following error.
    The columns in table Package do not match an existing primary key o unique constraint. Would it be possible that I am getting an error because the column names are different?

    Thanks,

    DB:2.81:Error While Creating Foreign Key xp

    Thanks you Hunchback.....
    I appreciate you..

  • RELEVANCY SCORE 2.80

    DB:2.80:Pk Generation Problem For Case Sensitive Table Names c7



    When table name is in quotes, for e.g., "Organization", jboss tries to create primary key constraint as pk"organization" ... This fails as pk"Organization" is not a valid constraint name. Instead it should put pk inside the quotes "pk_Organization".I am using Jboss 3.0.0.Sandeep Dixit

    DB:2.80:Pk Generation Problem For Case Sensitive Table Names c7


    JBossCMP does not support quoted identifiers. There was someone working on adding this, but I don't know the state of the development.

  • RELEVANCY SCORE 2.80

    DB:2.80:Oracle Given Column Names - Is It 10gr2 Bug ? sa


    I have encountered the following situation and I wonder why Oracle does this way?

    Tables with data to query:
    create table table_1 as select 1 as join_id, 'HANNIBAL' as name from dual;
    create table table_2 as select 1 as join_id, 'SMITH' as name from dual;The following queries do not state column names explicitly so it is DBMS's responsibility to make unique column names if there exists duplicate column names.

    The first query:
    select * from table_1 t1 join table_2 t2 on t1.join_id=t2.join_id;The second query:
    select * from(select * from table_1 t1 join table_2 t2 on t1.join_id=t2.join_id);Column names given by Oracle are:
    The first query names:
    JOIN_ID,NAME,JOIN_ID_1,NAME_1The second query names:
    QCSJ_C000000000400000,QCSJ_C000000000400001,QCSJ_C000000000400002,QCSJ_C000000000400003I would expect Oracle to give the same names for columns within both queries.
    Does somebody know how Oracle processes these column names internally?
    However, I know that I can prevent these vague column names from happening when stating column names explicitly. Nevertheless I am curious about discovered situation and its background.

    DB:2.80:Oracle Given Column Names - Is It 10gr2 Bug ? sa

    I suspect that it is a bug that it doesn't raise an ORA-918 in either version.

    There was a related note 835701.1 around ansi sql raising an ora-918 in 11g but not 10g. This was confirmed as expected behaviour in 11g fixing a bug in 10g (that it didn't raise the error).

    So, maybe they haven't completely sorted it out as I'd have thought ORA-918 would be the expected behaviour here as well.

    The fact that it is not consistent between ANSI and non-ANSI is a pretty clear indication of bug.

  • RELEVANCY SCORE 2.80

    DB:2.80:Find Table/Column Given Class/Attribute Name kp


    How can you find the table+column for a given package+class+attribute? --
    Realize this can be more complex if attribute is a collection etc. - only
    interested in simple case (attribute is an Integer) here.

    Thanks!

    DB:2.80:Find Table/Column Given Class/Attribute Name kp


    kodo.meta.ClassMetaData meta =
    ((kodo.runtime.KodoPersistenceManager)pm).
    getConfiguration ().
    getMetaDataRepository ().
    getMetaData (theClass, theClass.getClassLoader (), true);You can replace all that with:

    kodo.meta.ClassMetaData meta = kodo.runtime.KodoHelper.getMetaData
    (theClass, pm);

  • RELEVANCY SCORE 2.80

    DB:2.80:Cyrillic Symbols In Column Names fx


    My table have cyrillic symbols in name and in column names.
    I create new Form on a Table or View, on page, when needed
    select the Primary Key Column, I select column
    and get error Column names must be valid Oracle identifiers.
    If column name dont have cyrillic symbols, all ok.
    In other Oracle developers products, like Portal and Oracle Forms and Reports,
    I used cyrillic symbols in table names and in column names and all be ok.

    It is feature or is it bug?
    How create forms in Htmldb 2.0 if column names in my table have cyrillic symbols?

    Thanks,

    Victor

    DB:2.80:Cyrillic Symbols In Column Names fx

    My table have cyrillic symbols in name and in column names.
    I create new Form on a Table or View, on page, when needed
    select the Primary Key Column, I select column
    and get error Column names must be valid Oracle identifiers.
    If column name dont have cyrillic symbols, all ok.
    In other Oracle developers products, like Portal and Oracle Forms and Reports,
    I used cyrillic symbols in table names and in column names and all be ok.

    It is feature or is it bug?
    How create forms in Htmldb 2.0 if column names in my table have cyrillic symbols?

    Thanks,

    Victor

  • RELEVANCY SCORE 2.80

    DB:2.80:How To Find Table Names Using Column Names d3


    Hi,

    From which tables can I get the following columns in AR
    Unscheduled Revenue
    Revenue
    Line Amount
    Line Number
    and whats the way of finding out table name from column name

    Best Regards

    DB:2.80:How To Find Table Names Using Column Names d3

    914618 wrote:
    Hi,

    From which tables can I get the following columns in AR
    Unscheduled Revenue
    Revenue
    Line Amount
    Line Number
    and whats the way of finding out table name from column name

    Best RegardsPlease see old threads for similar topic/discussion -- https://forums.oracle.com/forums/search.jspa?threadID=q=Column+AND+Table+AND+NameobjID=c3dateRange=alluserID=numResults=15rankBy=10001

    You can also refer to eTRM website -- http://etrm.oracle.com/

    Thanks,
    Hussein

  • RELEVANCY SCORE 2.79

    DB:2.79:Enable Foreign Key Constraint Is Slow 79


    Hi,

    I am trying to enable a foreign key constraint using..

    alter table table name enable constraint fk constraint name;

    It takes a long time, in fact, it hangs. The SQL is shown to consume CPU, when seen from Oracle Enterprise Manager Top SQL page.

    Is this a bug?

    We have tried with and without index on the FK column(s) before enabling the FK.

    Regards,
    Rahul

    DB:2.79:Enable Foreign Key Constraint Is Slow 79

    Hi,

    Please look into table statistics.
    When any constraint is enabled, Oracle will check for it's validity.
    In your case it might have to look into billions of records causing it to take long time.
    Share more details about FK, is it self referential FK?
    If referring to PK of another table, look into stats of that table as well.

    Cheers,
    Ullhas

  • RELEVANCY SCORE 2.79

    DB:2.79:Lengths Of Table And Column Names xf


    Hello!

    I'm seeking to find how long a table and column name can be in Oracle 7, Oracle 8, 8i, and 9i.

    Can anyone direct me to where I can find this information?

    Thanks very much!

    Bill Loggins
    bill@acm.org

    DB:2.79:Lengths Of Table And Column Names xf

    The limit of both table and column names are 30 characters, with the first character needing to be a letter(alpha) with the other characters permitted to be any combination of alphanumeric and underscores that does not match an ORACLE reserved word.

    Reference manual for create table sybntax at this link:
    http://otn.oracle.com/pls/db92/db92.tabbed?section=129973

    HTH
    Dave

  • RELEVANCY SCORE 2.79

    DB:2.79:How Can I Get The Column Name For Assigned Constraint Name From The User_Constraints Table????? 89


    Hi ,I have a table so many constraints on so many columns. I need to know which column has which constraint plsql dev?? like below...table_name -------- col_name ------ constraint_name_of_that_col xxxxxx xxxxxxx xxxxxxxxxxxxxxxxor else please let me know how can i get the corresponding col_name for a particular constraint name???

  • RELEVANCY SCORE 2.79

    DB:2.79:Syntax Request 99


    Could someone check my syntax to add a foreign key to a table?

    ALTER TABLE table ADD CONSTRAINT name FOREIGN KEY(column) REFERENCES schema.table(column);

  • RELEVANCY SCORE 2.78

    DB:2.78:Not Null Constraint d7


    trying to drop a column that is defined as not null,

    i am trying to use this query, , but i dont know the constraint name is not null, or ?

    alter table dbo.Robust2 drop constraint [ xxxx ]
    GO
    alter table dbo.Robust2 drop column BESTSCORE3

    Thanks

    DB:2.78:Not Null Constraint d7


    Drop the default constraint and then drop the column
    You can find the constraint name in the error.
    You can also use the below sql to find the default constraint name
    select * from sys.default_constraints where parent_object_id=object_id('Robust2')
    ALTER TABLE dbo.Robust2 drop constraint constraint_Name
    ALTER TABLE dbo.Robust2 drop column BESTSCORE3
    --Prashanth

  • RELEVANCY SCORE 2.78

    DB:2.78:Unique Constraint Is Only Given As Unique Index In Databasemetadata dp


    Hi,

    I run a DDL that generates a unique constraint (and an index with the same name) on a table in the database when I use a UNIQUE CONSTRAINT statement.
    Getting the table information with JDBC it only comes back as an index, neither as an exported key, nor as a primary key.

    I get the same information running a DDL that generates a unique index.

    How can I find out (with DatabaseMetaData) that it is a constraint (and therefore can be interpreted as unique key, not as index?!

    Udo

    DB:2.78:Unique Constraint Is Only Given As Unique Index In Databasemetadata dp

    Hi,

    I run a DDL that generates a unique constraint (and an index with the same name) on a table in the database when I use a UNIQUE CONSTRAINT statement.
    Getting the table information with JDBC it only comes back as an index, neither as an exported key, nor as a primary key.

    I get the same information running a DDL that generates a unique index.

    How can I find out (with DatabaseMetaData) that it is a constraint (and therefore can be interpreted as unique key, not as index?!

    Udo

  • RELEVANCY SCORE 2.78

    DB:2.78:Sql Query df


    Hi ,
    I have two tables.1st table with 210 columns.
    Table structure like this
    1st table
    sid,record_date,up1,dwn1,up2,dwn2...up50,dwn50.
    ex data:
    1,24-dec-2005,10,20,30,40...50,50
    2,24-dec-2005,20,20,20,20,..20,20
    2nd table
    name,col1,col2. This table contains some value and corresponding column names from table 1
    ex data:
    Ravi,up1,dwn1
    gvs,up2,dwn2
    now given a "name" in table2 i want to find all the corresponding column values in table1.(table2 actually contains column names of table1 with respect to the value).
    How to get column values from table1 with respect to a value in table2 say
    for ravi i know columns are up1,dwn1 and need to get their values from table1.
    Thanks in advance
    ravi

    DB:2.78:Sql Query df

    maybe you can use the decode function
    select name , decode (col1 , 'up1',up1,'up2',up2,'dwn1',dwn1,'dwn2',dwn2) ,
    decode (col2 , 'up1',up1,'up2',up2,'dwn1',dwn1,'dwn2',dwn2)
    from
    table1 , table2
    where join contition
    and name = name

  • RELEVANCY SCORE 2.78

    DB:2.78:How To Find And Replace The Table Name/Column Name 78


    We have just upgraded the application to R12. Have many reports developed in Oracle Reports Builder. There are many changes in R12 table and column names when compare with 11i. Please suggest a solution to change the table name or column name in multiple reports queries instead of doing manually.

    DB:2.78:How To Find And Replace The Table Name/Column Name 78

    We have just upgraded the application to R12. Have many reports developed in Oracle Reports Builder. There are many changes in R12 table and column names when compare with 11i. Please suggest a solution to change the table name or column name in multiple reports queries instead of doing manually.

  • RELEVANCY SCORE 2.77

    DB:2.77:Adding Not Null Constraint To Existing Table ck


    hi, i am trying to add a not null constraint to a column i have already created.
    i have this
    ALTER TABLE mytable
    MODIFY (mynumber NUMBER(8,2) NOT NULL);
    but i need to give this constraint a name.

    DB:2.77:Adding Not Null Constraint To Existing Table ck

    And the command hasn't changed in 6 years so that's
    cool too!That's funny. :D

  • RELEVANCY SCORE 2.77

    DB:2.77:Sql Query??? fp


    Hi,
    I have a small query, assume that i have the following table,

    name
    ram
    ravi
    rahul

    and iam given a runtime input of names, now my job is to find out what names are not in the table, can any body help me out by framing the sql query, also note that the number of names given at runtime is very much variaable and not a constant...

    cheere...

    DB:2.77:Sql Query??? fp

    Everyone needs schema-level collections for the basic scalar data types. These are mine.

    CREATE OR REPLACE TYPE varchar2_array AS TABLE OF VARCHAR2 (32767);

    CREATE OR REPLACE TYPE number_array AS TABLE OF NUMBER;

    CREATE OR REPLACE TYPE integer_array AS TABLE OF INTEGER;

    CREATE OR REPLACE TYPE date_array AS TABLE OF DATE;

  • RELEVANCY SCORE 2.77

    DB:2.77:Unnamed Default Constraint Cannot Be Automatically Dropped f7


    We recently switched to using DACFx to help us generate the heavy lifting (excluding migrations) in our ad-hoc database change management (i.e. to help apply each development change to the test environment) and monthly change management process (i.e. coming
    up with the final release script).
    All in all, it was a big day and we're all very excited.
    We did however come across a fairly important bug that is making me glad we didn't go all-in just yet, relying solely on the comparison of a DACPAC to a database, at deployment time, b/c I'm not sure how we would have recovered from this if we were doing
    that. So, I atleast need to report this bug.
    Here's the scenario...
    We have many unnamed 'default constraints'. These are our only 'unnamed' object in the whole database. To be more precise, these are 'system generated names'. Making matters worse, the system generated names are different for each implementation
    of our database structure (there are hundreds). So, in scripting world, typically we would query the system table whenever we encountered this, to get the name of the constraint on a given column, then drop that constraint.
    Probably the answer is we should work to name all of our constraints to avoid this problem, but the bug is that the 'generated SQL script' from SSDT ends up with a syntax violation, b/c it does not correctly gather the name of the constraint.
    Initially, Publish was just failing, but obtaining the SQL script, clued us in that the problem was with the unnamed constraint.
    You end up getting this:
    ALTER TABLE [dbo].[h837p_transaction] DROP CONSTRAINT ;
    Which leads to this:
    Dropping Default Constraint on [dbo].[h837p_transaction]....
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ';'.
    I read these related posts but none of them addressed the angle of potentially having the tool query for the name dynamically, instead of failing with a syntax error.
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7ae2e3c-f99a-4395-9a9d-13709930e76a/dropping-default-constraints-during-deploy?forum=vstsdb
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a1ea6d8d-bef1-4f6e-8f66-a16b812de6fc/drop-column-with-default-constraint?forum=transactsql
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a0710aa-aed4-4604-8258-945a1ed7b6c0/can-i-drop-default-constraint-for-a-column?forum=sqlgetstarted
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b5f6edd-6249-4279-9871-146644d9194d/deployment-fails-column-drop-blocked-by-its-own-default-constraint?forum=ssdt
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e608264f-f84e-4c2a-b6f8-355397031859/drop-constraint?forum=sqlgetstarted
    Should we just implement the work-around of sweeping through and naming all of our default constraints going forward, to avoid this problem?
    Do you recognize this as a bug? If so, how would you 'fix it'? Maybe the current behavior is ok and we just need to know it's a 'known-issue' of using unnamed objects, which is probably a bad practice really.
    Make sense?
    Thank you!
    Ryan

    DB:2.77:Unnamed Default Constraint Cannot Be Automatically Dropped f7

    We recently switched to using DACFx to help us generate the heavy lifting (excluding migrations) in our ad-hoc database change management (i.e. to help apply each development change to the test environment) and monthly change management process (i.e. coming
    up with the final release script).
    All in all, it was a big day and we're all very excited.
    We did however come across a fairly important bug that is making me glad we didn't go all-in just yet, relying solely on the comparison of a DACPAC to a database, at deployment time, b/c I'm not sure how we would have recovered from this if we were doing
    that. So, I atleast need to report this bug.
    Here's the scenario...
    We have many unnamed 'default constraints'. These are our only 'unnamed' object in the whole database. To be more precise, these are 'system generated names'. Making matters worse, the system generated names are different for each implementation
    of our database structure (there are hundreds). So, in scripting world, typically we would query the system table whenever we encountered this, to get the name of the constraint on a given column, then drop that constraint.
    Probably the answer is we should work to name all of our constraints to avoid this problem, but the bug is that the 'generated SQL script' from SSDT ends up with a syntax violation, b/c it does not correctly gather the name of the constraint.
    Initially, Publish was just failing, but obtaining the SQL script, clued us in that the problem was with the unnamed constraint.
    You end up getting this:
    ALTER TABLE [dbo].[h837p_transaction] DROP CONSTRAINT ;
    Which leads to this:
    Dropping Default Constraint on [dbo].[h837p_transaction]....
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ';'.
    I read these related posts but none of them addressed the angle of potentially having the tool query for the name dynamically, instead of failing with a syntax error.
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7ae2e3c-f99a-4395-9a9d-13709930e76a/dropping-default-constraints-during-deploy?forum=vstsdb
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a1ea6d8d-bef1-4f6e-8f66-a16b812de6fc/drop-column-with-default-constraint?forum=transactsql
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a0710aa-aed4-4604-8258-945a1ed7b6c0/can-i-drop-default-constraint-for-a-column?forum=sqlgetstarted
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b5f6edd-6249-4279-9871-146644d9194d/deployment-fails-column-drop-blocked-by-its-own-default-constraint?forum=ssdt
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e608264f-f84e-4c2a-b6f8-355397031859/drop-constraint?forum=sqlgetstarted
    Should we just implement the work-around of sweeping through and naming all of our default constraints going forward, to avoid this problem?
    Do you recognize this as a bug? If so, how would you 'fix it'? Maybe the current behavior is ok and we just need to know it's a 'known-issue' of using unnamed objects, which is probably a bad practice really.
    Make sense?
    Thank you!
    Ryan

  • RELEVANCY SCORE 2.77

    DB:2.77:Problem Create Table With Primary Key Still In Trouble ! Please Help! a3


    problem CREATE TABLE with PRIMARY KEY Still in Trouble ! Please Help!
    Hi there !

    I use the orcle 8i, and i don't know why i can't create table with any primary key EXample:

    SQL CREATE TABLE O_caisses
    2 (No_caisse NUMBER(3) constraint caisses_pk PRIMARY KEY,
    3 NB_BILLETS NUMBER(5)
    4 )
    5 /
    CREATE TABLE O_caisses
    *
    ERROR at line 1:
    ORA-18008: cannot find OUTLN schema

    ***********some Debuger show me this way: *********************

    Well there r certain point u got to notice when creating a table with constraints.
    1) U can create table with COLUMN level constraint.
    2) U can create table with TABLE level constraint.
    3) In COLUMN level constraint u can't give a constraint a name
    but only mention the type of constraint.
    4) In TABLE level constraint u can give a name to constraint.

    Following are the examples of both

    --COLUMN LEVEL
    CREATE TABLE O_caisses
    (No_caisse NUMBER(3) PRIMARY KEY,
    NB_BILLETS NUMBER(5));

    --TABLE LEVEL
    CREATE TABLE O_caisses
    (No_caisse NUMBER(3),
    NB_BILLETS NUMBER(5),
    constraint pk_caisse primary key (No_caisse));

    ********************And this is another one:*****************

    SQLgrant create any outline to username;

    **************************************************************

    BUT the problem is still present, i don't know what to do now !
    Please could some body help me !

    Thanks alot!
    Luong.

    DB:2.77:Problem Create Table With Primary Key Still In Trouble ! Please Help! a3

    Luo,

    Is this problem still occuring? Your previous posting says that you fixed it ...

    Alison

  • RELEVANCY SCORE 2.77

    DB:2.77:Oca Query 1k


    Hi ALL,

    here is my query i doubt

    Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS
    table holds a value?
    A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
    B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT
    NULL;
    C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
    D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;
    E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;
    F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

    Help will be a help?

    DB:2.77:Oca Query 1k

    Help will be a help?How very Zen.

  • RELEVANCY SCORE 2.77

    DB:2.77:Query Help: Query To Return Column That Represents Multiple Rows 7z


    I have a table with a name and location column. The same name can occur multiple times with any arbitrary location, i.e. duplicates are allowed.

    I need a query to find all names that occur in both of two separate locations.

    For example,

    bob usa
    bob mexico
    dot mexico
    dot europe
    hal usa
    hal europe
    sal usa
    sal mexico

    The query in question, if given the locations usa and mexico, would return bob and sal.

    Thanks for any help or advice,
    -=beeky

  • RELEVANCY SCORE 2.77

    DB:2.77:Custom Column Names For Drillthroughs?? 1a


    Hi,
    I have created an SSAS Cube which contains one custom Action.
    This action is of course used for drillthrough purposes - which works fine.
    HOWEVER the column names displayed for the drillthrough table contains the full namespaces ...
    For Example:
    one of the drillthrough columns I selected was from my Product dimension - the attribute is called Product Name.
    but on the drillthrough results table the column name for this is given as:
    [$Product].[Product Name]
    HOW can I get this 'ugly' column name to just be Product Name instead of [$Product].[Product Name]
    ?I.W Coetzer

    DB:2.77:Custom Column Names For Drillthroughs?? 1a

    The only method I know for doing this is to use a .Net stored procedure like the Drillthrough functions from the AS Stored Procedure project

    http://asstoredprocedures.codeplex.com/wikipage?title=DrillthroughreferringTitle=Homehttp://geekswithblogs.net/darrengosbell - please mark correct answers

  • RELEVANCY SCORE 2.77

    DB:2.77:Table Name zm


    Hi..

    I have column names, i want to find out table name in oracle 9.0...
    please any one help me

    V.Surya

    DB:2.77:Table Name zm

    thank u very much.

    Now i am able to get the result...

    surya

  • RELEVANCY SCORE 2.76

    DB:2.76:Given The Table Values, Get The Row And Column Names 8f


    A
    B
    D

    1
    45
    52
    54

    2
    34
    24
    11

    3
    22
    334
    223

    Given the above table, what I want to find is:

    Value: 24 -- I type this value

    Column Name: B -- this pops up
    Row Name: 2 -- this pops up

    I think this is a reverse of the vlookup function but I don't know how to do it. I've tried a combined INDEX and MATCH function but again you have to select the lookuparray manually, I want it to be automated.

    Is there a formula to do that? can anybody help me out??

    DB:2.76:Given The Table Values, Get The Row And Column Names 8f

    Hi,

    Assumingthe layout which i shared with you in my earlier post, try this

    1. In cell B7, enter this formula

    =INDEX($B$1:$E$1,1,SUMPRODUCT((INDEX(ABS($B$2:$E$4-A7),,)=MIN(INDEX(ABS($B$2:$E$4-A7),,)))*(COLUMN($B$1:$E$1)))-1)

    2. In cell C8, enter this formula

    =INDEX($A$2:$A$4,SUMPRODUCT((INDEX(ABS($B$2:$E$4-A7),,)=MIN(INDEX(ABS($B$2:$E$4-A7),,)))*(ROW(A2:A4)))-1,1)

    Hope this helps.

  • RELEVANCY SCORE 2.76

    DB:2.76:Re: Constraint Based On Existing Values For A Column In The Table 8m


    A unique constraint is a single column or combination of columns that uniquely identify a rows. That means that a row should no have identical rows.

    CREATE TABLE [Table Name]
    ([columns datatype],
    ...
    CONSTRAINT [constraint name] UNIQUE ([colums],...));or if table has been created
    ALTER TABLE [Table Name]
    add CONSTRAINT [constraint name] UNIQUE ([colums],...);

    DB:2.76:Re: Constraint Based On Existing Values For A Column In The Table 8m

    Yeah..
    I checked it as (decode(upper(primary_acct),'Y',upper(primary_acct))

  • RELEVANCY SCORE 2.76

    DB:2.76:Improvement For Constraint Tab Of Table Window dm


    Here an improvement to put on the list...

    When I'm looking at the constraits on a table - select the table and navigate to the constraints tab. I like to know which columns are participating in integrity constraints (RI, PK and Unique). The current tab only shows me the name of the referenced table/constraint or enforcing index, but to see which columns it applies to, I need to navigate to the referenced table (RI), look in the constraints tab to see what index is enforcing the constraint and then navigate to the index tab to see the column names.
    Ow.. 3 steps to see which columns are protected by the constaint and even more difficulty if the constraint is disabled (no index on PK/Unique constraint)

    The constraint tab would be more usable if the column names appeared together with the constraint name. A master-detail display like in the index tab would be perfect.

    Keep up the good work !

    DB:2.76:Improvement For Constraint Tab Of Table Window dm

    We do already have this on our list of enhancement for the future - it would be a big time saver.

    -- Sharon