Solved

Default constraint related in SQL

Posted on 2013-05-31
8
501 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now