Om jag gjort en tabell i mysql som här:
mysql> create database accounts;
mysql> use accounts;
mysql> create table acc (acc_id int primary key, username varchar(64), password varchar(64), ts timestamp);
mysql> describe acc;
+----------+---------------+------+-----+---------+-------+
| Field | Type | NULL | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| acc_id | int(11) | | PRI | 0 | |
| username | varchar(64) | YES | | NULL | |
| password | varchar(64) | YES | | NULL | |
| ts | timestamp(14) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
Sen fyllt den med data....
Och i efterhand kommer på att jag vill ändra uppbyggnaden till
mysql> describe acc;
+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+-------+
| acc_id | int(11) | | PRI | 0 | |
| username | varchar(64) | | PRI | | |
| password | varchar(64) | YES | | NULL | |
| ts | timestamp | YES | PRI | CURRENT_TIMESTAMP | |
| act_ts | timestamp | YES | | 0000-00-00 00:00:00 | |
+----------+-------------+------+-----+---------------------+-------+
Går det? Eller måste jag börja från början med en helt ny tabell?
.
edit.. fixade till det..
use accounts;
describe acc;
+----------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+-------------------+-------+
| acc_id | int(11) | | PRI | 0 | |
| username | varchar(64) | YES | | NULL | |
| password | varchar(64) | YES | | NULL | |
| ts | timestamp | YES | | CURRENT_TIMESTAMP | |
+----------+-------------+------+-----+-------------------+-------+
ALTER TABLE acc RENAME account;
ALTER TABLE account ADD act_ts timestamp;
ALTER TABLE account DROP PRIMARY KEY;
ALTER TABLE account MODIFY acc_id int NOT NULL;
ALTER TABLE account MODIFY username varchar(64) NOT NULL;
ALTER TABLE account MODIFY ts timestamp NOT NULL;
ALTER TABLE account ADD PRIMARY KEY(acc_id,username,ts);
describe account;
+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+-------+
| acc_id | int(11) | | PRI | 0 | |
| username | varchar(64) | | PRI | | |
| password | varchar(64) | YES | | NULL | |
| ts | timestamp | YES | PRI | CURRENT_TIMESTAMP | |
| act_ts | timestamp | YES | | 0000-00-00 00:00:00 | |
+----------+-------------+------+-----+---------------------+-------+