By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,724 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,724 IT Pros & Developers. It's quick & easy.

Primary Keys

P: n/a
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!
Jul 16 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Sun, 17 Aug 2003 15:43:00 +0000 (UTC), "John Simmons"
<Jo**********@hisPC.net> wrote:
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!


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 (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #2

P: n/a

"John Simmons" <Jo**********@hisPC.net> wrote in message
news:bh**********@hercules.btinternet.com...
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!

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
Jul 16 '05 #3

P: n/a
On Sun, 17 Aug 2003 16:00:46 GMT, "Jamie Davison" <ja***@bardavon.org> wrote:
"John Simmons" <Jo**********@hisPC.net> wrote in message
news:bh**********@hercules.btinternet.com...
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!

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`) ;


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 (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #4

P: n/a
Andy Hassall wrote:

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.


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
Jul 16 '05 #5

P: n/a
On Sun, 17 Aug 2003 21:04:13 +0000 (UTC), "John Simmons"
<Jo**********@hisPC.net> wrote:
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_db($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($sql)))
{
die(mysql_error());
}

With this layout I would have expected to get some sort of error report back
about a duplicate key(?)


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 (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #6

P: n/a
Excellent, just what I needed. Thanks for this Andy.

"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:tq********************************@4ax.com...
On Sun, 17 Aug 2003 21:04:13 +0000 (UTC), "John Simmons"
<Jo**********@hisPC.net> wrote:
Ok, see attached zipped html file for details of my layout. And yes, we alldon'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 haveat times.

My PHP code to update the table goes as follows:

mysql_select_db($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($sql)))
{
die(mysql_error());
}

With this layout I would have expected to get some sort of error report backabout a duplicate key(?)
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 (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Jul 16 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.