p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: MySQL UNIQUE fields


Message #1 by "John" <dotsyntax@r...> on Fri, 15 Feb 2002 21:49:07
I have created a table where the first field is a person's email contact. 

The properties of this field are VARCHAR() NOT NULL PRIMARY KEY. The 

problem is that everytime a duplicate contact of the same value tries to 

be entered into this table I get an error.



I am assuming that the field is automatically defined as UNIQUE, I did not 

define it as such. Am I correct when I assume this? If so, how would I get 

around this problem if I wanted to allow duplicate submissions to a 

database table?
Message #2 by John Owen <JOWEN@f...> on Fri, 15 Feb 2002 15:47:58 -0600
I believe any field that is defined as a primary key is going to be

considered unique by any database. To allow multiple records with the same

email address, pair email with another field like username and make that

column pair a unique index.



-----Original Message-----

From: John [mailto:dotsyntax@r...]

Sent: Friday, February 15, 2002 3:49 PM

To: beginning php

Subject: [beginning_php] MySQL UNIQUE fields





I have created a table where the first field is a person's email contact. 

The properties of this field are VARCHAR() NOT NULL PRIMARY KEY. The 

problem is that everytime a duplicate contact of the same value tries to 

be entered into this table I get an error.



I am assuming that the field is automatically defined as UNIQUE, I did not 

define it as such. Am I correct when I assume this? If so, how would I get 

around this problem if I wanted to allow duplicate submissions to a 

database table?




$subst('Email.Unsub').

Message #3 by "Krister" <krister@m...> on Fri, 15 Feb 2002 23:11:39 +0100
Hi



An email field cannot be primary key because its not nessessary unique.

use name like fx. id and int(11) insted of an empty varchar() and add also

auto_increment then it adds numbers by it self, and then you can make it to

primary key. ( not null is correct )



Krister



----- Original Message -----

From: "John" <dotsyntax@r...>

To: "beginning php" <beginning_php@p...>

Sent: Friday, February 15, 2002 9:49 PM

Subject: [beginning_php] MySQL UNIQUE fields





> I have created a table where the first field is a person's email contact.

> The properties of this field are VARCHAR() NOT NULL PRIMARY KEY. The

> problem is that everytime a duplicate contact of the same value tries to

> be entered into this table I get an error.

>

> I am assuming that the field is automatically defined as UNIQUE, I did not

> define it as such. Am I correct when I assume this? If so, how would I get

> around this problem if I wanted to allow duplicate submissions to a

> database table?




$subst('Email.Unsub').

>



Message #4 by David Cameron <dcameron@i...> on Mon, 18 Feb 2002 11:28:03 +1100
This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C1B813.20D51750

Content-Type: text/plain;

	charset="iso-8859-1"



*Every* table in the database should have a primary key that is generated by

the database. This ensures that:

1. each record is going to unique

2. You get a handle to refer to the record

3. You use it for joins

4. It is a very important part of ensuring RI (Referential Integrity) in

your database. MySQL isn't really strong on RI.



Depending on the database you can create tables that use two fields to make

up the primary key, but this only occurs when you are attempting a many to

many join between two tables. I won't go into the detail (unless someone

wants me to), suffice to say if in doubt create a db generated primary key

for the table. For MySQL that data type you want is AUTO_INCREMENT, Access

uses autonumber, SQL Server uses IDENTITY. For MySQL see:

http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#exam

ple-AUTO_INCREMENT





regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Krister [mailto:krister@m...]

Sent: Saturday, 16 February 2002 9:12 AM

To: beginning php

Subject: [beginning_php] Re: MySQL UNIQUE fields





Hi



An email field cannot be primary key because its not nessessary unique.

use name like fx. id and int(11) insted of an empty varchar() and add also

auto_increment then it adds numbers by it self, and then you can make it to

primary key. ( not null is correct )



Krister



----- Original Message -----

From: "John" <dotsyntax@r...>

To: "beginning php" <beginning_php@p...>

Sent: Friday, February 15, 2002 9:49 PM

Subject: [beginning_php] MySQL UNIQUE fields





> I have created a table where the first field is a person's email contact.

> The properties of this field are VARCHAR() NOT NULL PRIMARY KEY. The

> problem is that everytime a duplicate contact of the same value tries to

> be entered into this table I get an error.

>

> I am assuming that the field is automatically defined as UNIQUE, I did not

> define it as such. Am I correct when I assume this? If so, how would I get

> around this problem if I wanted to allow duplicate submissions to a

> database table?




$subst('Email.Unsub').

>








$subst('Email.Unsub').





  Return to Index