Trying to add primary key to existing database.

Hi,

I have a database, which was till now, not having any primary key defined. I
thought I would not need it but now I think I do (based on the suggestions
from my prior postings). I am now trying to insert the primary key in an
already existing database. There was not even an index defined earlier
(sorry, if it looks strange). There are many columns that have same names
this means that I cannot make that column the primary key.

I have now got an idea to first define index on the table and then make the
index thing the primary key. I have mySql on cPanel. please advice how to
achieve this on an existing database in cPanel php admin.

Any link or steps would help. I tried it myself but there is something it
wants to tell me, that I am not sure about.

Thanks,

Chris
--
View this message in context: http://www.nabble.com/Trying-to-add-primary-key-to-existing- database.-tf3168750.html#a8790147
Sent from the Php - Database mailing list archive at Nabble.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to add primary key to existing database.

hehe, ok
i would write a script that reads the existing data from the table and
inserts it into a new one that has the same structure PLUS a primary key

something like:
original_table: name, description, whatever
new_table: id, name, description, whatever

where id is primary key and auto_increment

then

if ($link=mysql_connect("host","user","pw")
{
mysql_select_db("the_correct_database);
$result=mysql_query("SELECT * FROM original_table");
while (list($name,$description,$whatever) = mysql_fetch_row($result))
{
mysql_query("INSERT INTO new_table (name,description,whatever) VALUES
($name,$description,$whatever)"); //that should add the id automatically
}
mysql_close($link)
?>

then you can drop the original_table and rename the new_table to
original_table

i don't know if there's a better, more simple solution and i hope i did
not forget anything important and it works this way

Chris

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to add primary key to existing database.

Hi.

I don't know cPanel, but I expect it can run SQL directly, most panels
can. Use the ALTER TABLE ADD {INDEX|KEY} syntax to add indexes/primary
keys. You can use multiple columns, which together form a unique value
to create your primary key.

Niel

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to add primary key to existing database.

Chris Carter wrote:
> Hi,
>
> I have a database, which was till now, not having any primary key defined. I
> thought I would not need it but now I think I do (based on the suggestions
> from my prior postings). I am now trying to insert the primary key in an
> already existing database. There was not even an index defined earlier
> (sorry, if it looks strange). There are many columns that have same names
> this means that I cannot make that column the primary key.
>
> I have now got an idea to first define index on the table and then make the
> index thing the primary key. I have mySql on cPanel. please advice how to
> achieve this on an existing database in cPanel php admin.
>
> Any link or steps would help. I tried it myself but there is something it
> wants to tell me, that I am not sure about.

Most likely not everything can be done through phpmyadmin, however you
can add your own primary key easily:

alter table tablename add id int not null auto_increment primary key;

See http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to add primary key to existing database.

Christopher Blöcker wrote:
> hehe, ok
> i would write a script that reads the existing data from the table and
> inserts it into a new one that has the same structure PLUS a primary key
>
> something like:
> original_table: name, description, whatever
> new_table: id, name, description, whatever
>
> where id is primary key and auto_increment
>
> then
>
> > if ($link=mysql_connect("host","user","pw")
> {
> mysql_select_db("the_correct_database);
> $result=mysql_query("SELECT * FROM original_table");
> while (list($name,$description,$whatever) = mysql_fetch_row($result))
> {
> mysql_query("INSERT INTO new_table (name,description,whatever) VALUES
> ($name,$description,$whatever)"); //that should add the id automatically
> }
> mysql_close($link)
> ?>
>
> then you can drop the original_table and rename the new_table to
> original_table
>
> i don't know if there's a better, more simple solution and i hope i did
> not forget anything important and it works this way

Good idea but you can put that all into one step and leave php out
altogether:

insert into table (f1, f2, f3) select f1,f2,f3 from other_table;

See http://dev.mysql.com/doc/refman/4.1/en/insert-select.html

(Nice bonus - this is sql standard so it works across a lot of different
db's).

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Trying to add primary key to existing database.

I think you also should read this
http://en.wikipedia.org/wiki/Database_normalization

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php