Is default a constraint ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is default a constraint ?

Author

Topic

Kali
Starting Member

14 Posts

Posted - 2004-10-26 : 16:33:01

Hi,

I didn't think default is a constraint, but:
ALTER TABLE doc_exe ADD
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081
GO

Does this mean that "column_e_default" is a name of default which is a constraint? Because this is correct too:

ALTER TABLE my_table ADD DEFAULT 10 FOR column1

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 16:34:16

Yes defaults are constraints. Do you have SQL Server Books Online?

Tara

Kali
Starting Member

14 Posts

Posted - 2004-10-26 : 16:38:17

I do. As I said, I saw syntax like:

ALTER TABLE my_table ADD DEFAULT 10 FOR column1

as well. Also, from BOL, under Constraints->overview:
"Using constraints is preferred to using triggers, rules, and defaults"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 16:43:57

Those are different things. The default constraint is to constrain the data. This:

ALTER TABLE my_table ADD DEFAULT 10 FOR column1

is to provide a default value if you don't provide one like this:

INSERT INTO Table1 (Column1, Column3) VALUES ('Tara', 1)

Pretend that Column2 is my column that has a default of 'SQLTeam' on it. Since I have a default, I don't have to provide a value for it on the INSERT. So now if we look at Table1 after the insert, you would see:

Tara SQLTeam 1

Tara

Kali
Starting Member

14 Posts

Posted - 2004-10-26 : 16:54:11

tduggan, thanks for the responce. I understant the 2nd part, which has to do with NSERT INTO Table1 (Column1, Column3) VALUES ('Tara', 1).

But what do you mean by "The default constraint is to constrain the data"? And when would I want to use
ALTER TABLE my_table ADD
column1 DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT 10
GO

rather than
ALTER TABLE my_table ADD DEFAULT 10 FOR column1

thanks again

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 16:59:16

"Using constraints is preferred to using triggers, rules, and defaults"

Well, you can't use a CONSTRAINT instead of a DEFAULT, makes no sense !

BOL seems to have trouble defining if a DEFAULT is a CONSTRAINT or not.
It uses both the terms DEFAULT constraint and DEFAULT definition here & there...

This is highly academic I guess, but yes a DEFAULT would be a constraint in the sense that it
constrains the value of a column when no value is given for an inserted row.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 17:05:00

quote:
Originally posted by Kali
..... And when would I want to use
ALTER TABLE my_table ADD
column1 DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT 10
GO

rather than
ALTER TABLE my_table ADD DEFAULT 10 FOR column1 ...

When you want to put a given name to your DEFAULT constraint.
Also the two samples do different things, in the first case you are adding a column with a DEFAULT, in the second case you are adding a DEFAULT constraint to an existing column.

/rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 17:41:54

"The default constraint is to constrain the data." Here's an example...Let's say we have a column that we want to constrain the data in it so that only values of 0-10 are allowed. So if someone tries to insert a value of 11, the default constraint would not let it. But that's only if you've added the constraint. Some people handle this at the front-end. But it's always good to have the constraint at the database level just in case.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 17:45:03

That is a CHECK constraint Tara, not a DEFAULT constraint.

/rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 17:49:47

Hehe, oops. I knew that; I was reading too many things at once.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 18:15:46

This was quite fun Tara...

create table t( nr int not null default 11 check(nr between 1 and 10 ) )
insert t default values

kind of like making sql server tripping over it's own toes

/rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 18:22:02

quote:

Originally posted by rockmoose

create table t( nr int not null default 11 check(nr between 1 and 10 ) )
insert t default values

Tara

Kali
Starting Member

14 Posts

Posted - 2004-10-26 : 18:41:47

ok, thanks for all your thoughts, guys. I'm understanding from this thread that default IS a sort of constraint, although before I thought it was a separate entity.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 18:45:30

It's a pretty "weak" constraint, as far as constraints go

Now we are all a lot wiser from thinking so much!
cheers,
/rockmoose

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-10-27 : 04:07:21

It is not a constraint in any real sense, they just use that term so they can reconcile it with using the alter table drop constraint syntax for droping the default.

The SQL 2003 standard uses the syntax


alter table t alter column c drop default
-- and
alter table t alter column c set default 4711

which is better than the garbled hodgepodge SQL server is currently using.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-27 : 04:27:05

quote:
originally posted by rockmooseThis is highly academic I guess, but yes a DEFAULT would be a constraint in the sense that it
constrains the value of a column when no value is given for an inserted row.[/i]

Hi Lars, nice to hear from a fellow countryman

Ok, the syntax of sql server when it comes to e.g. DEFAULTS maybe isn't 100% SQL2003 Standard.
But IMO this is pretty close:

ALTER TABLE my_table ADD DEFAULT 10 FOR columnx

Also you have the option of naming the defaults in an "intelligent" manner:

ALTER TABLE my_table ADD CONSTRAINT dflt_10default DEFAULT 10 FOR column2

Anyway...

Question:
Is a DEFAULT value a CONSTRAINT ?
a) Yes
b) No

I vote a)

rockmoose

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-10-27 : 06:35:34

I vote B.

The presence of a default value will not affect which values that can be stored in a column.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-27 : 09:15:51

I agree -- it is not a constraint. that always confused me, too. it does not put any constraints whatsoever on what you can store in that column -- it just sets a default value.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources