Auto increment with composite primary key

Question

I am not entirely sure what to search so I apologize if this is in fact a duplicate.

I have a table (or at least would like to) as follows:

• ID
• Company Name
• SomeOtherInfo

The primary key would be ID and Company name (composite primary key). What I would like is so that the ID auto increments on each company.

Ex:

1-google
2-google
3-google
1-yahoo
4-google
2-yahoo

This way they are always unique, but each one increments for each company individually.

Is this possible from simple SQL create commands, would rather not have 2 tables and join them using a secondary ID.

Let me know, thanks.

Answer 1

If I follow the question. Create a single table with an identity on the ID column. Then create a unique index on the Company Name.

MySql Version

CREATE TABLE Company (
    CompanyID int NOT NULL AUTO_INCREMENT,
    CompanyName varchar(255) NOT NULL,
    OtherData  varchar(255),
    PRIMARY KEY (CompanyID)
); 

CREATE UNIQUE INDEX CompanyUniqueComposite
ON Company (CompanyID , CompanyName );