SQLTeam.com | Weblogs | Forums

Customizing Primary Key


#1

Hi, am a learner and have no experience in programming nor sql. I have the following issue that i have failed to figure out how to implement.
I have the following columns (extract) in my admission table (Using entity framework core)

  1. StudentNo(nvarchar(30))
  2. ProgrammID(FK, nvarchar(10))
  3. IntakeID(Fk, nvarchar(10))

StudentNo Column should be generated from the programmID and IntakeID column in the following formatt
"ProgrammID/IntakeId/Index/current year" for instance
studentNo : ............. ProgrammID: .. IntakeID
B01/M/001/2019:...... B01 :................ M
B01/M/002/2019:...... B01 :................ M
B01/J/001/2019 :...... B01 :................ J
B01/J/002/2019 :...... B01 :................ J
C01/M/001/2019 :..... C01 :.............. M
C01/M/002/2019 :..... C01:............... M
C01/J/001/2019 :...... C01 :............... J
C01/J/002/2019 :...... C01 :............... J

How can i write a trigger to achieve the above, the studentNo column changes when the ProgrammID and IntakeID changes.

Please help, Thanks in advance.


#2

I'm not going to write your homework for you, but I will give you a hint. Looking into calculated columns instead of a trigger


#3

NO! You're violating proper design here (specifically first normal form (1NF)), the most basic rule of column design.

That is, a column contains a single, atomic (inseparable) piece of data.

Instead, have a column for each piece of data, then you can compute the convoluted string on the fly for display purposes, but you do NOT store it in that format.

So:
ProgrammID varchar(10)
IntakeID varchar(10)
Year char(4)
StudentCount smallint

Then, create a view that generates the StudentNo, something like this:

SELECT ProgrammID + '/' + IntakeID + '/' + RIGHT('000' + CAST(StudentCount AS varchar(5)), 3) + '/' + Year AS StudentNo, *
FROM table_name