February 14, 2008 at 10:53 am
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 CONSTRAINTDF_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
February 14, 2008 at 10:43 pm
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
February 14, 2008 at 11:11 pm
Hi
You can use information_schema.table_constraints.
"Keep Trying"
February 18, 2008 at 9:18 am
Chirag (2/14/2008)
HiYou 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.
February 18, 2008 at 10:47 am
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
February 18, 2008 at 7:39 pm
Try this:
[font="Courier New"]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
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 20, 2008 at 1:30 pm
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 CONSTRAINTDF_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).
July 3, 2008 at 11:36 am
Hi...I was just trying to find constraints that existed and I did it this way:
SELECT * FROM dbo.sysobjectsso
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.....
November 12, 2008 at 4:10 pm
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.
[font="Courier New"]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[/font]
So instead of the usual:
[font="Courier New"]ALTER TABLE invrecvr ADD CONSTRAINT DF_invrecr_IsExcluded DEFAULT 0 FOR IsExcluded[/font]
And receiving the warning: [font="Courier New"]Column already has a DEFAULT bound to it.[/font]
Just add this line prior to the ALTER TABLE command:
[font="Courier New"]EXEC spDropDefaultConstraint @tableName = 'invrecvr', @columnName = 'IsExcluded'[/font]
This could have easily been incorpoarated as a function, but either way, it solves the problem.
Michael
November 17, 2008 at 6:48 am
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'
January 30, 2009 at 4:58 am
You can simply try using:
SELECT [name] from sys.default_constraints
WHERE parent_object_id = OBJECT_ID(N'[dbo].tbYourTableName')
AND ([name] = 'YourConstraintName' )
May 9, 2011 at 4:57 pm
This is a good solution i have used this like this
Many thanks
Rohan
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy