SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IF EXISTS Default Value constraint


IF EXISTS Default Value constraint

Author
Message
Putts
Putts
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 165
I've found a solution to this but I think there must be a better one....

I'm trying to add a Default Value Constraint but only if the Constraint doesn't already exist.

The solution I've concocted so far looks like......

if not(exists(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AID_AGENCY_STAFF' AND COLUMN_NAME = 'CSR' AND COLUMN_DEFAULT IS NOT NULL))
ALTER TABLE dbo.AID_AGENCY_STAFF ADD CONSTRAINT DF_AID_AGENCY_STAFF_CSR DEFAULT 0 FOR CSR
GO


......however, in my head I'm thinking 'I'm providing a name for the constraint so I should be able to query that somehow' but I couldn't find the INFORMATION_SCHEMA table that stores that information.

Can anyone show me a better way to do this?
TIA
HardCoder
HardCoder
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 103
Your query looks great!!

After this article I tried here and there but colud not get a compact solution, however I get a query to get all the constraints for a table, but you are trying to get which columns are not having constraints.

I am looking for this, hopefully we can get a better solution if there one exist.

Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3465 Visits: 1865
Hi

You can use information_schema.table_constraints.

"Keep Trying"
Putts
Putts
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 165
Chirag (2/14/2008)
Hi

You can use information_schema.table_constraints.



Have you ever tried this for a Default Value Constraint?

I can't find any trace of the constraint in question within that schema anywhere. Even doing a search just based on table name, all I see is the PKey, no other constraints.....and I know that the constraint exists because after using the table_constraint search and finding no trace of the constraint I get a 'Column already has a DEFAULT bound to it' when trying to add it.
HardCoder
HardCoder
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 103
Hi
I find a script which also get the default constraints.

This script I already added to my blog.

http://matespoint.blogspot.com

Thanks.

Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3716 Visits: 939
Try this:

if not exists (select name from sys.objects where type_desc = 'DEFAULT_CONSTRAINT' and name = 'DF_AID_AGENCY_STAFF_CSR')
ALTER TABLE dbo.AID_AGENCY_STAFF ADD CONSTRAINT DF_AID_AGENCY_STAFF_CSR DEFAULT 0 FOR CSR




Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Putts
Putts
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 165
Thanks for everyone's responses.

I ended up taking it to the next level because there's really a double check that needs to be done to be 100% sure that you will be able to create the constraint. You need to make sure that the column in question doesn't already have a default value constraint and you have to make sure no constraint exists with the name you're going to use.

What I ended up with this...

CREATE FUNCTION dbo.FFG_FX_GET_CONSTRAINT_ID (@constraintName varchar(125),@tableName varchar(125),@columnName varchar(125)) RETURNS bit
AS
BEGIN
DECLARE @return int

SELECT @return = id FROM
(
SELECT constid as id FROM sysconstraints
WHERE id = (SELECT id FROM sysobjects WHERE name = @tableName)
AND colid = (SELECT colid FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = @tableName) AND name = @columnName)
UNION
SELECT id FROM sysobjects WHERE name = @constraintName
) TBL

RETURN CONVERT(bit,isnull(@return,0))
END
GO

if not(exists(SELECT 'go' WHERE dbo.FFG_FX_GET_CONSTRAINT_ID('DF_AID_AGENCY_STAFF_CSR','AID_AGENCY_STAFF','CSR') = 1))
ALTER TABLE dbo.AID_AGENCY_STAFF ADD CONSTRAINT DF_AID_AGENCY_STAFF_CSR DEFAULT 0 FOR CSR
GO


It's not too hard to follow.
If either a constraint with that name is found or a constraint on that column is found then the function will return a 1.
If you are not concerned with one of these aspects, then you can just send in '' for either the constraint name or for the table and column name.


I had to use a function because I couldn't find the way to use a SProc within a If exists() statement......so if someone knows that trick then you could easily make this a SProc (which makes more sense given its use).
Angelindiego
Angelindiego
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 445
Hi...I was just trying to find constraints that existed and I did it this way:

SELECT * FROM dbo.sysobjects so
where so.name = 'DF_SortsMtfSendingProcessConfig_SShKeyFile'
and so.xtype = 'D')

I then built a test script that checked other servers to be sure that they existed....

hope this helps. I tried to make it TOO HARD, when it came down to being pretty simple.....


Thank you!!,

Angelindiego

Michael Newell
Michael Newell
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2112 Visits: 710
I ended up taking a slightly different approach and created a stored procedure to call before creating a default constraint. The stored procedure is merely an IF EXISTS...DROP CONSTRAINT query, but determines the default name and only executes if there is in fact, a default constraint currently in place. Originally intended for use within SQL scripts sent to users, this method will not cause an error if ran more than once before a new default constraint is created.

CREATE PROCEDURE [dbo].[spDropDefaultConstraint]
(
@tableName varchar(100),
@columnName varchar(100)
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @dfName varchar(100)

SELECT @dfName = df.[name]
FROM sys.columns c
JOIN sys.default_constraints df
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID(@tableName)
AND c.[name] = @columnName

IF @dfName IS NOT NULL
BEGIN
EXEC ('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @dfName)
PRINT 'Constraint [' + @dfName + '] dropped for ' + @tableName + '.' + @columnName
END
ELSE
PRINT 'Constraint not found for ' + @tableName + '.' + @columnName
END


So instead of the usual:
ALTER TABLE invrecvr ADD CONSTRAINT DF_invrecr_IsExcluded DEFAULT 0 FOR IsExcluded

And receiving the warning: Column already has a DEFAULT bound to it.

Just add this line prior to the ALTER TABLE command:
EXEC spDropDefaultConstraint @tableName = 'invrecvr', @columnName = 'IsExcluded'

This could have easily been incorpoarated as a function, but either way, it solves the problem.

Michael
Putts
Putts
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 165
I also ended up creating a view to be able to see the DF constraints the same way I can see any other constraint via Information Schema views.

It doesn't have everything the official views has but it works pretty well for my needs...

CREATE VIEW [dbo].[SYS_V_DEFAULT_CONSTRAINTS] AS
SELECT c.name as constraint_name,t.name as table_name,cl.name as column_name
FROM
sysconstraints cn
INNER JOIN
sysobjects c on cn.constid = c.id
INNER JOIN
sysobjects t on cn.id = t.id
INNER JOIN
syscolumns cl on t.id = cl.id and cn.colid = cl.colid
WHERE c.xtype = 'D'


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search