[SOLVED] auto increment based on unique row

[intodesi]

Is it possible to have a unique row that does not auto increment, that has a relationship to another table

 

say for example

 

i have a clients table and a projects table

 

uid in the clients table auto increments and is unique

 

and then uid in projects table is unqiue while also having a project_id row that auto increments based on uid

 

so

 

table projects

row's uid = unique project id auto increment (starting from 1 for each uid)

uid = 1

project_id = 1

project_id = 2

project_id = 3

project_id = 4 and so on

 

then

uid = 2

project_id = 1

project_id = 2

project_id = 3

project_id = 4 and so on

 

 

[intodesi]

Assuming what I am asking is not possible then?

[Barand]

I can't work out what you are asking.

 

"project_id row" ???

[intodesi]

yeah project_id is a row as well as uid, the table is projects, i want project_id to auto increment based on each uid, which doesnt auto increment in the project table, uid is part of my clients table, which auto increments there.

 

so each client gets a uid that auto increments in the client table, then in the projects table uid links each project to a customer. but i want each customers project to auto increment starting from 1, its not a nescessary application in my database, most likley its useless, but i would still ike to know if its possible

[Barand]

So given this 1-many relationship

 

[pre]

client                project

---------              -----------

uid          -------<  uid

client_name            project_number

                      project_name

[/pre]

 

then this client project table definition will auto_increment the project number from 1 for each uid as the auto inc column is the second in the compound primary key

                      
                       CREATE table project (
                       uid              int not null,
                       project_number   int not null auto_increment,
                       project_name     varchar(60),
                       PRIMARY KEY (uid, project_number) );

[intodesi]

well in the client table uid needs to auto increment, I will show my client table

 

CREATE TABLE `clients` (
  `uid` mediumint( unsigned NOT NULL auto_increment,
  `f_name` varchar(60) NOT NULL,
  `l_name` varchar(60) NOT NULL,
  `c_name` varchar(60) NOT NULL,
  `email` varchar(60) NOT NULL,
  `phone` varchar(60) NOT NULL,
  `u_name` varchar(60) NOT NULL,
  `u_pass` varchar(60) NOT NULL,
  `client_url` varchar(60) NOT NULL,
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `activated` enum('1','0') NOT NULL default '0',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `u_name` (`u_name`),
  UNIQUE KEY `client_url` (`client_url`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

 

and this is my projects table

 

CREATE TABLE `projects` (
  `project_id` mediumint( unsigned NOT NULL auto_increment,
  `uid` mediumint(9) NOT NULL,
  `project` varchar(60) NOT NULL,
  `description` text NOT NULL,
  `status` varchar(60) NOT NULL,
  `file_loc` varchar(60) NOT NULL,
  `invoice` varchar(120) NOT NULL,
  `proof` varchar(120) NOT NULL,
  `l_update` datetime NOT NULL default '0000-00-00 00:00:00',
  UNIQUE KEY `project_id` (`project_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

[Barand]

Sorry, the create code should've been "CREATE TABLE project ( ... "

[intodesi]

kk and Barand, I dont think i can make two primary keys in the same table? I get a sql error? and how do i move a row up in a table thats already been completed? from what you showed me on how my rows should be, project_id being second, how can i move that down without recreating the table?

[Barand]

TWO ???

 

I only see one:

 

PRIMARY KEY (uid, project_id)

 

where the project_id is the auto_incrementing bit

[intodesi]

("PRIMARY" must be the name of and only of a primary key!)

 

so my primary key looks like this

 

 PRIMARY KEY  (`project_id`)

 

not

 

PRIMARY KEY (uid, project_number)

[intodesi]

So this is the table data I get

 

INSERT INTO `projects` VALUES(0, 1, 'Test', 'whatever', 'Finished', 'none', 'ferret.jpg', 'cat.jpg', '0000-00-00 00:00:00');
INSERT INTO `projects` VALUES(3, 2, 'Test', 'teast', 'test', 'test', 'test', 'test', '2008-05-25 00:00:00');

 

so "0" and "3" are uid's

 

and the second numbers are the project_id's which are auto incrementing, but not starting over for each uid

[Barand]

("PRIMARY" must be the name of and only of a primary key!)

 

so my primary key looks like this

 

 PRIMARY KEY  (`project_id`)

 

not

 

PRIMARY KEY (uid, project_number)

 

If you want to to start at 1 for each UID then you need the PK I defined (uid, project_id). The project_id cannot be primary key as its values will not be unique.

 

auto_increment values are created on insert so any in there when you alter the table will not magically change. 

[Barand]

sample

[pre]

CREATE TABLE `projects` (

  `project_id` mediumint( unsigned NOT NULL auto_increment,

  `uid` mediumint(9) NOT NULL,

  `project` varchar(60) NOT NULL,

  `description` text NOT NULL,

  `status` varchar(60) NOT NULL,

  `file_loc` varchar(60) NOT NULL,

  `invoice` varchar(120) NOT NULL,

  `proof` varchar(120) NOT NULL,

  `l_update` datetime NOT NULL default '0000-00-00 00:00:00',

  PRIMARY KEY  (`uid`,`project_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

INSERT INTO `projects` VALUES(NULL, 0, 'Test', 'whatever', 'Finished', 'none', 'ferret.jpg', 'cat.jpg', '0000-00-00 00:00:00');

INSERT INTO `projects` VALUES(NULL, 3, 'Test', 'teast', 'test', 'test', 'test', 'test', '2008-05-25 00:00:00');

 

mysql> select * from projects;

+------------+-----+---------+-------------+----------+----------+------------+---------+--------------------+

| project_id | uid | project | description | status  | file_loc | invoice    |proof  | l_update            |

+------------+-----+---------+-------------+----------+----------+------------+---------+--------------------+

|          1 |  0 | Test    | whatever    | Finished | none    | ferret.jpg |cat.jpg | 0000-00-00 00:00:00 |

|          1 |  3 | Test    | teast      | test    | test    | test      |test    | 2008-05-25 00:00:00 |

+------------+-----+---------+-------------+----------+----------+------------+---------+--------------------+

2 rows in set (0.00 sec)

[intodesi]

Wow, I really must thank you Barand.. I dropped that table, and used the sql you last posted, and all is well.. but i dont understand why i could make

PRIMARY KEY  (`uid`,`project_id`)

 

from the table i already had... but anyways, thank you

 

nm I see how now its what i get for using phpmyadmin i need to look for the right stuff to do