Using unique, and using auto_increment

[John Taylor]

I have 2 questions, that did not seem to be answered in the mysql manual.

I have a table that has 3 entries like this:

Year                                varchar(255) not null,
Number                              int unsigned not null,
Minor                               int unsigned,
unique                              (Number)


An example data set would be:
Year = H99
Number = (any # between 0000 and 9999)
         (2000 will probably be the realistic max)
Minor = any # between 1 and 20

The problem is this.  This year a project will have a number
Year=H99
Number=1234

and next year a project will have a number of
Year=H00
Number=1234

This causes a problem, because Number is unique.  Is there a way to group
Year and Number to make one unique entity? So that it checks H99-1234 and
H00-1234?

Also, it would be nice to use the AUTO_INCREMENT feature for Number.  How is
this used?  What is the syntax, for an INSERT and for an UPDATE statement?

Thanks,
John

-- 

John Taylor                 Large Scale Distributed Information Systems
System Administrator        Computer Science Dept, University of Georgia
john@stripped             http://lsdis.cs.uga.edu/
                            (Where our programs are Y1.999K compliant :-)