Jump to content


Photo

Primary Key Conundrum


  • Please log in to reply
3 replies to this topic

#1 xr6vn9z

xr6vn9z
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 14 July 2003 - 04:26 PM

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?

#2 barbatruc

barbatruc
  • Members
  • PipPip
  • Member
  • 28 posts
  • LocationMontreal, Quebec, Canada

Posted 14 July 2003 - 05:01 PM

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 occuredif (!$result) {  // we display the error returned by the MySQL server  echo "An error occured: ".mysql_error()."<br />n";}
Hope this will help.

JP.
Unfortunately, PHP \'empowered every moron with a copy of Windows notepad to be \"web programmers\". (...) Give PHP a real INFRASTRUCTURE. Use PEAR!!!\'

#3 xr6vn9z

xr6vn9z
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 14 July 2003 - 05:42 PM

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.

#4 xr6vn9z

xr6vn9z
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 14 July 2003 - 05:58 PM

It worked this time. THanks for your excellent help.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users