Professional Web Applications Themes

dropping uniqueness of a field - MySQL

I have a unique constraint on a field that is neither a primary key nor an index field. How do I remove the unique constraint without deleting the field? Thanks John...

  1. #1

    Default dropping uniqueness of a field

    I have a unique constraint on a field that is neither a primary key
    nor an index field.
    How do I remove the unique constraint without deleting the field?

    Thanks
    John

    john7 Guest

  2. #2

    Default Re: dropping uniqueness of a field

    john7 <com> wrote:
     

    ALTER TABLE table_name DROP KEY key_name;
    --
    Rik Wasmus
    Posted on Usenet: any site claiming this as original content or me as an
    contributing member is stealing content.
    Ask Smart Questions: http://tinyurl.com/anel
    Rik Guest

  3. #3

    Default Re: dropping uniqueness of a field

    On Mar 5, 11:14 am, Rik <com> wrote: 
    >
    > ALTER TABLE table_name DROP KEY key_name;
    > --
    > Rik Wasmus
    > Posted on Usenet: any site claiming this as original content or me as an
    > contributing member is stealing content.
    > Ask Smart Questions:http://tinyurl.com/anel[/ref]

    Hi Rik,
    Thanks gain for answering my questions.
    I just wanted to let you know that I am using MySQL 3.3. I tested it
    and it did not work. I did see a comment from someone in MySQL site
    that it is has been tested for version 5.0.11 to 5.0.15.

    Thanks
    John

    john7 Guest

  4. #4

    Default Re: dropping uniqueness of a field

     

    What was the error message? Please be as specific as possible
    when asking questions.

    You started out with:
    - no MySQL version
    - no record of what you've tried
    - no metadata as extracted by the mysql tools

    Do you think we can guess what your system looks like? :)


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  5. #5

    Default Re: dropping uniqueness of a field

    Martijn Tonies wrote: 
    >
    > What was the error message? Please be as specific as possible
    > when asking questions.
    >
    > You started out with:
    > - no MySQL version
    > - no record of what you've tried
    > - no metadata as extracted by the mysql tools
    >
    > Do you think we can guess what your system looks like? :)[/ref]

    Ys, it astonishes me the amount of people who think "it does not work" is a
    good description of a problem!


    Paul Guest

  6. #6

    Default Re: dropping uniqueness of a field

    On Mar 5, 3:04 pm, "Paul Lautman" <com> wrote: [/ref]



    >
    > Ys, it astonishes me the amount of people who think "it does not work" is a
    > good description of a problem![/ref]

    I don't think you know the answer to the question. Why would an error
    message help? The question is, is it implemented in MySQL 3.3?

    john7 Guest

  7. #7

    Default Re: dropping uniqueness of a field

    I forgot to put the version in the first post, but I did said the
    version is 3.3 later and I said I am trying to drop uniqueness
    constraint from a field. There is nothing complicated here that would
    need examples and error messages. The question is can uniqueness
    constraint be removed from a field in MySQL 3.3?

    On Mar 5, 2:58 pm, "Martijn Tonies" <removethis.com>
    wrote: 
    >
    > What was the error message? Please be as specific as possible
    > when asking questions.
    >
    > You started out with:
    > - no MySQL version
    > - no record of what you've tried
    > - no metadata as extracted by the mysql tools
    >
    > Do you think we can guess what your system looks like? :)
    >
    > --
    > Martijn Tonies
    > Database Workbench - development tool for MySQL, and more!
    > Upscene Productionshttp://www.upscene.com
    > My thoughts:http://blog.upscene.com/martijn/
    > Database development questions? Check the forum!http://www.databasedevelopmentforum.com[/ref]


    john7 Guest

  8. #8

    Default Re: dropping uniqueness of a field

    john7 wrote: 
    >> Ys, it astonishes me the amount of people who think "it does not work" is a
    >> good description of a problem![/ref]
    >
    > I don't think you know the answer to the question. Why would an error
    > message help? The question is, is it implemented in MySQL 3.3?
    >[/ref]

    An error message helps because it tells us exactly what MySQL is
    complaining about. "It doesn't work" is not a real description of the
    problem.

    So yes - an error message helps. If they weren't important, why would
    MySQL go to the trouble of implementing them? They could just return
    "it doesn't work" for any error.

    And I think you're an .

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  9. #9

    Default Re: dropping uniqueness of a field

    On Mar 5, 7:14 pm, Jerry Stuckle <net> wrote: [/ref]

    >
    > An error message helps because it tells us exactly what MySQL is
    > complaining about. "It doesn't work" is not a real description of the
    > problem.
    >
    > So yes - an error message helps. If they weren't important, why would
    > MySQL go to the trouble of implementing them? They could just return
    > "it doesn't work" for any error.
    >
    > And I think you're an .
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    An error message helps in certain cases but not when you only want to
    know if a feature is implemented in a certain version


    john7 Guest

  10. #10

    Default Re: dropping uniqueness of a field

    john7 wrote: 
    >> An error message helps because it tells us exactly what MySQL is
    >> complaining about. "It doesn't work" is not a real description of the
    >> problem.
    >>
    >> So yes - an error message helps. If they weren't important, why would
    >> MySQL go to the trouble of implementing them? They could just return
    >> "it doesn't work" for any error.
    >>
    >> And I think you're an .
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > An error message helps in certain cases but not when you only want to
    > know if a feature is implemented in a certain version
    >
    >[/ref]

    If you want to know if a certain feature is implemented in a certain
    version, check the doc at www.mysql.com. It's all in there.

    If you want help with a problem, ask here. And give the error message.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  11. #11

    Default Re: dropping uniqueness of a field

    On Mar 5, 10:40 pm, Jerry Stuckle <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > If you want to know if a certain feature is implemented in a certain
    > version, check the doc atwww.mysql.com. It's all in there.
    >
    > If you want help with a problem, ask here. And give the error message.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    You have only given a link to mysql.com. I have to that site thousands
    of times and I have not seen anywhere they indicate what features are
    in which version. That is something similar to what PHP has in their
    site can be useful.
    My first question was regarding if the feature exists. Then Rik
    mentioned it exists but I found it does not work in 3.3. So after my
    second post, the question was really whether the feature is in 3.3.
    You are only trying to be rude without contributing anything
    meaningful, you would better of spending your time doing something else

    john7 Guest

  12. #12

    Default Re: dropping uniqueness of a field

     

    Apparently, not the way you're trying to do it.

    Then again, we have no idea what you're doing cause you're
    not telling us.


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  13. #13

    Default Re: dropping uniqueness of a field

    On 6 Mar, 06:14, "john7" <com> wrote: 
    Your first question was:
    "How do I remove the unique constraint without deleting the field?"

    No mention of features or existance.

    Captain Guest

  14. #14

    Default Re: dropping uniqueness of a field

    john7 wrote: 
    >> If you want to know if a certain feature is implemented in a certain
    >> version, check the doc atwww.mysql.com. It's all in there.
    >>
    >> If you want help with a problem, ask here. And give the error message.
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > You have only given a link to mysql.com. I have to that site thousands
    > of times and I have not seen anywhere they indicate what features are
    > in which version. That is something similar to what PHP has in their
    > site can be useful.
    > My first question was regarding if the feature exists. Then Rik
    > mentioned it exists but I found it does not work in 3.3. So after my
    > second post, the question was really whether the feature is in 3.3.
    > You are only trying to be rude without contributing anything
    > meaningful, you would better of spending your time doing something else
    >[/ref]

    RTFM. It's all in the doc.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  15. #15

    Default Re: dropping uniqueness of a field

    On 6 Mar, 09:43, "Martijn Tonies" <removethis.com>
    wrote: 
    >
    > Apparently, not the way you're trying to do it.
    >
    > Then again, we have no idea what you're doing cause you're
    > not telling us.
    >
    > --
    > Martijn Tonies
    > Database Workbench - development tool for MySQL, and more!
    > Upscene Productionshttp://www.upscene.com
    > My thoughts:http://blog.upscene.com/martijn/
    > Database development questions? Check the forum!http://www.databasedevelopmentforum.com[/ref]

    Of course he hasn't. He already knows so much that he has decided
    precisely what we all need to know in order to be able to help him
    (despite us all telling him that we need more):

    "An error message helps in certain cases but not..."

    Captain Guest

  16. #16

    Default Re: dropping uniqueness of a field

    My apologies to all. I think I was in a defensive position when I felt
    attacked.
    Ok, let me be more for specific.
    I have a varchar field called it xyz whose only contstraint is
    uniqueness. In MySQL doentation, there is nothing like
    ALTER TABLE table_name DROP Unique xyz;
    Ss I tried ALTER TABLE table_name DROP index xyz; and ALTER TABLE
    table_name DROP key xyz;
    and I get an error that no key 'xyz' exist
    I tried ALTER TABLE table_name CHANGE xyz varchar (20)
    worked without any error but the unique constraint still persisted.

    John

    On Mar 6, 4:37 am, "Captain Paralytic" <com> wrote: [/ref]



    >
    > Of course he hasn't. He already knows so much that he has decided
    > precisely what we all need to know in order to be able to help him
    > (despite us all telling him that we need more):
    >
    > "An error message helps in certain cases but not..."- Hide quoted text -
    >
    > - Show quoted text -[/ref]


    john7 Guest

  17. #17

    Default Re: dropping uniqueness of a field

    john7 wrote: 
    >> Of course he hasn't. He already knows so much that he has decided
    >> precisely what we all need to know in order to be able to help him
    >> (despite us all telling him that we need more):
    >>
    >> "An error message helps in certain cases but not..."- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    >
    > My apologies to all. I think I was in a defensive position when I felt
    > attacked.
    > Ok, let me be more for specific.
    > I have a varchar field called it xyz whose only contstraint is
    > uniqueness. In MySQL doentation, there is nothing like
    > ALTER TABLE table_name DROP Unique xyz;
    > Ss I tried ALTER TABLE table_name DROP index xyz; and ALTER TABLE
    > table_name DROP key xyz;
    > and I get an error that no key 'xyz' exist
    > I tried ALTER TABLE table_name CHANGE xyz varchar (20)
    > worked without any error but the unique constraint still persisted.
    >
    > John[/ref]

    (Top posting fixed)

    John,

    You index may be called anything - it doesn't have to be xyz.

    Try from a command prompt issuing

    SHOW INDEX FROM table_name;

    See what indexes you have. Alternatively, you can issue

    P.S. Please don't top post. Thanks.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  18. #18

    Default Re: dropping uniqueness of a field


     

    This is a fine description of your problem and we can spot your
    mistake.

    As Jerry said, it's an index that has a name.

    Uniqueness isn't enforced on the column level, but is done by
    creating an additional object -> mostly a so-called "unique
    constraint" and in older versions of MySQL a "unique index".
    Sometimes a unique constraint is also called a "secondary
    key", as opposed to a primary key.

    Now, the ALTER TABLE command allows you to drop such
    an index (that enforces uniqueness for a column or a combined
    set of columns) by specifying the index name. Jerry told you how
    you can figure out the available indices for your table.

    Do you now see how a better description can help us to help you?

    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  19. #19

    Default Re: dropping uniqueness of a field

    On Mar 6, 5:15 pm, "Martijn Tonies" <remove.com>
    wrote: 
    >
    > This is a fine description of your problem and we can spot your
    > mistake.
    >
    > As Jerry said, it's an index that has a name.
    >
    > Uniqueness isn't enforced on the column level, but is done by
    > creating an additional object -> mostly a so-called "unique
    > constraint" and in older versions of MySQL a "unique index".
    > Sometimes a unique constraint is also called a "secondary
    > key", as opposed to a primary key.
    >
    > Now, the ALTER TABLE command allows you to drop such
    > an index (that enforces uniqueness for a column or a combined
    > set of columns) by specifying the index name. Jerry told you how
    > you can figure out the available indices for your table.
    >
    > Do you now see how a better description can help us to help you?
    >
    > --
    > Martijn Tonies
    > Database Workbench - development tool for MySQL, and more!
    > Upscene Productionshttp://www.upscene.com
    > My thoughts:http://blog.upscene.com/martijn/
    > Database development questions? Check the forum!http://www.databasedevelopmentforum.com[/ref]

    Thank you very much. It worked ok. The field had a different key name.
    Next time I will be giving examples and error messages

    john7 Guest

Similar Threads

  1. Contribute not dropping old URL
    By GDPWATSON in forum Macromedia Contribute Connection Administrtion
    Replies: 2
    Last Post: January 12th, 02:26 PM
  2. Flash is Dropping Passed URL.field Info
    By bkspeck in forum Coldfusion Flash Integration
    Replies: 0
    Last Post: December 7th, 03:43 AM
  3. characters dropping out when sent to RIP
    By Lauri_Lange@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 6
    Last Post: October 25th, 11:16 PM
  4. Creating uniqueness in complex data structures
    By Paul Harwood in forum PERL Beginners
    Replies: 4
    Last Post: November 24th, 06:16 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139