A better way to constrain - 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 2000 Forums

Transact-SQL (2000)

A better way to constrain

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-01 : 15:46:51

Guys,
Say I have table Product with columns (productID int, productTypeID int, isForRent bit)

I want to constrain isForRent = 1 for a select set of productTypeIDs. Can this be done at the table level via a constraint? I currently have it enforced via a stored procedure (that does the insert into product) but wanted to know if there is a better way.

Thanks!

Nathan Skerl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-01 : 15:57:43

Is this what you mean:

CREATE TABLE [dbo].[Product] (
[productID] [int] NOT NULL ,
[productTypeID] [int] NOT NULL ,
[isForRent] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [chk_Product_isForRent] CHECK ([isForRent] = CASE WHEN ([productTypeID] = 1) THEN 1 ELSE 0 END)
GO

INSERT INTO Product (productID, productTypeID, isForRent)
VALUES (1, 1, 1)

SELECT *
FROM Product

INSERT INTO Product (productID, productTypeID, isForRent)
VALUES (2, 1, 0)

SELECT *
FROM Product

DROP TABLE Product

Tara Kizer
aka tduggan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-01 : 16:10:22

or perhaps:


alter table product add 
constraint chk_Product_isForRent check (isForRent = case when productTypeID in (1,2,3) then 1 else isForRent end)

Be One with the Optimizer
TG

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-01 : 16:46:07

Yes, exactly guys. I didnt know you could CASE a constraint like that.

Thank you both.

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-02 : 13:12:55

Is there a way to use a subquery to get the set of typeIDs? When I use:

case when productTypeID in (SELECT productTypeID from...)

I receive the Subquery in Constraint... error.

Nathan Skerl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-02 : 13:18:39

I don't think that you can do that. I think you are limited as to what it can constrain. I don't believe you can access a table in it.

Tara Kizer
aka tduggan

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-02 : 13:21:51

Maybe through a function? I havent tried this yet (not in office) but would this work?

CHECK([isForRent] = dbo.isRentableType(productTypeID)...

Nathan Skerl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-02 : 13:31:13

I've never tried it, but my gut says it won't work. I don't think you can get too fancy with the constraints.

Tara Kizer
aka tduggan

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-02 : 13:47:37

Maybe it would be best just to talk about the root problem I have here (brilliant!)

Im basically trying to create a conditional foreign key via the constraint. If a product is rentable then I want to enforce that it is of type (1,2,3). But not all products of type (1,2,3) are rentable.

Btw, thanks again for taking the time to talk this out with me.

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-02 : 13:56:03

And yes, this is all a direct result of a legacy design issue.

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2005-12-02 : 13:56:24

As a last ditch resort you could put a TRIGGER on the table that does a RAISERROR if some specific conditions are not met

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-02 : 13:57:15

Do you even need the isForRent column? It sounds like you'd know based upon the productTypeID.

Tara Kizer
aka tduggan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-02 : 14:02:06

I would create a new table, call it something like "ProductTypesRentable":

ProductType <-- fk to your Product Types table
IsForRent <-- 1 or 0

with a composite PK of both columns. Then fill it up:

ProductType,IsForRent
1,1
1,0
2,1
2,0
3,1
3,0
4,0
5,0

Finally, your main table would have a fk constraint to reference *that* table on a composite FK of (ProductType/IsForRent). This way, only valid combos will be allowed.

Another option is to move the "IsForRent" attribute to your product types table, if possible. This would require you to have 2 different PRoductTypes for "type 1", for example: one for the case where type 1 is for rent, one where it is not.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-02 : 14:09:24

quote:
Do you even need the isForRent column?

well, yes because isForRent cannot be derived from productTypeID otherwise I would have put the bit on the productType table.

If a product is rentable then it must be of productType (1,2,3), but not all productType (1,2,3) are rentable.

This is really business logic I guess and should be enforced via a stored procedure / trigger (as Kristen mentioned).

Nathan Skerl

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-02 : 14:18:01

quote:
Originally posted by nathans

quote:
Do you even need the isForRent column?

well, yes because isForRent cannot be derived from productTypeID otherwise I would have put the bit on the productType table.

If a product is rentable then it must be of productType (1,2,3), but not all productType (1,2,3) are rentable.

This is really business logic I guess and should be enforced via a stored procedure / trigger (as Kristen mentioned).

Nathan Skerl

I disagree. See the two options I gave you, above. Both are easy to implement and fully constrain your data and allow for flexibility.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-02 : 14:24:57

quote:
Originally posted by jsmith8858

I would create a new table...

Hmmm... thats interesting. The first option definitely covers all the bases. Thats a great way to model the relationship (well, given the existing situation).

Nice work Dr.

Thanks again everyone!

Nathan Skerl

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-02 : 14:35:11

What's nice is, now your front-end application can simply reference this table to determine whether or not a Product is considered "rentable" based on its type. And if you add new product Types and/or decide that Product #4 should someday be "rentable", all you do is add an entry to the table and you are done!

cfederl
Starting Member

26 Posts

Posted - 2005-12-02 : 17:33:47

UDF can be referenced in check constraints and work as good alternative to writing complex triggers.
CREATE TABLE dbo.ProductTypes
( productTypeID int NOT NULL
, isRentable bit NOT NULL
, constraint ProductTypes_P primary key (productTypeID)
)
GO
CREATE TABLE dbo.Product
( productID int NOT NULL
, productTypeID int NOT NULL
, isForRent bit NOT NULL
, constraint Product_P primary key (productID)
)
GO
create function ProductType_isRentable( @productTypeID int )
returns bit
as
begin
return (select isRentable from ProductTypes where productTypeID = @productTypeID)
end
go

ALTER TABLE dbo.Product
ADD CONSTRAINT Product_C_isRentable CHECK (isForRent = 0 OR (isForRent = 1 and isForRent = dbo.ProductType_isRentable( productTypeID ) ) )
GO
insert into ProductTypes
( productTypeID , isRentable )
select 1, 1 union all select 2, 1 union all select 3, 0

-- These inserts pass the check constraint UDF
insert into product select 1, 1, 0
insert into product select 2, 1, 1
insert into product select 3, 2, 0
insert into product select 4, 2, 1
insert into product select 5, 3, 0
-- This insert fails the check constraint UDF
insert into product select 6, 3, 1
select * from product
-- This update passes the check constraint UDF
Update Product set isForRent = 1 where productid = 1
-- This update fails the check constraint UDF
Update Product set isForRent = 1 where productid = 5

productid = 5

Carl Federl

cfederl
Starting Member

26 Posts

Posted - 2005-12-03 : 08:38:35

There is even simplier solution that allows three rentable combinations:
'Rentable or Non-Rentable' , 'Rentable Only' , 'Never Rentable'

CREATE TABLE dbo.ProductTypes
( productTypeID int NOT NULL
, RentableDefn varchar(255) not null
, constraint ProductTypes_P primary key (productTypeID)
)
CREATE TABLE dbo.ProductTypeRentAlternatives
( productTypeID int NOT NULL
, isRentable bit NOT NULL
, constraint ProductTypeRentAlternatives_P primary key (productTypeID, isRentable )
)
GO
CREATE TABLE dbo.Product
( productID int NOT NULL
, productTypeID int NOT NULL
, isForRent bit NOT NULL
, constraint Product_P primary key (productID)
, constraint ProductTypeRentAlternatives_F_Product foreign key (productTypeID, isForRent )
references ProductTypeRentAlternatives
)
GO
insert into dbo.ProductTypes
( productTypeID , RentableDefn)
select 1, 'Rentable or Non-Rentable' union all
select 2, 'Rentable Only' union all
select 3, 'Never Rentable'
go
insert into ProductTypeRentAlternatives
(productTypeID, isRentable)
select 1 , 0 union all
select 1 , 1 union all
select 2 , 1 union all
select 3 , 0

insert into product select 1, 1, 0
insert into product select 2, 1, 1
insert into product select 3, 2, 0
insert into product select 4, 2, 1
insert into product select 5, 3, 0
-- This insert fails the foreign key constraint
insert into product select 6, 3, 1
select * from product
-- This update passes the foreign key constraint
Update Product set isForRent = 1 where productid = 1
-- This update fails the foreign key constraint
Update Product set isForRent = 1 where productid = 5

Carl Federl

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-03 : 09:36:36

Carl -- did you see my post?

The code you've posted is almost line-for-line the same as my previous post.

cfederl
Starting Member

26 Posts

Posted - 2005-12-03 : 22:15:47

My apologies for posting your solution.

I am sure that your post is where I got the solution in the first place but overnight I forgot the source of the solution, thought it was an alternative and made the post with your solution.

Carl Federl

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources