What is the best way to solve this issue

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)

What is the best way to solve this issue

Author

Topic

tleal
Starting Member

4 Posts

Posted - 2009-09-12 : 09:43:41

I have a large table - example
(PrimaryKey - ClientId), (Primary - AccountId), AcctName, VendorId
example data
1, 1, Tony Leal, ''
1, 2, Sue Jones, ''
2, 1, Sam Smith, 'X123'

I need to stop the following insert
2, 2, Ted, Jones, 'X123'
**The vendor id if not blank, cannot duplicate within the ClientId
This insert is valid
1, 3, Ted, Jones, 'X123'

I'm not sure if I should add a constraint?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-12 : 10:43:44

Should be able to modify this to work for you:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130718

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 13:42:24

just make a udf and use it in check constraint


CREATE FUNCTION GetCount
(
@ClientID int,
@VendorId varchar(10)
)
RETURNS int
AS
BEGIN
DECLARE @Cnt int
SELECT @Cnt=CASE WHEN @VendorId='' THEN 1 ELSE COUNT(1) END
FROM YourTable
WHERE ClientID=@ClientID
AND VendorId=@VendorId

RETURN @Cnt
END

ALTER TABLE YourTable ADD CONSTRAINT CHK_Cnt CHECK (dbo.GetCount(ClientId,VendorId)=1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources