Check Constraint - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Check Constraint

Author

Topic

Killians
Starting Member

2 Posts

Posted - 2012-05-15 : 09:06:20

I am working on an existing db and have an issue that I don't know if I can resolve. I have a table say cars with fields model and brand where model is the PK. I have a second table say supplier with fields (Factory PK, Brand....). In supplier brand could be entered for multiple factories. I wonder if its possible to define a check constraint on cars that insures brand exists (at least once) in supplier? If yes, how would the alter statement look to create that check?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 22:34:57

you can use UDF for that
see below


CREATE FUNCTION dbo.CheckBrand
(
@Brand varchar(30)
)
RETURNS bit 
AS
BEGIN
DECLARE @Ret bit

SET @Ret =1

IF NOT EXISTS(SELECT 1 FROM supplier WHERE Brand = @Brand)
 SET @Ret = 0

RETURN (@Ret)
END

then use it like

ALTER TABLE cars ADD CONSTRAINT chk_brand CHECK dbo.CheckBrand(Brand) = 1

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

Killians
Starting Member

2 Posts

Posted - 2012-05-16 : 08:48:21

Visakh16, thanks for the response. I am going to implement as you describe.

quote:
Originally posted by visakh16

you can use UDF for that
see below

CREATE FUNCTION dbo.CheckBrand
(
@Brand varchar(30)
)
RETURNS bit
AS
BEGIN
DECLARE @Ret bit

SET @Ret =1

IF NOT EXISTS(SELECT 1 FROM supplier WHERE Brand = @Brand)
SET @Ret = 0

RETURN (@Ret)
END

then use it like

ALTER TABLE cars ADD CONSTRAINT chk_brand CHECK dbo.CheckBrand(Brand) = 1

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 20:22:05

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources