Char and Varchar: constraint

Showing posts with label constraint. Show all posts

Monday, March 19, 2012

Check for constraint on delete

How do you code a procedure to delete a record where if it has an
error (because of foeign key constraint , no cascade and related
records) it will continue and do an update instead.
I have tried this but it doesnt continue if the delete hits an error.
Thanks
Create procedure dbo.delete_record
@.id smallint
as
delete from table
where id=@.id
if @.@.error <>0
update table set deleted=1
where id=@.idwhy don't you update before deleting?

Sunday, March 11, 2012

Check Contraint question

I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),
1;LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:

>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Check Contraint question

I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) =
0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:

>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

CHECK CONTRAINT issue

I need something like this:
ALTER TABLE MatchResults
ADD CONSTRAINT ck_MatchResults
CHECK (
NOT EXISTS (
SELECT B1.Id, B2.Id
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
)
)
But it yields following errors:
Server: Msg 8142, Level 16, State 1, Line 1
Subqueries are not supported in CHECK constraints, table 'MatchResults'.
Server: Msg 1759, Level 16, State 1, Line 1
Invalid column 'ProfielId' is specified in a constraint or computed-column d
efinition.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
I already tried this:
CREATE TRIGGER cti_MatchResults ON MatchResults
INSTEAD OF INSERT
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
INSERT INTO MatchResults
SELECT ParentId, DatMatch, Id, Updated, Deleted
FROM inserted
ELSE
RAISERROR ('WARNING (Insert): you are inserting faulty data into table!', 0,
1) WITH NOWAIT
END
GO
CREATE TRIGGER ctu_MatchResults ON MatchResults
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
UPDATE M
SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Updated
= I.Updated, M.Deleted = I.Deleted
FROM MatchResults M, inserted I
WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.Id
ELSE
RAISERROR (''WARNING (Update): you are inserting faulty data into table!', 0
,1) WITH NOWAIT
END
GO
But for some reason this has no effect at all when I try to update a record
with faulty data (that violates the contraint).
I am updating it via a stored procedure:
CREATE PROCEDURE xsp_AddMatchResult
(
@.ParentId INT,
@.DatMatch DATETIME = NULL,
@.Id INT,
@.Updated DATETIME = NULL,
@.Deleted BIT = 0
) AS SET NOCOUNT ON
IF (@.Updated IS NULL) SET @.Updated = GETDATE()
IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
IF EXISTS(SELECT ParentId FROM MatchResults
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
ELSE
INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
GO
Does anyone have a clue?
LisaHi Lisa
Please check if column ProfielId exists in the Table Bedrijven
thanks and regards
Chandra
"Lisa Pearlson" wrote:

> I need something like this:
> ALTER TABLE MatchResults
> ADD CONSTRAINT ck_MatchResults
> CHECK (
> NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM MatchResults M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> )
> )
> But it yields following errors:
> Server: Msg 8142, Level 16, State 1, Line 1
> Subqueries are not supported in CHECK constraints, table 'MatchResults'.
> Server: Msg 1759, Level 16, State 1, Line 1
> Invalid column 'ProfielId' is specified in a constraint or computed-column
definition.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
>
> I already tried this:
> CREATE TRIGGER cti_MatchResults ON MatchResults
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> BEGIN
> IF (NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM inserted M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> ))
> INSERT INTO MatchResults
> SELECT ParentId, DatMatch, Id, Updated, Deleted
> FROM inserted
> ELSE
> RAISERROR ('WARNING (Insert): you are inserting faulty data into table!',
0,1) WITH NOWAIT
> END
> GO
> CREATE TRIGGER ctu_MatchResults ON MatchResults
> INSTEAD OF UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF (NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM inserted M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> ))
> UPDATE M
> SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Upd
ated = I.Updated, M.Deleted = I.Deleted
> FROM MatchResults M, inserted I
> WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.I
d
> ELSE
> RAISERROR (''WARNING (Update): you are inserting faulty data into table!'
, 0,1) WITH NOWAIT
> END
> GO
> But for some reason this has no effect at all when I try to update a recor
d with faulty data (that violates the contraint).
> I am updating it via a stored procedure:
> CREATE PROCEDURE xsp_AddMatchResult
> (
> @.ParentId INT,
> @.DatMatch DATETIME = NULL,
> @.Id INT,
> @.Updated DATETIME = NULL,
> @.Deleted BIT = 0
> ) AS SET NOCOUNT ON
> IF (@.Updated IS NULL) SET @.Updated = GETDATE()
> IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
> IF EXISTS(SELECT ParentId FROM MatchResults
> WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
> UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
> WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
> ELSE
> INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
> VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
> GO
> Does anyone have a clue?
> Lisa|||Lisa
It's hard to suggest something without seeing the data. But if your stored p
rocedure does the job for you I would not change it to the trigger.
"Lisa Pearlson" <no@.spam.plz> wrote in message news:%23ETDzrEUFHA.2128@.TK2MS
FTNGP15.phx.gbl...
I need something like this:
ALTER TABLE MatchResults
ADD CONSTRAINT ck_MatchResults
CHECK (
NOT EXISTS (
SELECT B1.Id, B2.Id
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
)
)
But it yields following errors:
Server: Msg 8142, Level 16, State 1, Line 1
Subqueries are not supported in CHECK constraints, table 'MatchResults'.
Server: Msg 1759, Level 16, State 1, Line 1
Invalid column 'ProfielId' is specified in a constraint or computed-column d
efinition.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
I already tried this:
CREATE TRIGGER cti_MatchResults ON MatchResults
INSTEAD OF INSERT
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
INSERT INTO MatchResults
SELECT ParentId, DatMatch, Id, Updated, Deleted
FROM inserted
ELSE
RAISERROR ('WARNING (Insert): you are inserting faulty data into table!', 0,
1) WITH NOWAIT
END
GO
CREATE TRIGGER ctu_MatchResults ON MatchResults
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
UPDATE M
SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Updated
= I.Updated, M.Deleted = I.Deleted
FROM MatchResults M, inserted I
WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.Id
ELSE
RAISERROR (''WARNING (Update): you are inserting faulty data into table!', 0
,1) WITH NOWAIT
END
GO
But for some reason this has no effect at all when I try to update a record
with faulty data (that violates the contraint).
I am updating it via a stored procedure:
CREATE PROCEDURE xsp_AddMatchResult
(
@.ParentId INT,
@.DatMatch DATETIME = NULL,
@.Id INT,
@.Updated DATETIME = NULL,
@.Deleted BIT = 0
) AS SET NOCOUNT ON
IF (@.Updated IS NULL) SET @.Updated = GETDATE()
IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
IF EXISTS(SELECT ParentId FROM MatchResults
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
ELSE
INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
GO
Does anyone have a clue?
Lisa|||I'm not sure why you couldn't use a standard After trigger for this. You wan
t
other check and unique constraints to fire. So why not something like:
Create Trigger trigMatchResultsIU
On dbo.MatchResults
For Insert, Update
As
If Not Exists(
Select *
From inserted As I, dbo.Bedrijven As B
Where I.Deleted <> 1
And (
(B.Id = I.ParentId And B.ProfielId <> 3)
Or (B.Id = I.Id And B.ProfielId = 3)
)
)
Begin
Raiserror('Warning Will Robenson! Danger! Danger!, 16, 1)
Rollback Tran
End
Thomas|||You can simply call "rollback tran" inside a trigger to undo the
insert/delete even if you didn't call "begin tran" yourself?
I didn't know that.
Can you attatch multiple triggers to insert/update on same table? Do they
get executed in the order the triggers were created?
Lisa
"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:OezbXQLUFHA.3584@.TK2MSFTNGP14.phx.gbl...
> I'm not sure why you couldn't use a standard After trigger for this. You
> want other check and unique constraints to fire. So why not something
> like:
> Create Trigger trigMatchResultsIU
> On dbo.MatchResults
> For Insert, Update
> As
> If Not Exists(
> Select *
> From inserted As I, dbo.Bedrijven As B
> Where I.Deleted <> 1
> And (
> (B.Id = I.ParentId And B.ProfielId <> 3)
> Or (B.Id = I.Id And B.ProfielId = 3)
> )
> )
> Begin
> Raiserror('Warning Will Robenson! Danger! Danger!, 16, 1)
> Rollback Tran
> End
>
> Thomas
>
>|||Yes you can use Rollback Tran because each DML statement (Insert, Update,
Delete) is in an implicit transaction.
Yes, you can attach multiple Insert, Update and/or Delete triggers on the sa
me
table although you should do it with caution. In general, it is difficult to
determine any sort of firing order with multiple triggers. Thus, I would
recommend that you assume that the order is random when writing triggers.
That said, there is a system stored proc called sp_settriggerorder which wil
l
allow you to specify which trigger should fire first or last. That's about t
he
extent of the firing order.
HTH
Thomas
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23HXATgQUFHA.580@.TK2MSFTNGP15.phx.gbl...
> You can simply call "rollback tran" inside a trigger to undo the insert/de
lete
> even if you didn't call "begin tran" yourself?
> I didn't know that.
> Can you attatch multiple triggers to insert/update on same table? Do they
get
> executed in the order the triggers were created?
> Lisa
> "Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
> news:OezbXQLUFHA.3584@.TK2MSFTNGP14.phx.gbl...
>

Check constraints on Tables within UDFs - cannot drop constraint l

This is probably obscure usage of the SQL Server feature-set, but any help
appreciated.
I attempted to include a CHECK constraint in the table-definition for the
RETURN table value of a UDF. Like this:
create function dbo.MyFunction ()
returns @.r table
( MyColumn int not null,
check (MyColumn in (1,2,3))
)
as
... ... ...
(Greatly simplified of course.)
I succeeded in having it create the constraint, as long as I (a) did not
name it, and (b) did it as a table constraint rather than inline with the
column definition. [These are also odd behaviors to me.]
However, when I later attempt to ALTER FUNCTION to apply a new version, I
get an error that it cannot alter the function because it is being reference
d
by another object, then gives the obviously system-generated name of the
CHECK constraint it created, apparently, under the hood.
It seems the only way to get rid of it now is to DROP the function (which I
do not like for other reasons, preferring "ALTER" until SQL Server gets an
Oracle-esque "create or replace" syntax going).
But outside of that, there seems to be no way to get rid of it. I can't
alter-function-drop-constraint, like one could with a table. And I can't jus
t
drop the constraint by itself.
Thoughts? Suggestions? Future feature request maybe?
It would be nice if table-valued functions were more closely aligned with
tables in functionality.
Eric M. Wilson
www.datazulu.comHi
Your finding seem to be correct! It does seem to be an obscure requirement
and I can not think of a reason why you would want to do this. The most
obvious way to get around it is to work with a table variable within the
function that has the constraint and remove it from the function.
If you have any requests for additional/changed functionality you can email
them too SQLWish@.microsoft.com
John
"Eric Wilson" wrote:

> This is probably obscure usage of the SQL Server feature-set, but any help
> appreciated.
> I attempted to include a CHECK constraint in the table-definition for the
> RETURN table value of a UDF. Like this:
> create function dbo.MyFunction ()
> returns @.r table
> ( MyColumn int not null,
> check (MyColumn in (1,2,3))
> )
> as
> ... ... ...
> (Greatly simplified of course.)
> I succeeded in having it create the constraint, as long as I (a) did not
> name it, and (b) did it as a table constraint rather than inline with the
> column definition. [These are also odd behaviors to me.]
> However, when I later attempt to ALTER FUNCTION to apply a new version, I
> get an error that it cannot alter the function because it is being referen
ced
> by another object, then gives the obviously system-generated name of the
> CHECK constraint it created, apparently, under the hood.
> It seems the only way to get rid of it now is to DROP the function (which
I
> do not like for other reasons, preferring "ALTER" until SQL Server gets an
> Oracle-esque "create or replace" syntax going).
> But outside of that, there seems to be no way to get rid of it. I can't
> alter-function-drop-constraint, like one could with a table. And I can't j
ust
> drop the constraint by itself.
> Thoughts? Suggestions? Future feature request maybe?
> It would be nice if table-valued functions were more closely aligned with
> tables in functionality.
> --
> Eric M. Wilson
> www.datazulu.com

Check constraints on clustered columns

Hi guys 'n gals, I'm having an issue wrapping my head around a check constraint that I need to set on a table in my database.

Table: OnCall
Columns: OnCall_PKey (identity), Person_Key, StartDate, EndDate

When a new record is entered, I need a check constraint to make sure that the person entered does not already exist in the table with an overlapping time period:

If in the new record, the start date or the end date fall between the start date and the end date for an existing record having the person key in the new record then the record fails the check.

Example:
One existing data row from my table:
1496, 06/12/2007, 12/12/2007

I try to add:
1496, 09/12/2007, 15/12/2007

The record fails because the new date range overlaps the existing record in the table. No person can have overlapping time periods, however, a person can have multiple time slots in the table, it's just that none of the time slots may overlap.

Any pointers, will be gratefully received.What about defining a trigger on the table ?

Check Constraint?

In SQL Server 2000, I want to apply a check constraint on a column type varchar that it is not duplicated. Could someone help me out with this?

Also, should I just make this column the primary key instead of an identity field being the primary key?

What is the performance difference by applying the constraint rather then just making it the primary key?

Mike BTry using a unique constraint !!

Check constraint?

I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
MikeThis is a multi-part message in MIME format.
--=_NextPart_000_00F6_01C3529D.605F7E80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
Mike
--=_NextPart_000_00F6_01C3529D.605F7E80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You can use an indexed view to enforce =the uniqueness for codes > 5650000:
create view =dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > =5650000
go
create unique clustered index =idx on MyView (ProjectCode)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because we =have manyprojects pending that have not been assigned project codes yet =there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a user =added a projectcode that had already been used. What can I do to =prevent this in thefuture?Thanks for any =helpMike

--=_NextPart_000_00F6_01C3529D.605F7E80--|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C3529E.BB1064B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I am kind of new to this. I tried this and I got the message
Cannot create index on view 'myview' because the view is not schema =bound.
What does this mean?
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eArxy7rUDHA.1916@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there =are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
Mike
--=_NextPart_000_001D_01C3529E.BB1064B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I am kind of new to this. I tried =this and I got the message
Cannot create index on view 'myview' =because the view is not schema bound.
What does this mean?
Mike
"Tom Moreau" = wrote in message news:eArxy7rUDHA.1916=@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to =enforce the uniqueness for codes > 5650000:

create view =dbo.MyView
as
select =ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go

create unique clustered index =idx on MyView (ProjectCode)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We =use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because we =have manyprojects pending that have not been assigned project codes yet =there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a =user added a projectcode that had already been used. What can I do to =prevent this in thefuture?Thanks for any helpMike

--=_NextPart_000_001D_01C3529E.BB1064B0--|||This is a multi-part message in MIME format.
--=_NextPart_000_002F_01C352AD.D241BFD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Makes sense. Thanks for the help.
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eYrWjDsUDHA.3152@.tk2msftngp13.phx.gbl...
Oops. Here's the revised code:
create view dbo.MyView
with schemabinding
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
Schema binding ensures that any attempt to change an object referenced =by the view will fail.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:u0GmDBsUDHA.1928@.TK2MSFTNGP12.phx.gbl...
I am kind of new to this. I tried this and I got the message
Cannot create index on view 'myview' because the view is not schema =bound.
What does this mean?
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eArxy7rUDHA.1916@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use =a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there =are
multiple default values of 5650000. Is there any way to apply a =check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in =the
future?
Thanks for any help
Mike
--=_NextPart_000_002F_01C352AD.D241BFD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Makes sense. Thanks for the help.
Mike
"Tom Moreau" = wrote in message news:eYrWjDsUDHA.3152=@.tk2msftngp13.phx.gbl...
Oops. Here's the revised code:

create view =dbo.MyView
with schemabinding
as
select =ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go

Schema binding ensures that any =attempt to change an object referenced by the view will fail.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Mike" wrote in message news:u0GmDBsUDHA.1928=@.TK2MSFTNGP12.phx.gbl...
I am kind of new to this. I =tried this and I got the message

Cannot create index on view 'myview' =because the view is not schema bound.

What does this mean?

Mike
"Tom Moreau" = wrote in message news:eArxy7rUDHA.1916=@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to =enforce the uniqueness for codes > 5650000:

create view =dbo.MyView
as
select =ProjectCode
from =dbo.MyTable
where ProjectCode > 5650000
go

create unique clustered =index idx on MyView (ProjectCode)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We =use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because =we have manyprojects pending that have not been assigned project codes =yet there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a =user added a projectcode that had already been used. What can I =do to prevent this in thefuture?Thanks for any helpMike

--=_NextPart_000_002F_01C352AD.D241BFD0--

Check Constraint Violation

I am getting a check constraint error on the following query.
INSERT TABLEA
( COL1, COL2, COL3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
returning 20000. All columns are of datatype INT as is the return value of
the function.
If i replace the function call with a literal 0, it works, but having the
function in there violates the constraint on COL3, despite the value still
being 0.
Does anyone have any idea why this is happening? I have tried making COL3 a
computed column, but can't have a constraint on a computed column, tried
making the select statement into a derived table and selecting from that int
o
my insert statement.
This is being done on SQL Server 2000 Enterprise.
Thank you
Clint ColefaxHi
What is the error that you received.
and what was displayed when you tried:
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:

> I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that i
nto
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||The error received was a violation of check constraint.
INSERT statement conflicted with COLUMN CHECK constraint...
Execute that select statement returns as expected
20000, -20000, 0
Thank you
Clint Colefax|||Hi
Can you try as
INSERT INTO TABLEA
SELECT dbo.Function1(),-20000, dbo.Function1() + (-20000)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:

> The error received was a violation of check constraint.
> INSERT statement conflicted with COLUMN CHECK constraint...
> Execute that select statement returns as expected
> 20000, -20000, 0
> Thank you
> Clint Colefax
>|||What data types are the columns and the function?
If they are not of an exact type, but e.g. "real", the third expression may
evaluate to a value slightly below 0, although it is displayed as 0. CAST to
integer to avoid this problem.
I hope this helps!
Martin
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:42D1D89D-9EA0-4F00-8F91-D8D55732C387@.microsoft.com...
>I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
> a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that
> into
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||On Mon, 30 May 2005 19:19:41 -0700, Clint Colefax wrote:

>I am getting a check constraint error on the following query.
>INSERT TABLEA
>( COL1, COL2, COL3)
>SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
>There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
>returning 20000. All columns are of datatype INT as is the return value of
>the function.
Hi Clint,
I could not reproduce this behaviour (see repro script below). Could you
post a repro script for me to run and reproduce the error?

>I have tried making COL3 a
>computed column, but can't have a constraint on a computed column,
If Col3 is always equal to Col1 - Col2, you should make it a computed
column. You can replace the check constraint wiuth the following
equivalent:
CHECK (Col1 >= Col2)
Here's the script I used to try to reproduce your problem, and the
output I got from it:
create table TableA
(Col1 int not null,
Col2 int not null,
Col3 int not null,
PRIMARY KEY (Col1),
CHECK (Col3 >= 0)
)
go
create function dbo.Function1()
returns int
as
begin
return 20000
end
go
INSERT TableA
( Col1, Col2, Col3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
go
select * from TableA
go
drop function dbo.Function1
go
drop table TableA
go
Col1 Col2 Col3
-- -- --
20000 -20000 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I gave the earlier example as I didn't want to post all of the infrastructur
e
around this problem. With the following cut down version, I am still able to
replicate the problem.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST
Thank you
Clint Colefax|||Sorry, I didn't think out my example very well, the following is code that
should reproduce the error.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST|||Thank you but all datatype are of INT, all match, even using a CAST or
CONVERT statement does not get around the problem (had previouslty attempted
).
Thank you for your input
Clint Colefax|||It's a bug, and a surprising one:
CREATE TABLE TEST (
a int not null,
b smalldatetime not null,
constraint finalv CHECK (a >= 0)
)
go
INSERT TEST(a) SELECT 1
Gives this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.TEST';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Can you post the result of
SELECT @.@.version
so we can see what version you're running?
I verified this on 8.00.2039, and will report it to Microsoft.
SK
Clint Colefax wrote:

>there error is a check constraint violation for the FINAL_VALUE constraint.
>Server: Msg 547, Level 16, State 1, Line 1
>INSERT statement conflicted with COLUMN CHECK constraint
>'CK__test__FINAL_VALU__3AA27A0F'. The conflict occurred in database
>'LIPSDev', table 'test', column 'FINAL_VALUE'.
>The statement has been terminated.
>Thank you
>

Check Constraint UDF: Update vs. Insert

I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
> > I am using a check constraint on one of my tables. It appears to me
> > that the constraint processes an insert AFTER the row is inserted on
> > an insert, but BEFORE the data is updated on an update. here's my
> > example:
> > Table:
> > CREATE TABLE [test1] (
> > [test] [int] NOT NULL )
> > Function:
> > CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> > RETURNS INT
> > AS
> > BEGIN
> > declare @.cnt int
> > select @.cnt = count(*) from test1 where test=@.test
> > return @.cnt
> > END
> > here's my constraint added to the table test1:
> > alter table test1 add constraint ck_test1 check
> > (dbo.fn_RI_test([test])=1)
> > Now, if I insert new rows:
> > insert into test1 values (1) - works fine
> > insert into test1 values (2) - works fine
> > insert into test1 values (1) - error:
> > Server: Msg 547, Level 16, State 1, Line 1
> > INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> > The conflict occurred in database 'JHRA_Test', table 'test1', column
> > 'test'.
> > The statement has been terminated.
> > BUT, if I perform an update:
> > update test1 set test=1 where test=2
> > IT WORKS and now there are 2 rows with test=1
> > Am I crazy? Is there any way for me to know in the function whether
> > this is an update or an insert? Any other options? BTW, I don't need
> > to make a single column unique in this manner, my needs are much more
> > complex. this is just an illustration which demonstrates my issue.
> > Thanks!- Hide quoted text -
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
> > Alejandro, thanks for the reply - I was trying to find some resource
> > to confirm or deny the workings so I appreciate it.
> > I will share the true source of my problem in case you, or others,
> > would like to weigh in on the best method.
> > Basically, all of our data tables have a delete_flag bit field.
> > At the same time, I am trying to disallow duplicate combination of 3
> > fields. (sounds like a primary key, huh?)
> > Each table has an Identity field as the primary key.
> > I can't create a unique index on the three field combo because I allow
> > logical deletes using the delete_flag field.
> > I tried including the delete_flag in a four field index, but then you
> > can only delete each unique combination once.
> > I need unlimited logically deleted rows where my three fields are not
> > necessarily unique,
> > but only one non-deleted one row for each unique combination.
> > make sense?
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > rich wrote:
> >
> > > Alejandro, thanks for the reply - I was trying to find some resource
> > > to confirm or deny the workings so I appreciate it.
> >
> > > I will share the true source of my problem in case you, or others,
> > > would like to weigh in on the best method.
> >
> > > Basically, all of our data tables have a delete_flag bit field.
> > > At the same time, I am trying to disallow duplicate combination of 3
> > > fields. (sounds like a primary key, huh?)
> > > Each table has an Identity field as the primary key.
> > > I can't create a unique index on the three field combo because I allow
> > > logical deletes using the delete_flag field.
> > > I tried including the delete_flag in a four field index, but then you
> > > can only delete each unique combination once.
> >
> > > I need unlimited logically deleted rows where my three fields are not
> > > necessarily unique,
> > > but only one non-deleted one row for each unique combination.
> >
> > > make sense?
> >
> > The solution(s) to the problem "unique constraint with multiple NULLs"
> > also applies to your case, only in your case the delete flag acts as a
> > NULL.
> >
> > So for example, something like the script below could be a solution:
> >
> > ALTER TABLE YourTable
> > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> >
> > ALTER TABLE YourTable
> > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> >
> > HTH,
> > Gert-Jan- Hide quoted text -
> >
> > - Show quoted text -|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
> > Gert-Jan, thanks for the tip!
> > I get it and it makes sense.
> > I also just tried a trigger (based on the above "test1" table) and it
> > works too.
> > Not sure what I'd rather do - add a new trigger to a bunch of tables
> > or a column and constraint.
> > here's the trigger in case anyone is interested:
> > CREATE TRIGGER dbo.tr_Test
> > ON [dbo].[test1]
> > FOR INSERT, UPDATE
> > AS
> > declare @.cnt int
> > BEGIN
> > select @.cnt=count(*) from test1 where test in (select test from
> > inserted)
> > if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> > updated row is included in the test1 table
> > END
> > Thanks for everyone's help!
> > On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > > rich wrote:
> > > > Alejandro, thanks for the reply - I was trying to find some resource
> > > > to confirm or deny the workings so I appreciate it.
> > > > I will share the true source of my problem in case you, or others,
> > > > would like to weigh in on the best method.
> > > > Basically, all of our data tables have a delete_flag bit field.
> > > > At the same time, I am trying to disallow duplicate combination of 3
> > > > fields. (sounds like a primary key, huh?)
> > > > Each table has an Identity field as the primary key.
> > > > I can't create a unique index on the three field combo because I allow
> > > > logical deletes using the delete_flag field.
> > > > I tried including the delete_flag in a four field index, but then you
> > > > can only delete each unique combination once.
> > > > I need unlimited logically deleted rows where my three fields are not
> > > > necessarily unique,
> > > > but only one non-deleted one row for each unique combination.
> > > > make sense?
> > > The solution(s) to the problem "unique constraint with multiple NULLs"
> > > also applies to your case, only in your case the delete flag acts as a
> > > NULL.
> > > So for example, something like the script below could be a solution:
> > > ALTER TABLE YourTable
> > > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> > > ALTER TABLE YourTable
> > > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> > > HTH,
> > > Gert-Jan- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-Paul
CHECK constraint work at row-by-row basis. I suggest you use a trigger instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegro ups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||You can also create a view for STATUS = 2 and create a unique clustered index
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:

>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:

>You can also create a view for STATUS = 2 and create a unique clustered index
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegroups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||You can also create a view for STATUS = 2 and create a unique clustered index
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:
>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:
>You can also create a view for STATUS = 2 and create a unique clustered index
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CHECK Constraint to prevent a conditional duplicate

Hi,

I need to enforce that a table does not have "duplicates" for a
specific status type in the table.

If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.

I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.

Just when the status = 2, there can not be any other rows with the same
ID and status = 2.

Any ideas?

-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger instead.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/

<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegro ups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:

>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul

Hi Paul,

Apart from the trigger Tibor suggests, there are two other options:

1. Use an indexed view:

CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go

2. Use a computed column (assuming PKCol is the primary key):

ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go

(both versions untested - bewarer of typos!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Monday, March 19, 2012

Check for constraint on delete

Sunday, March 11, 2012

Check Contraint question

Check Contraint question

CHECK CONTRAINT issue

Check constraints on Tables within UDFs - cannot drop constraint l

Check constraints on clustered columns

Check Constraint?

Check constraint?

Check Constraint Violation

Check Constraint UDF: Update vs. Insert

Check Constraint UDF: Update vs. Insert

Check Constraint UDF: Update vs. Insert

CHECK Constraint to prevent a conditional duplicate

CHECK Constraint to prevent a conditional duplicate

CHECK Constraint to prevent a conditional duplicate

Char and Varchar

Blog Archive

About Me