Primary Key...

Jezz

I am trying to create a script that will allow me to create a table, using an HTML/PHP front end. The first of the two scripts simply allows me to give the table a name, and say how many fields will be present. The second script allows me to enter names for the fields, as well as set one field to be the Primary Key, and Auto Increment. When I submit this, I should get a new page saying the table has been created, instead I get the following message:

All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead

As I think the first script is fine, here is the code for the second:

<?
$db_name = "******";

$connection = @mysql_connect("***", "", "***")
or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

$sql = "CREATE TABLE $_POST[table_name] (";

for ($i = 0; $i < count($POST[field_name]); $i++) {
$sql .= $POST[field_name][$i]." ".$_POST[field_type][$i];

if ($_POST[auto_increment][$i] == "Y") {
$additional = "NOT NULL auto_increment";
} else {
$additional = "";
}

if ($POST[primary][$i] == "Y") {
$additional .= ", primary key (".$POST[field_name][$i].")";
} else {
$additional = "";
}

if ($POST[field_length][$i] != "") {
$sql .= " (".$POST[field_length][$i].") $additional ,";
} else {
$sql .= " $additional ,";

}

$sql = substr($sql, 0, -1);
$sql .= ")";

$result = mysql_query($sql,$connection) or die(mysql_error());
if ($result) {
$msg = "<P>".$_POST[table_name]." has been created!</P>";
}
?>
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 3</TITLE>
</HEAD>
<BODY>
<h1>Adding table to <? echo "$db_name"; ?>...</h1>
<? echo "$msg"; ?>
</BODY>
</HTML>

Can anyone tell me why this message appears? I have basically taken this code straight out of a book, so I'm puzzled as to why it isn't working, it is exactly as it says in the book!

eoghain

The error you are getting is from your database telling you that your primary key has to be not null. Not null is a field modifier that you can add, just like primary key, and when using primary key you need to add it.

Basically in your code if you have a field that is a primary key but isn't autoincrement then your code won't make that field not null. So that is something that you are going to have to check for and handle.

Sxooter

Why does MySQL require all fields in a PK to be NOT NULL? Since NULLS aren't equal to each other, a null in a PK field is quite legal by SQL spec.

Was this one of those "easier to make a rule against it than to implement it right" kind of decisions?

eoghain

I don't know. That'd be a good question to ask the MySQL guys.

Jezz

I added a line into the primary key bit of the code, to make it not null, and it allowed me to make the table. However, when I add the first record it gives it an id of 0, when I try to add a second record, I get the following error message:

Duplicate entry '0' for key 1

In the add record scripts, I give no mention to the id field, as this is automated, I only allow data to be enetered for the other fields, should there be some code for the auto incrementing primary key field, in the add record scripts?

eoghain

If your field is both auto incrementing and a primary key then it would have to have both in it's declaration:

id_field int not null auto_increment primary key