473,574 Members | 3,213 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Primary Keys

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
6 3571
On Sun, 17 Aug 2003 15:43:00 +0000 (UTC), "John Simmons"
<Jo**********@h isPC.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

"John Simmons" <Jo**********@h isPC.net> wrote in message
news:bh******** **@hercules.bti nternet.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
On Sun, 17 Aug 2003 16:00:46 GMT, "Jamie Davison" <ja***@bardavon .org> wrote:
"John Simmons" <Jo**********@h isPC.net> wrote in message
news:bh******* ***@hercules.bt internet.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
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
On Sun, 17 Aug 2003 21:04:13 +0000 (UTC), "John Simmons"
<Jo**********@h isPC.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_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(?)


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
Excellent, just what I needed. Thanks for this Andy.

"Andy Hassall" <an**@andyh.co. uk> wrote in message
news:tq******** *************** *********@4ax.c om...
On Sun, 17 Aug 2003 21:04:13 +0000 (UTC), "John Simmons"
<Jo**********@h isPC.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_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 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
49807
by: Philip Yale | last post by:
I'm probably going to get shot down with thousands of reasons for this, but I've never really heard or read a convincing explanation, so here goes ... Clustered indexes are more efficient at returning large numbers of records than non-clustered indexes. Agreed? (Assuming the NC index doesn't cover the query, of course) Since it's only...
7
2946
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am trying to insert. I therefore do not think that I can use the sql "Insert Into" command. Here is a simplified illustration of my tables: ...
5
4534
by: Geoff Cayzer | last post by:
At http://www.blueclaw-db.com/tips_tricks.htm I came across a section which is included below and was hoping for some comment on the article. -------------- Almost never use this auto-number field as the primary key of the table. If you are thinking of hiring an Access programmer or consultant ask
7
5343
by: Philip | last post by:
Hey all, (Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table needs a unique ID# based on its context. Primary Keys AUTHORS = AuthorID - NO Duplicates
18
12636
by: Thomas A. Anderson | last post by:
I am a bit confused in creating a composite primary key. I have three table with two of the tables containing primary keys. I have two of the tables (each with a primary key) having one to many relations with the table containing no primary key. Do I just create two primary keys on the table that does not contain any primary key for this to...
7
2455
by: Dave | last post by:
Hi, Maybe I'm missing something with the DataKeyField attribute of a datagrid but it seems that it's somewhat limiting since this only allows you to specify one field as the key. I have a table that has two keys and it seems you need this value to find the corresponding record in the DataSet in order to update the data from the Datagrid.
1
1852
by: GGerard | last post by:
Hello I'm trying to find the best way to set indexes and primary keys on MSAccess tables What are the advantages and disadvantages of indexes and primary keys? What fields should be indexed? Should I index more than one field per table? What fields should have the primary key?
9
3898
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for eg: area.txt, school.txt, students.txt.... and so on (ok?!?) now, 1. area code used in the school.txt must be defined in the area.txt (Primary key...
115
6201
by: LurfysMa | last post by:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can I use them as primary keys or should I define an autonum primary key? One table has information about the 50 states in the US. The table looks...
2
9150
by: Danny | last post by:
Hello, We imported a bunch of tables from a database and realized that the primary keys weren't copied to the destination db. In order to re- create the keys, we need to know which tables have them. Is there a command that I can use (on the source db) to find out which tables contain primary keys? The db has hundreds of tables and I'd...
0
7843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8111
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5336
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3776
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3795
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2282
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1375
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1102
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.