Check constraint with a scalar UDF not working

Prev: T-SQL cursor - Variables in an email body
Next: sysobject

From: 20060524 on 14 Apr 2010 13:29

---

I appreciate your responses, sloan and Tibor!

The DDL for the Order_Status scalar UDF is now included; and my apologies
for the square bracket inconvenience with the DDL scripted out by SQL
Management Studio.

For this issue I stumbled on a workaround of passing all the possible
columns, which now causes the constraint to fire.

I was glad to see the technique discussed in the comments on Tibor's blog
posting, however I was not satisfied with the approach of passing all
possible columns since this UDF can potentially be used in other tables
having different columns.

To work around the issue of using this UDF in different tables, I created
table-specific versions of the Order_Status UDF (i.e.
Order_Details_Order_Status) which in turn call the Order_Status UDF.

Not pretty, but it does have the added side-effect of allowing updates to
the Order_Status UDF because it is no longer directly schema-bound to any
tables.

Curiously, (before I went on this tangent of creating table-specific
versions of the Order_Status UDF) when altering the Order_Status UDF to have
no parameters, the constraint does fire regardless of whatever column has
been affected by an insert or update. This is closer to the description in
BOL of what a table constraint should behave like.

With this in mind, I framed my searches differently and found that this
behavior with UDF's not firing when the affected columns are not passed as a
parameter has been classified as a bug:

https://connect.microsoft.com/SQLServer/feedback/details/301828

https://connect.microsoft.com/SQLServer/feedback/details/344003

Again, thanks for perusing this post, and here's the DDL once more including
the UDF and the workaround of passing all possible columns.

create
function [dbo].[Order_Status]
(
@Orders_ID int
)
returns nchar(10)
as
begin
declare @Order_Status nchar(10)

select @Order_Status = Order_Status
from Orders
where ID = @Orders_ID

return @Order_Status
end
GO

CREATE TABLE [dbo].[Orders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nchar](10) NOT NULL,
[Order_Status] [nchar](10) NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT
[CK_Orders_Order_Status] CHECK (([Order_Status]='Shipped' OR
[Order_Status]='InProgress'))
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [CK_Orders_Order_Status]

CREATE TABLE [dbo].[Order_Details](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nchar](10) NOT NULL,
[Orders_ID] [int] NOT NULL,
CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Order_Details] WITH CHECK ADD CONSTRAINT
[FK_Order_Details_Orders] FOREIGN KEY([Orders_ID])
REFERENCES [dbo].[Orders] ([ID])
GO
ALTER TABLE [dbo].[Order_Details] CHECK CONSTRAINT [FK_Order_Details_Orders]
GO
ALTER TABLE [dbo].[Order_Details] WITH CHECK ADD CONSTRAINT
[CK_Order_Details] CHECK (([dbo].[Order_Status]([Orders_ID])='InProgress'))
GO
ALTER TABLE [dbo].[Order_Details] CHECK CONSTRAINT [CK_Order_Details]

declare @identity as int

insert Orders
select 'Title'
, 'InProgress'

set @identity = @@identity

insert Order_Details
select 'Title'
, @identity

update Orders
set Order_Status = 'Shipped'

update Order_Details
set Title = 'Title'

--Why does CK_Order_Details fire in the next update but not with the
previous update?
update Order_Details
set Title = 'Title'
, Orders_ID = @identity

delete from Order_Details
delete from Orders

IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[CK_Order_Details]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Order_Details]'))
ALTER TABLE [dbo].[Order_Details] DROP CONSTRAINT [CK_Order_Details]
GO

alter
function [dbo].[Order_Status]
(
@Title nchar(10)
, @Orders_ID int
)
returns nchar(10)
as
begin
declare @Order_Status nchar(10)

select @Order_Status = Order_Status
from Orders
where ID = @Orders_ID

return @Order_Status
end
GO

ALTER TABLE [dbo].[Order_Details] WITH CHECK ADD CONSTRAINT
[CK_Order_Details] CHECK
(([dbo].[Order_Status]([Title],[Orders_ID])='InProgress'))
GO
ALTER TABLE [dbo].[Order_Details] CHECK CONSTRAINT [CK_Order_Details]

declare @identity as int

insert Orders
select 'Title'
, 'InProgress'

set @identity = @@identity

insert Order_Details
select 'Title'
, @identity

update Orders
set Order_Status = 'Shipped'

update Order_Details
set Title = 'Title'

--CK_Order_Details now fires in the next update and the previous update...
update Order_Details
set Title = 'Title'
, Orders_ID = @identity

drop table Order_Details
drop table Orders
drop function [dbo].[Order_Status]

From: Gert-Jan Strik on 14 Apr 2010 14:40

---

I wouldn't recommend going down this road... You do realize that if you
change the Order_Status in Orders to 'Shipped', then any UPDATE of
Order_Details will (or at least should) fail!?

--
Gert-Jan


20060524(a)newsgroups.nospam wrote:
>
> I appreciate your responses, sloan and Tibor!
>
> The DDL for the Order_Status scalar UDF is now included; and my apologies
> for the square bracket inconvenience with the DDL scripted out by SQL
> Management Studio.
>
> For this issue I stumbled on a workaround of passing all the possible
> columns, which now causes the constraint to fire.
>
> I was glad to see the technique discussed in the comments on Tibor's blog
> posting, however I was not satisfied with the approach of passing all
> possible columns since this UDF can potentially be used in other tables
> having different columns.
>
> To work around the issue of using this UDF in different tables, I created
> table-specific versions of the Order_Status UDF (i.e.
> Order_Details_Order_Status) which in turn call the Order_Status UDF.
>
> Not pretty, but it does have the added side-effect of allowing updates to
> the Order_Status UDF because it is no longer directly schema-bound to any
> tables.
>
> Curiously, (before I went on this tangent of creating table-specific
> versions of the Order_Status UDF) when altering the Order_Status UDF to have
> no parameters, the constraint does fire regardless of whatever column has
> been affected by an insert or update. This is closer to the description in
> BOL of what a table constraint should behave like.
>
> With this in mind, I framed my searches differently and found that this
> behavior with UDF's not firing when the affected columns are not passed as a
> parameter has been classified as a bug:
>
> https://connect.microsoft.com/SQLServer/feedback/details/301828
>
> https://connect.microsoft.com/SQLServer/feedback/details/344003
>
> Again, thanks for perusing this post, and here's the DDL once more including
> the UDF and the workaround of passing all possible columns.
>
> create
> function [dbo].[Order_Status]
> (
> @Orders_ID int
> )
> returns nchar(10)
> as
> begin
> declare @Order_Status nchar(10)
>
> select @Order_Status = Order_Status
> from Orders
> where ID = @Orders_ID
>
> return @Order_Status
> end
> GO
>
> CREATE TABLE [dbo].[Orders](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [Title] [nchar](10) NOT NULL,
> [Order_Status] [nchar](10) NOT NULL,
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
> [ID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
> GO
> ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT
> [CK_Orders_Order_Status] CHECK (([Order_Status]='Shipped' OR
> [Order_Status]='InProgress'))
> GO
> ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [CK_Orders_Order_Status]
>
> CREATE TABLE [dbo].[Order_Details](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [Title] [nchar](10) NOT NULL,
> [Orders_ID] [int] NOT NULL,
> CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
> (
> [ID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
> GO
> ALTER TABLE [dbo].[Order_Details] WITH CHECK ADD CONSTRAINT
> [FK_Order_Details_Orders] FOREIGN KEY([Orders_ID])
> REFERENCES [dbo].[Orders] ([ID])
> GO
> ALTER TABLE [dbo].[Order_Details] CHECK CONSTRAINT [FK_Order_Details_Orders]
> GO
> ALTER TABLE [dbo].[Order_Details] WITH CHECK ADD CONSTRAINT
> [CK_Order_Details] CHECK (([dbo].[Order_Status]([Orders_ID])='InProgress'))
> GO
> ALTER TABLE [dbo].[Order_Details] CHECK CONSTRAINT [CK_Order_Details]
>
> declare @identity as int
>
> insert Orders
> select 'Title'
> , 'InProgress'
>
> set @identity = @@identity
>
> insert Order_Details
> select 'Title'
> , @identity
>
> update Orders
> set Order_Status = 'Shipped'
>
> update Order_Details
> set Title = 'Title'
>
> --Why does CK_Order_Details fire in the next update but not with the
> previous update?
> update Order_Details
> set Title = 'Title'
> , Orders_ID = @identity
>
> delete from Order_Details
> delete from Orders
>
> IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[CK_Order_Details]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Order_Details]'))
> ALTER TABLE [dbo].[Order_Details] DROP CONSTRAINT [CK_Order_Details]
> GO
>
> alter
> function [dbo].[Order_Status]
> (
> @Title nchar(10)
> , @Orders_ID int
> )
> returns nchar(10)
> as
> begin
> declare @Order_Status nchar(10)
>
> select @Order_Status = Order_Status
> from Orders
> where ID = @Orders_ID
>
> return @Order_Status
> end
> GO
>
> ALTER TABLE [dbo].[Order_Details] WITH CHECK ADD CONSTRAINT
> [CK_Order_Details] CHECK
> (([dbo].[Order_Status]([Title],[Orders_ID])='InProgress'))
> GO
> ALTER TABLE [dbo].[Order_Details] CHECK CONSTRAINT [CK_Order_Details]
>
> declare @identity as int
>
> insert Orders
> select 'Title'
> , 'InProgress'
>
> set @identity = @@identity
>
> insert Order_Details
> select 'Title'
> , @identity
>
> update Orders
> set Order_Status = 'Shipped'
>
> update Order_Details
> set Title = 'Title'
>
> --CK_Order_Details now fires in the next update and the previous update...
> update Order_Details
> set Title = 'Title'
> , Orders_ID = @identity
>
> drop table Order_Details
> drop table Orders
> drop function [dbo].[Order_Status]

From: 20060524 on 14 Apr 2010 15:00

---

I appreciate your response, Joe!

Please pardon the simplified schema I posted, it was an attempt to replicate
the specific issue at hand.

The use of a surrogate (identity) key, the default data types (i.e. nchar),
the <table name>_<column name> convention used in foreign key notation (i.e.
Orders_ID), and non-standard syntax (insert..select) were only to set the
stage for this post.

The primary business rule I am hoping to enforce is: “inserts and updates to
the Order_Details table should not occur if the Order has already shipped.”

The secondary business rule I am hoping to enforce is: “items in the
Order_Details table associated with an Order that has already shipped can be
re-associated with an Order that is in progress.”

I am also trying to avoid setting the order status at the Order_Details
level, since that would require a separate update. It is not practical in a
situation where there are potentially hundreds of tables related to the
Orders table and all of them need to know what the status of an order is, so
storing the order status in each related table would introduce more issues.

I have struggled coming up with a declarative design that accomplishes these
business rules, so any method which precludes a procedural approach is
welcome.

I tested to see if the DDL you posted would satisfy this, but sadly it does
not. I hope the additional information I have provided in this post helps
clarify. Looking forward to your response, thanks!

CREATE
TABLE Orders
(
order_nbr
INTEGER
NOT NULL
PRIMARY KEY
, something_title
NCHAR(10)
NOT NULL
, order_status
CHAR(10)
DEFAULT 'inprogress'
NOT NULL
CHECK
(
order_status IN ('inprogress', 'shipped')
)
)

CREATE
TABLE Order_Details
(
order_nbr
INTEGER
NOT NULL
REFERENCES Orders(order_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE
, item_nbr
INTEGER
NOT NULL
, PRIMARY KEY
(order_nbr, item_nbr)
, item_status
CHAR(10)
DEFAULT 'inprogress'
NOT NULL
CHECK
(
item_status IN ('inprogress', 'shipped')
)
)

insert Orders
select 1
, 'title'
, 'inprogress'

insert Order_Details
select 1
, 1
, 'inprogress'

update Orders
set order_status = 'shipped'
where order_nbr = 1

--the constraint should prevent this type of update to an order detail when
the order has shipped
update Order_Details
set item_nbr = 2
where order_nbr = 1

insert Orders
select 2
, 'title'
, 'inprogress'

--the constraint should allow this type of update
update Order_Details
set order_nbr = 2
where order_nbr = 1

drop table Order_Details
drop table Orders

From: 20060524 on 14 Apr 2010 15:37

---

Yes, that's exactly the behavior I need enforced :)

Please see my response to Joe Celko for more details. Thanks!

From: Gert-Jan Strik on 14 Apr 2010 16:28

---

20060524(a)newsgroups.nospam,

> The primary business rule I am hoping to enforce is: “inserts and updates to
> the Order_Details table should not occur if the Order has already shipped.”
>
> The secondary business rule I am hoping to enforce is: “items in the
> Order_Details table associated with an Order that has already shipped can be
> re-associated with an Order that is in progress.”

Call me crazy, but to me, that makes no sense at all. First you have a
business rule that says that any order in the status Shipped is final
and should be immutable. Next you have a business rule that alters it
anyway (by deleting the item from the order).


> I have struggled coming up with a declarative design that accomplishes these
> business rules, so any method which precludes a procedural approach is
> welcome.

SQL Server doesn't support Assertions. So unfortunately you cannot
enforce these types of rules declaratively. Because of that, in my
opinion it is better not to try it that way. It is very confusing and
probably not very reliable and/or has unintended side effects. Also,
this approach makes it very hard to maintain. Typically, these kind of
rules are not set in stone and evolve over time.

My approach would be to use stored procedures, and only use these
dedicated stored procedures to change data in the Orders and
Order_Details tables. In these stored procedures, it is much easier to
enforce these rules, and much easier to document and maintain these
rules.

--
Gert-Jan

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: T-SQL cursor - Variables in an email body
Next: sysobject