November 23, 2016 at 4:05 pm
HI,
Im checking default constraint for 2 columns.I want to check whether the below mention query is correct?
IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF_locked]') AND object_id = OBJECT_ID(N'[dbo].[DF_count]'))
BEGIn
ALTER TABLE [dbo].[a] ADD CONSTRAINT DF_bit DEFAULT 0 FOR [bit]
ALTER TABLE [dbo].[a] ADD CONSTRAINT DF_count DEFAULT 0 FOR [count]
END
November 28, 2016 at 1:46 pm
No, it isn't.
First, the first constraint name in the EXISTS is not the same as the first one in the code.
Second, you really need to check for, and create, these constraints separately, since either could exist without the other one.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 29, 2016 at 3:38 pm
@Patrick123 (11/23/2016)
IF NOT EXISTS(SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[DF_locked]')
AND object_id = OBJECT_ID(N'[dbo].[DF_count]'))
1. This will NEVER EXIST. A single object_id can never be equal to two different objects IDs at the same time. I think you're confusing the AND & OR operators.
It should be written:
WHERE object_id = OBJECT_ID( N'[dbo].[DF_locked]' )
OR object_id = OBJECT_ID( N'[dbo].[DF_count]' )
2. If the constraint already exists, you're assuming it has the definition you want. I've seen bugs get introduced with this approach. What if DF_Bit exists, but is applied to a different column then you're expecting?
3. As mentioned by ScottPletcher, you should really break the work into a separate statement for each constraint. It is possible that one of the constraint will exist, but not the other. Depending on how you fix your statement in my #1 above, you may end up missing a constraint, or have your statement fail when you try to create an existing constraint.
4. My preferred syntax for an exists test of this type is
IF OBJECT_ID('schema_name.object_name') IS NOT NULL DROP TABLE 'schema_name.object_name';
It is easily understood, fits nicely on a single line and is less typing.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply