[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

Default constraint related in SQL

I create a new table with default value as given below:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tAccounting' AND 
COLUMN_NAME = 'CreatedDtm' AND DATA_TYPE = 'datetimeoffset')  ALTER TABLE [dbo].[tAccounting] ADD [CreatedDtm] [datetimeoffset]  (2)  NOT NULL  DEFAULT (GETDATE())

Open in new window

The default constraint does not have any name.

GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE PARENT_OBJ = OBJECT_ID(N'tAccountingDtl')  AND NAME = 'DF_tAccountingDtl_CreatedDtm' AND type = 'D')  ALTER TABLE  [dbo].[tAccountingDtl] ADD  CONSTRAINT [DF_tAccountingDtl_CreatedDtm]  DEFAULT (getutcdate()) FOR  [CreatedDtm]
GO

Open in new window


Using above code I  check for existing constraint name. Hence I get an error Column already has a DEFAULT bound to it.

I have below questions:

But this works fine in One DB. But failing in another DB. What would be the problem?

How to ensure that before creating a default constraint whether already default constraint is associated with it (which does not contain name)? If so, how to drop it?


Please do assist.
0
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
  • 5
  • 2
1 Solution
 
Anthony PerkinsCommented:
What makes you think the CONSTRAINT does not have a name?
0
 
Anthony PerkinsCommented:
Actually after re-reading your question I think I understand it now.  You are asking how do you identify DEFAULT CONSTRAINTS that have not been named explicitly.  Here is how:

SELECT  dc.*
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
                                                 AND c.column_id = dc.parent_column_id
WHERE   t.name = 'YourTableNameGoesHere'
        AND c.name = 'YourColumnNameGoesHere'
	AND dc.is_system_named = 1	    -- Comment out if you want all CONSTRAINTs

Open in new window

0
 
Easwaran ParamasivamAuthor Commented:
@ acperkins: I would like to search the default constraints all over the DB without explictly providing Table or Column name. Please let me know how to achieve it?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Anthony PerkinsCommented:
Quite simple, remove the conditions on table and column name, as in:
SELECT  t.name, c.name, dc.*
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
                                                 AND c.column_id = dc.parent_column_id
WHERE   dc.is_system_named = 1	    -- Comment out if you want all CONSTRAINTs and not just the ones that have not been named explicitly

Open in new window

0
 
Endurance UmenzehHead ITCommented:
Dear Team,

 I'm currently have challenges with my  SAGE  Pastel Evolution Synchronization. I' m running Inter branch accounting  with 16 depots.
 None of the 16 Branches are importing from Consolidated. The two major errors i'm getting are:
 1. 2014-11-06 12:40:38.260 [E] An unexpected error occurred during import. Violation of PRIMARY KEY constraint 'PK__etblDeleted'. Cannot insert duplicate key in object 'dbo._etblDeleted'
 2. [W] Import of "F:\ftp\Consolidation\0000-0016-000000.dat" was unexpected. Expected update no: 160.
 3.Argument is incomplete.
 Thank You as you help unravel this issues
0
 
Anthony PerkinsCommented:
Endurance Umenzeh,

Rather than piggy-backing on this thread, I suggest you may have better luck if you post your own question.

Thanks,
Anthony
0
 
Endurance UmenzehHead ITCommented:
Dear Anthony Perkins,

Thank You for your comment, but that is actually my question. What you saw as Piggy -Backing is the error log am getting due to inability to achieve Synchronization.
Thank You
0
 
Anthony PerkinsCommented:
Suit yourself.  If you want quick help with your question, post a new question with a link to this one if you think it is appropriate.  If you don't need quick help, then don't bother about posting a new question.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now