Check constraint with a scalar UDF not working

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

From: 20060524 on 13 Apr 2010 20:48

---

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

drop table Order_Details
drop table Orders

From: sloan on 13 Apr 2010 21:05

---

I'm confused.

Where is your scalar UDF?

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!569.entry

Check the post above.......and find the downloadable code.

I have a CHECK Constraint based on an UDF....in the example at the url
above.






<20060524(a)newsgroups.nospam> wrote in message
news:CB675C14-8660-4ED1-90B1-3BF0665ACA62(a)microsoft.com...
> 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
>
> drop table Order_Details
> drop table Orders

From: Tibor Karaszi on 14 Apr 2010 04:09

---

Your problem is that the update looks like:

update Orders
set Order_Status = 'Shipped'

And your constraint definition looks something like (I had a hard time
reading the DDL because of all the square brackets, btw, almost gave up...):

CHECK ((dbo.Order_Status(Orders_ID)='InProgress'))

Look at the two above. Do you see that the constraint definition doesn't (by
looking at it) refer to any of the columns you modify in the update command?
The constraint definition only refer to the Orders_ID columns, and you don't
modify that column in the UPDATE. So, SQL Server doesn't check the
constraint since it considers that the UPDATE cannot break the constraint.
This is one of the traps one can fall in when one uses UDFs in constraints.
I wouldn't be surprised if this has been blogged several times (come to
think of it, perhaps even I have blogged it...)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



<20060524(a)newsgroups.nospam> wrote in message
news:CB675C14-8660-4ED1-90B1-3BF0665ACA62(a)microsoft.com...
> 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
>
> drop table Order_Details
> drop table Orders

From: Tibor Karaszi on 14 Apr 2010 04:13

---

I found my blog post (also see the rather lengthy discussion for
elaboration):
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-careful-with-constraints-calling-udfs.aspx

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



<20060524(a)newsgroups.nospam> wrote in message
news:CB675C14-8660-4ED1-90B1-3BF0665ACA62(a)microsoft.com...
> 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
>
> drop table Order_Details
> drop table Orders

From: --CELKO-- on 14 Apr 2010 12:38

---

We might want to make the schema relational, follow ISO-11179 rules
and so forth before we worry about other fixes. You do know that
IDENTITY is not ever a key by definition, that there is no such
creature as a magical universal “id” in RDBMS, etc.? Did you really
need to use NCHAR() for Chinese or other non-Latin codes? The data
element orders_id should be singular. Etc.

The standard design pattern for orders and their details is like this:

CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
something_title NCHAR(10) NOT NULL,
order_status CHAR(10) DEFAULT 'in progress' NOT NULL
CHECK (order_status IN ('in progress', '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
REFERENCES Inventory(item_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_nbr, item_nbr)
item_status CHAR(10) DEFAULT 'in progress' NOT NULL
CHECK (item_status IN ('in progress', 'shipped'))
..);

Notice the use of a **REAL** relational key instead of a fake pointer
chain, the use of DRI, etc. SQL Server has supported Standard syntax
for years, so you should have written:

INSERT INTO Orders (..) VALUES (..);

thus avoiding dialect. Your whole approach is procedural, so you did
not think of a declarative design and immediately jumped to
proprietary, procedural UDFs.

Can you give a clear statement of what business rule you want to
enforce? Maybe something like: An order as a whole is 'in progress'
if any item on order is 'in progress' or something else?

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