Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IF EXISTS Default Value constraint Expand / Collapse
Author
Message
Posted Thursday, February 14, 2008 10:53 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 89, 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
Post #455866
Posted Thursday, February 14, 2008 10:43 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 18, 2011 12:57 AM
Points: 213, Visits: 97
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
Post #456151
Posted Thursday, February 14, 2008 11:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
Hi

You can use information_schema.table_constraints.



"Keep Trying"
Post #456155
Posted Monday, February 18, 2008 9:18 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 89, 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.
Post #456948
Posted Monday, February 18, 2008 10:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 18, 2011 12:57 AM
Points: 213, Visits: 97
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
Post #456995
Posted Monday, February 18, 2008 7:39 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:29 PM
Points: 2,644, Visits: 826
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
Post #457162
Posted Wednesday, February 20, 2008 1:30 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 89, 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).
Post #458201
Posted Thursday, July 3, 2008 11:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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

Post #528283
Posted Wednesday, November 12, 2008 4:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 14, 2014 3:35 PM
Points: 2,080, Visits: 590
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
Post #601764
Posted Monday, November 17, 2008 6:48 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 89, 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'

Post #603631
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse