Professional Web Applications Themes

Primary index issue - MySQL

Hi, With Mysql databases Is there a way around the situation where, unlike other databases mysql will not allow a primary 2 field index without each field being unique ? I'm used to paradox where the combined fields have to be unique i.e. partno supplier ERR3340 MAIN ERR3340 ALLM ERR3340 BORD ERR3340 PREM TIA...

  1. #1

    Default Primary index issue

    Hi,

    With Mysql databases Is there a way around the situation where, unlike
    other databases mysql will not allow a primary 2 field index without
    each field being unique ? I'm used to paradox where the combined
    fields have to be unique i.e.


    partno supplier
    ERR3340 MAIN
    ERR3340 ALLM
    ERR3340 BORD
    ERR3340 PREM

    TIA

    Robbo Guest

  2. #2

    Default Re: Primary index issue

    On 23 Jul, 13:33, Robbo <com> wrote: 

    Mysql only requires the combined fields to be unique.

    Captain Guest

  3. #3

    Default Re: Primary index issue

    On Jul 23, 1:53 pm, Captain Paralytic <com> wrote: 



    >
    > Mysql only requires the combined fields to be unique.[/ref]


    I ommited to show in the above example that certain supplier fileds
    can be null. That said the combination of the 2 field can and still
    should be unqiue within the dataset e.g.

    The full table is as follows

    partno supplier part_id

    ABC123 ABC0010110
    GFE187 ABC0010110
    ERR3340 MAIN ABC0010110
    ERR3340 ALLM ABC0010110
    ERR3340 BORD ABC0010110
    ERR3340 PREM ABC0010110


    The part_id is a field that links all superseeded (connected or
    linked) parts. The fact that the supplier field is null is that that
    partno has been sup[erseeded to one of the other ones connected by the
    part_id field.

    In this case I get an error when trying to create the index

    MySQL said:

    #1171 - All parts of a PRIMARY KEY must be NOT NULL; If you need NULL
    in a key, use UNIQUE instead

    Does this mean that I have to poulate the blank supplier fields with
    say "SUPER" ?

    TIA

    Robbo Guest

  4. #4

    Default Re: Primary index issue

    On 24 Jul, 09:48, Robbo <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > I ommited to show in the above example that certain supplier fileds
    > can be null. That said the combination of the 2 field can and still
    > should be unqiue within the dataset e.g.
    >
    > The full table is as follows
    >
    > partno supplier part_id
    >
    > ABC123 ABC0010110
    > GFE187 ABC0010110
    > ERR3340 MAIN ABC0010110
    > ERR3340 ALLM ABC0010110
    > ERR3340 BORD ABC0010110
    > ERR3340 PREM ABC0010110
    >
    > The part_id is a field that links all superseeded (connected or
    > linked) parts. The fact that the supplier field is null is that that
    > partno has been sup[erseeded to one of the other ones connected by the
    > part_id field.
    >
    > In this case I get an error when trying to create the index
    >
    > MySQL said:
    >
    > #1171 - All parts of a PRIMARY KEY must be NOT NULL; If you need NULL
    > in a key, use UNIQUE instead
    >
    > Does this mean that I have to poulate the blank supplier fields with
    > say "SUPER" ?
    >
    > TIA- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    You can populate them with the empty string ''
    But you cannot make them NULL

    Captain Guest

  5. #5

    Default Re: Primary index issue

    On Jul 24, 10:04 am, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]











    >
    > You can populate them with the empty string ''
    > But you cannot make them NULL- Hide quoted text -
    >
    > - Show quoted text -[/ref]


    Ahhh! Thanks very much. I had wrongly assumed they were the same !
    doh!

    Robbo Guest

  6. #6

    Default Re: Primary index issue

    On 24 Jul, 10:16, Robbo <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Ahhh! Thanks very much. I had wrongly assumed they were the same !
    > doh!- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    I did that once upon a time!

    Captain Guest

Similar Threads

  1. default index for primary key of a table
    By Greg Stark in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 24th, 08:15 PM
  2. converting unique index into primary key
    By Ed L. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 23rd, 03:42 AM
  3. default index created for primary key
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 7
    Last Post: December 22nd, 07:30 PM
  4. Changing Primary Index Question
    By Bill Hamilton in forum Informix
    Replies: 8
    Last Post: September 4th, 05:29 PM
  5. is primary key implicitly included in an index?
    By Zig Mandel in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 09:53 AM

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