CODEWITHSUNDEEP
phpmysql
Bulvak
Bulvak

Reputation: 1804

MySQL UNIQUE constraint..How do I fix this bug?

Firstly, by using Primary key am I applying the UNIQUE constraint properly?

<?php
// Make a MySQL Connection
mysql_connect("localhost", "oassda", "oas53a") or die(mysql_error());
mysql_select_db("o345ja") or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE PersonInfo(
Email VARCHAR(45) NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Email),
 PNumber VARCHAR(45))")
 or die(mysql_error());  

echo "Table Created!";

?>

I am trying to make it so Email and PNumber cannot have duplicate rows inserted..But the error I get is "Incorrect column specifier for column 'Email'" apparently this is a bug due to AUTO_INCREMENT only working on INT types....Any idea what to do to make these 2 columns without duplicates ? sorry I am not experienced with MySQL too much as I did it very long time ago and new bugs have developed since then..

Upvotes: 0

Views: 348

Answers (3)

Matt Fellows
Matt Fellows

Reputation: 6522

Are you looking for a composite primary key? If so this is the code that will fix your error.

mysql_query("CREATE TABLE PersonInfo(
Email VARCHAR(45) NOT NULL, 
 PNumber VARCHAR(45),
PRIMARY KEY(Email,PNumber))")
 or die(mysql_error());

If not you just want a primary key - the AUTO_INCREMENT flag is for INT type only.

mysql_query("CREATE TABLE PersonInfo(
    Email VARCHAR(45) NOT NULL, 
     PNumber VARCHAR(45),
    PRIMARY KEY(Email))")
     or die(mysql_error());

Or possibly you just need the UNIQUE constraint eg.

mysql_query("CREATE TABLE PersonInfo(
        Email VARCHAR(45) NOT NULL, 
         PNumber VARCHAR(45),
        PRIMARY KEY(Email),
        UNIQUE(PNumber))")
         or die(mysql_error());

Upvotes: 2

Alex Howansky
Alex Howansky

Reputation: 53543

  • Take out the AUTO_INCREMENT, it's for integers and makes no sense on a field that you'll be manually populating.
  • You don't need NOT NULL since primary key makes it implicit.

  • Think about making your fields wider. I have email addrs longer than 45 chars. (I.e., because of TMDA.)

    CREATE TABLE PersonInfo
(
    Email VARCHAR(45),
    PNumber VARCHAR(45),
    PRIMARY KEY (Email, PNumber)
);

Upvotes: 1

Aurelio De Rosa
Aurelio De Rosa

Reputation: 22152

  • Yes, using Primary Key you'll have no duplicated email (you're not adding the PNumber field too)
  • You are using AUTO_INCREMENT on a field that is not an integer (or a sub-type like smallint).

Upvotes: 1

Related Questions