Primary Keys

**Andy Hassall** · Jul 16 '05, 11:27 PM

Re: Primary Keys

On Sun, 17 Aug 2003 15:43:00 +0000 (UTC), "John Simmons"
<John.Simmons@h isPC.net> wrote:
[color=blue]
>How is it that even though I have the column "username" in my database set
>as a Primary key, using my PHP script to add new users to the database works
>without any errors even when signing up using an existing username. I have a
>database full of the same usernames![/color]

Which database? Can you post your table definition?

Without more information the only likely reasons are (a) you haven't actually
added the primary key correctly or (b) your database is severely broken.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

**Jamie Davison** · Jul 16 '05, 11:27 PM

Re: Primary Keys

"John Simmons" <John.Simmons@h isPC.net> wrote in message
news:bho7q4$o7o $1@hercules.bti nternet.com...[color=blue]
> How is it that even though I have the column "username" in my database set
> as a Primary key, using my PHP script to add new users to the database[/color]
works[color=blue]
> without any errors even when signing up using an existing username. I have[/color]
a[color=blue]
> database full of the same usernames!
>
>[/color]
PHP allows duplicate Primary Keys though it is not recommended at all. You
must specify that your primary key be "UNIQUE" when you add the primary key
field.

At your MySQL command prompt Try:

ALTER TABLE `thetable` ADD UNIQUE (`username`) ;

-JD

**Andy Hassall** · Jul 16 '05, 11:27 PM

Re: Primary Keys

On Sun, 17 Aug 2003 16:00:46 GMT, "Jamie Davison" <jamie@bardavon .org> wrote:
[color=blue]
>"John Simmons" <John.Simmons@h isPC.net> wrote in message
>news:bho7q4$o7 o$1@hercules.bt internet.com...[color=green]
>> How is it that even though I have the column "username" in my database set
>> as a Primary key, using my PHP script to add new users to the database
>> works without any errors even when signing up using an existing username. I have
>> a database full of the same usernames!
>>[/color]
>PHP allows duplicate Primary Keys though it is not recommended at all. You
>must specify that your primary key be "UNIQUE" when you add the primary key
>field.
>
>At your MySQL command prompt Try:
>
>ALTER TABLE `thetable` ADD UNIQUE (`username`) ;[/color]

Would you mind backing up this statement with some evidence please? PHP has
_nothing_ to do with primary keys, it's entirely the database's responsibility.
You can never have duplicate primary keys in a table; it's a contradiction.

A unique key constraint enforces uniqueness of a set of fields, which may
contain NULL values. You can have more than one unique key constraint on a
table.

A primary key constraint enforces uniqueness of a set of fields, which may NOT
contain NULL values. You can have only one primary key constraint on a table. A
primary key is also a unique identity of the entity represented by the row.

Since a primary key is more restrictive than a unique key, there's no point
adding a unique key on top of a primary key constraint (and you can't, anyway).

mysql> create table pktest (id int not null);
Query OK, 0 rows affected (0.04 sec)

mysql> alter table pktest add primary key (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table pktest add unique (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc pktest;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

The 'unique' specification gets ignored as it's redundant. Other databases may
reject it with an error (e.g. Oracle would say 'such column list already
indexed').

mysql> insert into pktest values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into pktest values (1);
ERROR 1062: Duplicate entry '1' for key 1

Duplicates not allowed; it's a primary key.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

**matty** · Jul 16 '05, 11:27 PM

Re: Primary Keys

Andy Hassall wrote:

[color=blue]
> mysql> insert into pktest values (1);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into pktest values (1);
> ERROR 1062: Duplicate entry '1' for key 1
>
> Duplicates not allowed; it's a primary key.
>[/color]

I reckon he's not checking in his code to see if MySQL
raised an error; i.e., just executing the INSERT
and carrying on regardless...

Another case of "Please check mysql_error()". ..

Still working on the faq/wiki thing; just a bit worried
about potential bandwidth, although I may be able to get
it free on tuxfamily.org, given the nature of the site

Matt

**Andy Hassall** · Jul 16 '05, 11:27 PM

Re: Primary Keys

On Sun, 17 Aug 2003 21:04:13 +0000 (UTC), "John Simmons"
<John.Simmons@h isPC.net> wrote:
[color=blue]
>Ok, see attached zipped html file for details of my layout. And yes, we all
>don't
>like attachments in posts but it's far easier to read in this format than
>trying to line up columns in a post. Please also bare in mind, I've only
>been at this for a few weeks so I'm not asking for you to "Please check
>mysql_error()" ..., I'm simply asking for some advice as I am sure you have
>at times.
>
>My PHP code to update the table goes as follows:
>
>mysql_select_d b($DB);
>$sql = "INSERT INTO " . $Table . " VALUES('','" . $_POST['username'] . "','"
>. md5($_POST['password']) . "','" . $_POST['firstname'] . "','" .
>$_POST['lastname'] . "','" . $_POST['email'] . "','" . $_POST['address1'] .
>"','" . $_POST['address2'] . "','" . $_POST['county'] . "','" .
>$_POST['postcode'] . "','" . $_POST['country'] . "')";
>if(!($result = mysql_query($sq l)))
>{
> die(mysql_error ());
>}
>
>With this layout I would have expected to get some sort of error report back
>about a duplicate key(?)[/color]

From the attached file:

Fields

Field Type Null Key Default Extra
ID
smallint(3)

PRI
(NULL)
auto_increment

username
varchar(15)

PRI

i.e. you have a composite primary key of (ID, username), meaning you're only
limiting unique usernames per value of ID. Since ID is auto_increment, you get
a new one each time.

What you want is primary key (ID), unique (username).

alter table member drop primary key;
alter table member add primary key (ID);
alter table member add unique (username);

You'll have to sort out the duplicates first, though.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

**John Simmons** · Jul 16 '05, 11:27 PM

Re: Primary Keys

Excellent, just what I needed. Thanks for this Andy.

"Andy Hassall" <andy@andyh.co. uk> wrote in message
news:tqrvjv0to4 bcbr3lj7fleetqr 24t95h9ts@4ax.c om...[color=blue]
> On Sun, 17 Aug 2003 21:04:13 +0000 (UTC), "John Simmons"
> <John.Simmons@h isPC.net> wrote:
>[color=green]
> >Ok, see attached zipped html file for details of my layout. And yes, we[/color][/color]
all[color=blue][color=green]
> >don't
> >like attachments in posts but it's far easier to read in this format than
> >trying to line up columns in a post. Please also bare in mind, I've only
> >been at this for a few weeks so I'm not asking for you to "Please check
> >mysql_error()" ..., I'm simply asking for some advice as I am sure you[/color][/color]
have[color=blue][color=green]
> >at times.
> >
> >My PHP code to update the table goes as follows:
> >
> >mysql_select_d b($DB);
> >$sql = "INSERT INTO " . $Table . " VALUES('','" . $_POST['username'] .[/color][/color]
"','"[color=blue][color=green]
> >. md5($_POST['password']) . "','" . $_POST['firstname'] . "','" .
> >$_POST['lastname'] . "','" . $_POST['email'] . "','" . $_POST['address1'][/color][/color]
..[color=blue][color=green]
> >"','" . $_POST['address2'] . "','" . $_POST['county'] . "','" .
> >$_POST['postcode'] . "','" . $_POST['country'] . "')";
> >if(!($result = mysql_query($sq l)))
> >{
> > die(mysql_error ());
> >}
> >
> >With this layout I would have expected to get some sort of error report[/color][/color]
back[color=blue][color=green]
> >about a duplicate key(?)[/color]
>
> From the attached file:
>
> Fields
>
> Field Type Null Key Default Extra
> ID
> smallint(3)
>
> PRI
> (NULL)
> auto_increment
>
> username
> varchar(15)
>
> PRI
>
>
> i.e. you have a composite primary key of (ID, username), meaning you're[/color]
only[color=blue]
> limiting unique usernames per value of ID. Since ID is auto_increment, you[/color]
get[color=blue]
> a new one each time.
>
> What you want is primary key (ID), unique (username).
>
> alter table member drop primary key;
> alter table member add primary key (ID);
> alter table member add unique (username);
>
> You'll have to sort out the duplicates first, though.
>
> --
> Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)[/color]

Primary Keys

Primary Keys

Comment

Comment

Comment

Comment

Comment

Comment