Mysql assigning default causes error

Question

Hi,

I am trying to run the below query to create a table in mysql and I am getting an error.

create table newtable ( 
       version_id int(11) auto_increment not null, 
       test_id int(11)   default version_id, 
       primary key(version_id) 
 );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'version_id not null, primary key(version_id), unique key(test_id) )' at line 1

I am getting above error.

I think the problem is setting test_id default as version_id, because it works otherwise.

Thank you
Bala

-- Update

Here is what I wanted to do,

When i create a new row, I want to use the version_id as the key. When I update, I want to use the value of existing record as the key. Note that test_id is not the primary key.

Answer 1

I don't think you can use "variables" as defaults. They probably have to be constants. If you want to get this effect, you could probably do it in a stored procedure / trigger "before Insert" such that if the trial_id is not supplied it gets assigned the same value as the version_id...

Here's a tutorial that might help you toward that end.

I think your trigger would look something like:

mysql> CREATE TRIGGER myTrigger
    -> BEFORE INSERT ON newtable
    -> FOR EACH ROW
    -> BEGIN
    ->      IF NEW.trial_id IS NULL THEN
    ->         SET NEW.trial_id = NEW.version_id;
    ->      END IF;
    -> END$$