Default Constraints

• AVB

  SSChampion

  Points: 11418

  More actions

  November 16, 2006 at 8:49 am

  #193168

  I am running a 2000 server but on my dev machine I have 2005 Management Studio. When I open up the constraints folder under a table it shows several constraints prefixed DF_tablename_(Although in Enterprise Manager I can't find them anywhere).

  Are these default constraints that have been created? If so why are they created? I can't seem to find where these constraints are editable.  TIA for any responses.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 16, 2006 at 9:02 am

  #672332

  default constraints are part of a column definition...so you could see them by running sp_help tablename on a table, and any/all constraints on the table would appear on the list.

  here's an example:

  CREATE TABLE TEST(

  TESTID int     identity(1,1) not null PRIMARY KEY,

  AGE  int not null default (0)   CHECK (AGE >0),

  GENDER  CHAR(1) not null default ('U') CHECK (GENDER IN('M','F','U')), --male/female/undefined

  SOMEFLAG CHAR(1) not null default('N') CHECK (SOMEFLAG='Y' OR SOMEFLAG='N') )

  exec sp_help test

  drop table test

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• AVB

  SSChampion

  Points: 11418

  More actions

  November 16, 2006 at 9:15 am

  #672347

  Ok I understand Default Constraints now... These columns all have default values set to them.

  I was looking into constraints because we are calling a view from an application and I'm getting a "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." error.

  Thanks for the post.