SQL Server: Create an ID field (PK) based on two f

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Server: Create an ID field (PK) based on two f

Author

Topic

NewDevMan
Starting Member

2 Posts

Posted - 2011-12-07 : 08:53:02

Ok, So I'm a bit new at SQL Server, and I've researched this everywhere but cannot find a clear answer and/or sample code for a solution:

-I have a table [Indicators] that has a bunch of fields including [MOE] field and a [Indicator_Selector] field, and the [INDid] field, which is the PK.

-My goal is to auto-generate the [INDid] field (PK) from concatenating the [MOE] and [Indicator_Selector] fields. so... [INDid]=[MOE]+[Indicator_Selector].

---

My first option was to make the [INDid] field a calculated field, which worked, and it even let me set it as a PK... BUT, SQL server does NOT allow this field to be a FK on a different table, which is crucial for my database. SO... I am told that a TRIGGER can do the trick, and I have found some samples on the internet, but none of them work correctly and are poorly explained.

Can anyone help me please? Thanks in advanced!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 09:03:17

[code]
create trigger checkfk
on relatedtable
instead of insert
as
begin
if exists(select 1
from inserted i
left join Indicators id
on id.INDid = i.FKcol
where id.INDid is null)
raiserror ('value inserted is not a valid value present in parent table',16,1)
end
[/code]

you need to create similar one for update also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NewDevMan
Starting Member

2 Posts

Posted - 2011-12-07 : 09:17:10

Visakh,

I don't see where this trigger gets the values from [MOE] and [Indicator_Selector] to form the [INDid] value??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 09:18:32

quote:
Originally posted by NewDevMan

Visakh,

I don't see where this trigger gets the values from [MOE] and [Indicator_Selector] to form the [INDid] value??

the trigger i gave is to enforce the foreign key constraint. you dont need to change current logic to get value for INDid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources