?
Solved

Default constraint related in SQL

Posted on 2013-05-31
8
Medium Priority
?
509 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

765 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