Professional Web Applications Themes

need some special key - MySQL

hi i got a table with a lot of datasets, one row is integer. what i need is that only one dataset can have a '1' in this row, every other dataset has to be '0'. sometimes the '1' can move to other datasets, but there has alwas to be only one '1' in the whole row. is it possible to create something like a very special key over this row? thanks for help...

  1. #1

    Default need some special key

    hi

    i got a table with a lot of datasets, one row is integer. what i need
    is that only one dataset can have a '1' in this row, every other
    dataset has to be '0'. sometimes the '1' can move to other datasets,
    but there has alwas to be only one '1' in the whole row. is it possible
    to create something like a very special key over this row?

    thanks for help

    replay Guest

  2. #2

    Default Re: need some special key


    replay wrote: 

    I have no idea what language you are talking, but whatever it is is
    doesn't seem to have much relationship to the language of MySQL (or
    indeed just about any other database system).

    A table has rows. A dataset is a term used in IBM mainframes to refer
    to a file.
    In a table a "row" cannot be integer. A row is made up of fields and
    one (or more) of those fields can be an integer data type.
    A key is a way to uniquely identify a row in a table.

    So, would you like to have a read up on what terms you should be using
    and then try asking your question again, this time in a way that makes
    some sort of sense?

    Captain Guest

  3. #3

    Default Re: need some special key

    lol, sorry my english is bad.

    i got a table with a field of type int. i want that only one line of
    the table can have the value "1" in this field, every other line has to
    have the value "0" in this column. it`s possible that the "1" moves up
    and down in the table, but there has always to be only one "1" in the
    whole column...

    was that understandable?




    On Nov 24, 2:57 pm, "Captain Paralytic" <com> wrote: 


    > doesn't seem to have much relationship to the language of MySQL (or
    > indeed just about any other database system).
    >
    > A table has rows. A dataset is a term used in IBM mainframes to refer
    > to a file.
    > In a table a "row" cannot be integer. A row is made up of fields and
    > one (or more) of those fields can be an integer data type.
    > A key is a way to uniquely identify a row in a table.
    >
    > So, would you like to have a read up on what terms you should be using
    > and then try asking your question again, this time in a way that makes
    > some sort of sense?[/ref]

    replay Guest

  4. #4

    Default Re: need some special key


    replay wrote: 
    Yes, much better!

    Now let me have a think about it.

    Captain Guest

  5. #5

    Default Re: need some special key

    replay wrote: 
    >> 
    >> 
    >>
    >>doesn't seem to have much relationship to the language of MySQL (or
    >>indeed just about any other database system).
    >>
    >>A table has rows. A dataset is a term used in IBM mainframes to refer
    >>to a file.
    >>In a table a "row" cannot be integer. A row is made up of fields and
    >>one (or more) of those fields can be an integer data type.
    >>A key is a way to uniquely identify a row in a table.
    >>
    >>So, would you like to have a read up on what terms you should be using
    >>and then try asking your question again, this time in a way that makes
    >>some sort of sense?[/ref]
    >
    >[/ref]

    There isn't anything you can do with keys or indexes to do this. About
    the only way I can think you could do it is with triggers (you'd need
    both INSERT and UPDATE triggers, and possibly DELETE).

    But the real question here is - WHY do you want only one row to contain
    a '1' in this column. Such a database design is suspect

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

  6. #6

    Default Re: need some special key

    replay wrote:
     
    >> 
    >> 
    >>
    >>doesn't seem to have much relationship to the language of MySQL (or
    >>indeed just about any other database system).
    >>
    >>A table has rows. A dataset is a term used in IBM mainframes to refer
    >>to a file.
    >>In a table a "row" cannot be integer. A row is made up of fields and
    >>one (or more) of those fields can be an integer data type.
    >>A key is a way to uniquely identify a row in a table.
    >>
    >>So, would you like to have a read up on what terms you should be using
    >>and then try asking your question again, this time in a way that makes
    >>some sort of sense?[/ref]
    >
    >[/ref]

    Sounds like you need to attend a Relational database theory class - what you ask
    is not possible from the database perspective. You can write your application
    to do this, but it will be very BAD thing to do and extremely ineffecient (slow).

    If you make a column(field) unique, then it can only have a single row with that
    value. (one row with 1, one row with 0). You can specify that a column is
    enumerated (can only have values 0,1) but you cannot limit the number of rows.

    Can you explain the goal of this method? What problem are you trying to solve?
    How do you know when a row changes from 0 to 1? or from 1 to 0? What if there
    are thousands/millions of rows in that table? This requires reading EVERY row in
    the table for EVERY update or insert.


    --
    Michael Austin.
    Database Consultant.
    Michael Guest

  7. #7

    Default Re: need some special key

    >i got a table with a field of type int. i want that only one line of 

    Ok, you could represent that with constraints:

    sum(field) = 1
    and the allowable values for field are 0 and 1, only.

    Now, how do you go about moving the 1 from one row to another?
    Assume you've got an `id` field which is a primary key of the table
    to identify rows. Assume rows with id of 13 and 42 exist and the
    row with id = 13 has the 1 in field.

    update table set field = 1 where id = 42;
    update table set field = 0 where id = 13;

    The first query will fail because the constraint is violated.
    If you reverse the order of the queries, same problem.

    update table set field = 1 - field where id in (13, 42);

    might work if the implementation of constraints makes *all* of the
    changes specified by a given query, then checks them. This is very
    awkward to deal with. Also, why do you need this?


    One possibility is to define another table, say, selected_row_in_table,
    which always has exactly one row in it with one field which contains
    the primary key of a row in `table`. Set it to point at whatever
    row you want. A foreign key constraint would prevent you from
    pointing it at a nonexistent row.

    Gordon Guest

  8. #8

    Default Re: need some special key

    hi together

    the reason for this question is: i got many profiles for something,
    thay are stored in a table. one profile is the default, so i created a
    field "default_profile" with a 0 or a 1 in it. i know its not very
    beautiful for a relational database to solve this that way. probably i
    will create one more table with only on row, which points to the id of
    the default profile.

    thank you for your help





    Gordon Burditt wrote: 
    >
    > Ok, you could represent that with constraints:
    >
    > sum(field) = 1
    > and the allowable values for field are 0 and 1, only.
    >
    > Now, how do you go about moving the 1 from one row to another?
    > Assume you've got an `id` field which is a primary key of the table
    > to identify rows. Assume rows with id of 13 and 42 exist and the
    > row with id = 13 has the 1 in field.
    >
    > update table set field = 1 where id = 42;
    > update table set field = 0 where id = 13;
    >
    > The first query will fail because the constraint is violated.
    > If you reverse the order of the queries, same problem.
    >
    > update table set field = 1 - field where id in (13, 42);
    >
    > might work if the implementation of constraints makes *all* of the
    > changes specified by a given query, then checks them. This is very
    > awkward to deal with. Also, why do you need this?
    >
    >
    > One possibility is to define another table, say, selected_row_in_table,
    > which always has exactly one row in it with one field which contains
    > the primary key of a row in `table`. Set it to point at whatever
    > row you want. A foreign key constraint would prevent you from
    > pointing it at a nonexistent row.[/ref]

    replay Guest

  9. Moderated Post

    Default Re: need some special key

    Removed by Administrator
    Peter Guest
    Moderated Post

Similar Threads

  1. paste special?
    By madillo2006 in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: October 6th, 11:31 AM
  2. special authentication
    By Peter Kornills in forum ASP.NET Security
    Replies: 5
    Last Post: March 1st, 01:38 PM
  3. Paste Special
    By Moey@adobeforums.com in forum Adobe Indesign Windows
    Replies: 10
    Last Post: May 20th, 04:54 PM
  4. special effects
    By Roofy webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 8
    Last Post: October 13th, 08:42 AM
  5. special tags
    By Lloyd Dupont in forum ASP.NET General
    Replies: 2
    Last Post: July 20th, 02:52 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