February 3, 2016 at 12:34 am
Hi,
I have a table :
ID | Project Name | Year
===================
1 | Sample Project1 | 2015
2 | Sample Project2 | 2015
3 | Sample Project3| 2016
4 | Sample Project4 | 2016
I would like to have a second column that acts as a second primary key depending on each year the record is saved, like so:
ID | Project Name | Year | YearID
=======================
1 | Sample Project1 | 2015 | 1
2 | Sample Project2 | 2015 | 2
3 | Sample Project3 | 2016 | 1
4 | Sample Project4 | 2016 | 2
5 | Sample Project5 | 2016 | 3
How do I do this in SQL Server? Also, what would you call YearID column in this case? Is it also a primary key?
February 3, 2016 at 1:13 am
No, it fails two tests of being a primary key, namely that there can only be one in a table, and that each of its values must be unique. Now, what precisely are you trying to do, and why?
John
February 3, 2016 at 1:20 am
Hi, Thank you for your reply.
I want the YearID second key to be auto-increment depending on the year of the project, if the project is for 2015, the YearID will start from 1 to n, if the year is 2016, the YearID will again start from 1 to n, and again if the year 2017 comes, the YearID will again start from 1 to n
February 3, 2016 at 1:41 am
So you want to add a generated column that can be combined with the Year column to become a composite candidate key? Okay.
"How do I do this in SQL Server?"
Run a query to find the highest value for the current year, add one, don't forget to use COALESCE to handle the case when no rows exist yet for the current year.
Also, talk with the business about the handling of gaps (if data is deleted). And be aware that this smght cause blocking if you have a high frequency of inserts,
"Also, what would you call YearID column in this case?"
I would call it whatever the business calls it.
For instance, the bookkeeping regualtions of my country give companies two choices for invoice numbers: either use an invoice number that is unique over all invoices ever issued by the company, or use an invoice number that is unique in the current year. In the latter case, invoice numbers can restart when the year rolls over. So in this case, the number that restarts at 1 in every new year would be called InvoiceNumber.
What do the business users of your database call the number you are adding? That's your column name.
"Is it also a primary key?"
In a relational database, every column or combination of columns that uniquely identifies a single row in a table is called a "candidate key". During implementation, one of the candidate keys is chosen to be the primary key, all others are alternate keys (enforced by a UNIQUE constraint in SQL Server and in ANSI SQL).
In your case, the column combination (Year, *WhateverNameYouChoose*) is a candidate key. It might also be chosen as the primary key,
The ID column by itself is also a candidate key. However, I am not sure if you actually need an artificial key for this table.
February 3, 2016 at 9:07 pm
Hugo Kornelis (2/3/2016)
So you want to add a generated column that can be combined with the Year column to become a composite candidate key? Okay."How do I do this in SQL Server?"
Run a query to find the highest value for the current year, add one, don't forget to use COALESCE to handle the case when no rows exist yet for the current year.
Also, talk with the business about the handling of gaps (if data is deleted). And be aware that this smght cause blocking if you have a high frequency of inserts,
I would rather not do this by query but I want a column to have such data
"Also, what would you call YearID column in this case?"
I would call it whatever the business calls it.
For instance, the bookkeeping regualtions of my country give companies two choices for invoice numbers: either use an invoice number that is unique over all invoices ever issued by the company, or use an invoice number that is unique in the current year. In the latter case, invoice numbers can restart when the year rolls over. So in this case, the number that restarts at 1 in every new year would be called InvoiceNumber.
What do the business users of your database call the number you are adding? That's your column name.
Sorry, Wrong question, I would like to ask what is the sql, it term for such a column,
"Is it also a primary key?"
In a relational database, every column or combination of columns that uniquely identifies a single row in a table is called a "candidate key". During implementation, one of the candidate keys is chosen to be the primary key, all others are alternate keys (enforced by a UNIQUE constraint in SQL Server and in ANSI SQL).
In your case, the column combination (Year, *WhateverNameYouChoose*) is a candidate key. It might also be chosen as the primary key,
The ID column by itself is also a candidate key. However, I am not sure if you actually need an artificial key for this table.
Thank you for straightening me out. I appreciate the detailed response.
February 4, 2016 at 12:37 pm
alex_fajardo2002 (2/3/2016)
Hugo Kornelis (2/3/2016)
So you want to add a generated column that can be combined with the Year column to become a composite candidate key? Okay."How do I do this in SQL Server?"
Run a query to find the highest value for the current year, add one, don't forget to use COALESCE to handle the case when no rows exist yet for the current year.
Also, talk with the business about the handling of gaps (if data is deleted). And be aware that this smght cause blocking if you have a high frequency of inserts,
I would rather not do this by query but I want a column to have such data
Yes, I know. Add the column to your table. Then use the code I posted is what you add to your INSERT statements to generate the data for this column.
If you have multi-row inserts, then you may may have to use ROW_NUMBER and add that to the result of the subquery to ensure that each new row gets a unique value.
"Also, what would you call YearID column in this case?"
I would call it whatever the business calls it.
For instance, the bookkeeping regualtions of my country give companies two choices for invoice numbers: either use an invoice number that is unique over all invoices ever issued by the company, or use an invoice number that is unique in the current year. In the latter case, invoice numbers can restart when the year rolls over. So in this case, the number that restarts at 1 in every new year would be called InvoiceNumber.
What do the business users of your database call the number you are adding? That's your column name.
Sorry, Wrong question, I would like to ask what is the sql, it term for such a column,
Eehhrrmm, ... a column?
Maybe I am misunderstanding you. There are no column names in SQL Server that have any special meaning. (Except "timestamp")
If you are looking for the name of the feature that automatically generates the numbers in this fashion for you, then there is no name for it since there is no such feature. But as I already showed, it's easy to roll your own.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply