Primary Key Conundrum

[xr6vn9z]

I have a create table script that ends with:

 

[php:1:d3e9bee9d7]<php

$login=\"login code\"...

 

CREATE TABLE Blah (

blah blah(n) not blah,

blah blah(n) not blah,

blah blah(n) not blah blah \"blah.blah\",

PRIMARY KEY(petName,petColor) );

 

?>[/php:1:d3e9bee9d7]

 

Then it goes on to describe the last line:

The primary key must be unique. For this table, two columns together are the primary key - this column and the petName column. MySQL won\'t allow two or more rows to be entered with the same petName and petColor.

 

You cannot have more than one primary key so I\'m totally confuseded. :shock:

 

Can someone PLEASE translate that into n00bie english? And how do I do what she is trying to tell me to, with PHP code?

[barbatruc]

It doesn\'t have anything to do with PHP.. it only describes the rules about the way data is recorded into your database.

 

A primary key is a set of fields (1 or more) than has a restriction: there is no possibility of having two rows with the same values into it (or at least in the fields contained in the Primary key). For example, if you have 4 pets in your table:

 

[petName], [petColor]

Bob, brown

Polo, white

Rex, Black

Roxy, Blue (?!?)

 

this will be legal because there are no pets with twice the same name and the same color at the time. The following would be legal too, even if you have Bob twice:

 

Bob, brown

Polo, white

Bob, yellow

Rex, Black

 

The following would be illegal because \"Bob, brown\"would be find twice, which is incorrect (the set petName and petColor is the same...):

 

Bob, brown

Polo, white

Bob, brown

Rex, Black

 

What this all means? If you try to execute an SQL query to insert a pet that is already in your table, then the query will fail to execute. In PHP term, you have the following:

// your SQL query:

$sql = "INSERT into Blah (\'bob\',\'brown\',\'some other params\')";

// execution of the query

$result = mysql_query($sql);

// if an error occured

if (!$result) {

 // we display the error returned by the MySQL server

 echo "An error occured: ".mysql_error()."<br />n";

}

Hope this will help.

 

JP.

[xr6vn9z]

Thank you for replying. What I wanted to know is how do I make the fields so that they are unique? What is the MySQL code to make the fields so they abide by the rule you mentioned. For instance how do I make the following table follow the rule that 2 fields can\'t have the same pair of values?

 

CREATE TABLE Color (

petName CHAR(25) NOT NULL,

petColor CHAR(15) NOT NUL,

pix CHAR(15) NOT NULL DEFAULT "na.gif",

PRIMARY KEY(petName,petColor) );

 

When I try to run it I get an error about not being able to have more than one primary key - as expected. What I want is the actual code that does what the author is trying to accomplish in theory.

[xr6vn9z]

It worked this time. THanks for your excellent help.