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)
