3

I need to change a default constraint value from 0 to 1. This is done easy with:

ALTER TABLE table DROP CONSTRAINT X
ALTER TABLE table ADD CONSTRAINT X default (1) for table.column

The problem is that I don't want to drop and create a new constraint every time I run modelupdate on my database. That's why I want to run this code IF the value of the constraint is 0.

Is it possible to check the value of a default constraint in SQL, if yes, how?

  • 1
    Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Oct 15 '15 at 11:39
  • 1
    Sorry! Now its there. –  Oct 15 '15 at 11:40
  • 1
    "I don't want to drop and create a new constraint every time I recreate my database" -- When you re-create your database, you don't have to first drop the constraint anyway, because it won't be there yet. When you re-create your database, you will always need to create your constraint. –  Oct 15 '15 at 11:42
  • SELECT * FROM sys.default_constraints \\\ definnition – Giorgi Nakeuri Oct 15 '15 at 11:44
  • Yeah, I asked the question a bit wrong. Everytime we do a update to the database we commit it in a large SQL file that check if table exists or column exists. If not, then create. I want the same to happen with the constraint. –  Oct 15 '15 at 11:47

2 Answers2

7

You can find the "definition" of the default constraint like this:

SELECT 
    DefaultConstraintName = df.name,
    df.definition
FROM 
    sys.default_constraints df
INNER JOIN 
    sys.tables t ON df.parent_object_id = t.object_id
INNER JOIN 
    sys.columns c ON c.object_id = df.parent_object_id AND df.parent_column_id = c.column_id
WHERE 
    t.Name = N'YourTableNameHere'
    AND c.Name = N'YourColumnNameHere'

That however will be returning a string representation of your default value - not the actual value itself (in whatever datatype it is). But that might help you find what you need

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thank you. I simplifed it a bit: IF exists (select 'x' from sys.default_constraints where name= 'DF' and definition = '((0))') –  Oct 15 '15 at 11:52
  • 1
    @user3025336 - of course, your simplification answers a different question - "is there a default constraint with a default value of 0 applied to *any* column in *any* table within this database?" - which may be sufficient for your purposes but would generally not be a good solution. – Damien_The_Unbeliever Oct 15 '15 at 12:11
  • @user3025336: works - **if** you have only a single default constraint with a value of `((0))` in your database - I have **many** default constraints, therefore I need to be specific about which default constraint (for what table and column) I'm interested in .... – marc_s Oct 15 '15 at 12:17
0

You can see the default value for the column from the below information_schema view.

SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TableName' and COLUMN_NAME = 'ColumnName'
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58