2

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.

Lain
  • 2,166
  • 4
  • 23
  • 47
  • 1
    That'll **strongly depend** on which **concrete** database system you're using. **SQL** as the query language doesn't have any such feature - but maybe some vendor-specific extensions would offer something like this. Please add a relevant tag like `oracle`, `postgresql`, `mysql`, `sql-server`, `db2` or whatever else you might be using to your post! – marc_s May 15 '17 at 21:12
  • Standard SQL has IDENTITY and SEQUENCE both of which are auto sequence generator features. The standard IDENTITY and SEQUENCE keywords are supported by most DBMSs but MySQL uses the AUTO_INCREMENT instead of IDENTITY and has no equivalent of SEQUENCE. – nvogel May 16 '17 at 18:16

1 Answers1

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 );
M T Head
  • 1,085
  • 9
  • 13
  • CompanyID is a way of referencing the record. A combination of CompanyName and SomeOtherInfo must be unique to prevent creating duplicate records. This assumes I understand what the OP is asking..... – M T Head May 17 '17 at 20:49
  • I was looking for an example to see if it was possible to do this sort of thing in mysql: http://stackoverflow.com/a/6383117/3339122 where I just insets the company name, and otherinfo and the ID increments up by one, for each company (as seen in the example he gave) – Lain May 18 '17 at 13:08