Solved

Default constraint related in SQL

Posted on 2013-05-31
8
507 Views
Last Modified: 2014-11-10
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
Comment
Question by:Easwaran Paramasivam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39210409
What makes you think the CONSTRAINT does not have a name?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39210420
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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39212312
@ 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39213089
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
 

Expert Comment

by:Endurance Umenzeh
ID: 40425969
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40430424
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
 

Expert Comment

by:Endurance Umenzeh
ID: 40432136
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40433295
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question