Jump to content

Primary Key Conundrum


xr6vn9z

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.