Mix complex Primary Key in table

Question

I got a database project as full semester homework from my university, in one of my tables is it possible to have a primary key in following format 001SALS201812101 auto increment Actually this primary key defines like following

001 - Branch Code
SALS - TRN Type
2018 - Current Year
122 - days of the year
101 - the auto increment number

can anybody tell me how to make such a primary key

MySql is not SQL Server. Those are two different products. In any case, keeping multiple values in a single database column is never a good idea. A primary key does not have to be a single column, it can be a composite key spanning over multiple columns. — Zohar Peled, May 26 '18 at 04:38
Thank you so much for response, If I make composite primary key in a table then can I combine them and use all of them as a foreign key in another table single column, I will be glad if you give me an example — Ghufran Ataie, May 26 '18 at 04:46
There are examples in the manual https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html — P.Salmon, May 26 '18 at 05:53
Personally, I prefer not to construct keys that have any meaning beyond the internal scope of the database. — Strawberry, May 26 '18 at 06:39
Is the auto-increment portion of the key a serial number in the range 000-999, with each combination key components having an independent sequence? — Dan Guzman, May 26 '18 at 11:23
Please tell exactly what you were told to do. You seem to be saying that you want a string-valued column that concatenates strings per other columns. [That is a design anti-pattern.](https://stackoverflow.com/a/28454136/3404097) Or could you have been asked for a *composite* key & no added columns. PS Please clarify by editing your post, not via comments. And which is it--MySQL/MySQLi or SQL Server? Please edit your tags. — philipxy, May 28 '18 at 00:17
@GhufranAtaie Why would you make your life difficult, and create a complicated primary key??? Stick with simple integer primary key. — Eric, May 28 '18 at 23:44
@philipxy I have edited my post tags, and what I want exactly it written in my post also I don't know what to call this procedure composite key or concatenating the columns I just want to get it done, either using all columns or making one column as primary key including all parts such as branchcode-transactionType-CurrentYear-CurrentDay-ReferenceNo like 001-PUR-18-122-001 — Ghufran Ataie, May 28 '18 at 23:47

pim · Accepted Answer · 2018-05-26T11:18:23.520

This isn't something I would recommend doing in the table. But rather in a view dependent on the table.

Let the db do what it does well, that being, a standard auto-incrementing integer as the primary key.

Should you need to ensure uniqueness of the combination, simply add a unique index/key (SQL Server / MySQL).

Below is an example, using SQL Server:

create table Sales (
    Id int identity primary key
    ,BranchCode int
    ,TRNType varchar(25)
    ,Year int
    ,DaysOfYear int
);

create unique index ux_sales_key on Sales (BranchCode, TRNType, Year, DaysOfYear);
go

Note that SQL Server supports computed columns as of 2016. This would also solve your problem, and in the table itself.

And now the same table structure in MySQL

create table Sales (
    Id int auto_increment primary key
    ,BranchCode int
    ,TRNType varchar(25)
    ,Year int
    ,DaysOfYear int
    ,unique key (BranchCode, TRNType, Year, DaysOfYear)
);

To formulate your "composite key" use a view similar to the following:

create view vSales
as
    select
        concat(BranchCode, TRNType, Year, DaysOfYear, Id) as SomeCompositeKey
        -- the rest of your cols go here
    from
        Sales;
go

Mix complex Primary Key in table

1 Answers1