Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    26

    Unanswered: Rename a column with unique constraint on it

    Hi,

    I need to write a script to rename a column. Problem is that there is a unique constraint on that column which has to be removed first in order to be able to rename the column. As this contraint is automagically created I don't know its name (it might differ from time to time). How can I determine the constraints name in my script.

    I am looking for something like this:

    Code:
    ALTER TABLE <TABLE> DROP UNIQUE (SELECT UNIQUE_CONSTRAINT_NAME FROM <TABLE>.<FIELD>)
    Thanks
    Olel

  2. #2
    Join Date
    Oct 2009
    Posts
    26
    I found out that I could use

    Code:
    select CONSTNAME from syscat.keycoluse where tabname = '<TABLE>' AND colname = '<FIELD>'
    but I cannot use it like

    Code:
    ALTER TABLE <TABLE> DROP UNIQUE (select CONSTNAME from syscat.keycoluse where tabname = '<TABLE>' AND colname = '<FIELD>')
    as it is syntactically wrong.

    Any ideas?

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Your db2 version+fixpack and db2-server operating system ?

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    select 'ALTER TABLE '||rtrim(tabcreator)||'.'||tabname.... from syscat... where
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, why not explicitly name your constraints? I know its easy to just let the system handle that and primary keys, etc... but it is a best practice to have all of that identified. Makes it easy to ensure you have the same definitions on other environments as you may have run this on a test system after having performed a redirected restore from your prod environment. Now if do any kind of cross check you will have names that do not match. Very simple to do in most modeling products with proper naming conventions and they will spit out the DDL to create your tables, name your PKs, FKs and UCs.
    Dave

Posting Permissions

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