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

On 23 Jul, 13:33, Robbo <i...@fds7.com> wrote:
> 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

Mysql only requires the combined fields to be unique.

On Jul 23, 1:53 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 23 Jul, 13:33, Robbo <i...@fds7.com> wrote:
>
> > 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
>
> Mysql only requires the combined fields to be unique.


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

On 24 Jul, 09:48, Robbo <i...@fds7.com> wrote:
> On Jul 23, 1:53 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 23 Jul, 13:33, Robbo <i...@fds7.com> wrote:
>
> > > 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
>
> > Mysql only requires the combined fields to be unique.
>
> 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 -

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

On Jul 24, 10:04 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 24 Jul, 09:48, Robbo <i...@fds7.com> wrote:
>
>
>
>
>
> > On Jul 23, 1:53 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 23 Jul, 13:33, Robbo <i...@fds7.com> wrote:
>
> > > > 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
>
> > > Mysql only requires the combined fields to be unique.
>
> > 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 -
>
> You can populate them with the empty string ''
> But you cannot make them NULL- Hide quoted text -
>
> - Show quoted text -


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

On 24 Jul, 10:16, Robbo <i...@fds7.com> wrote:
> On Jul 24, 10:04 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 24 Jul, 09:48, Robbo <i...@fds7.com> wrote:
>
> > > On Jul 23, 1:53 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 23 Jul, 13:33, Robbo <i...@fds7.com> wrote:
>
> > > > > 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
>
> > > > Mysql only requires the combined fields to be unique.
>
> > > 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 -
>
> > You can populate them with the empty string ''
> > But you cannot make them NULL- Hide quoted text -
>
> > - Show quoted text -
>
> Ahhh! Thanks very much. I had wrongly assumed they were the same !
> doh!- Hide quoted text -
>
> - Show quoted text -

I did that once upon a time!