Alter column add constraint unique

Is it possible to alter a table column data type AND add a unique
constraint at the same time?
I can get this to work
ALTER TABLE tablename ALTER COLUMN colName DataType(optional size);
and I can get this to work
ALTER TABLE tablename ADD CONSTRAINT UQ_myConstraint UNIQUE
but I can't get both to work at once and don't feel BOL is very
clear.
ThanksNo, you have to do it one at a time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:e64ur1p66uqe09e2r5e6njer7r8mc0bms7@.
4ax.com...
> Is it possible to alter a table column data type AND add a unique
> constraint at the same time?
> I can get this to work
> ALTER TABLE tablename ALTER COLUMN colName DataType(optional size);
> and I can get this to work
> ALTER TABLE tablename ADD CONSTRAINT UQ_myConstraint UNIQUE
> but I can't get both to work at once and don't feel BOL is very
> clear.
> Thanks|||Think about the BASICS!!
SQL is a set oriented language. Everything happens at once. If I
created a column, how the hell would I assign a unique value to each
row' Such things would be ordered an there is no order in RM.|||Then any other constraints will also have to be done seperately, for
instance - DEFAULT.
Correct?
Thanks for the replies
Jeff
On Sat, 07 Jan 2006 00:57:06 GMT, Jeff User <jeff31162@.hotmail.com>
wrote:

>Is it possible to alter a table column data type AND add a unique
>constraint at the same time?
>I can get this to work
>ALTER TABLE tablename ALTER COLUMN colName DataType(optional size);
>and I can get this to work
>ALTER TABLE tablename ADD CONSTRAINT UQ_myConstraint UNIQUE
>but I can't get both to work at once and don't feel BOL is very
>clear.
>Thanks|||No, check constraints and default constraints can be defined with the
column:
ALTER TABLE tbl
ADD SomeCol INT NOT NULL DEFAULT (10)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:6kdur1lfa8s38pjr730eets11vjpsnggkh@.
4ax.com...
> Then any other constraints will also have to be done seperately, for
> instance - DEFAULT.
> Correct?
> Thanks for the replies
> Jeff
> On Sat, 07 Jan 2006 00:57:06 GMT, Jeff User <jeff31162@.hotmail.com>
> wrote:
>
>|||That is adding a new column. And it works well.
But what about altering an existing column?
Assuming there is no existing Default value:
ALTER TABLE tester
ALTER COLUMN fld9 varchar(30) NOT NULL DEFAULT 'hello'
This doesn't work, I get error near DEFAULT.
I think Adding DEFAULT has to be done seperately. This works:
ALTER TABLE tester
ADD CONSTRAINT makeup_a_name DEFAULT 'test value' FOR fieldName
If there is a way though, to combine these, I would be mighty
interested.
Jeff
On Fri, 6 Jan 2006 22:42:56 -0500, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:

>No, check constraints and default constraints can be defined with the
>column:
>ALTER TABLE tbl
>ADD SomeCol INT NOT NULL DEFAULT (10)
>
>--
>Adam Machanic
>Pro SQL Server 2005, available now
>http://www.apress.com/book/bookDisplay.html?bID=457|||No, there isn't a way to combine them. Column constraints can only be
defined when creating columns...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:pmeur1hgvletj6jvu7tsfffol5het9tj7q@.
4ax.com...
> That is adding a new column. And it works well.
> But what about altering an existing column?
> Assuming there is no existing Default value:
> ALTER TABLE tester
> ALTER COLUMN fld9 varchar(30) NOT NULL DEFAULT 'hello'
> This doesn't work, I get error near DEFAULT.
> I think Adding DEFAULT has to be done seperately. This works:
> ALTER TABLE tester
> ADD CONSTRAINT makeup_a_name DEFAULT 'test value' FOR fieldName
> If there is a way though, to combine these, I would be mighty
> interested.
> Jeff
> On Fri, 6 Jan 2006 22:42:56 -0500, "Adam Machanic"
> <amachanic@.hotmail._removetoemail_.com> wrote:
>
>|||IDENTITY property
NEWID()
Have a default based from the result of a UDF value.
Order aside there are times when adding say a column with the IDENTIYY
property is really useful - consider data cleansing, siutation where you are
merging the output from two systems to get rid of duplicates.
Why go to the hassle of adding a new column and then having to write your
own unique number generator, simple type the extra 20 or so characters and
the ALTER TABLE statement will do it for you - KISS (Keep It Simple Sweet)
rather than spinning out the work required so you get paid more.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136602552.703133.30640@.f14g2000cwb.googlegroups.com...
> Think about the BASICS!!
> SQL is a set oriented language. Everything happens at once. If I
> created a column, how the hell would I assign a unique value to each
> row' Such things would be ordered an there is no order in RM.
>|||You would have to drop the existing DEFAULT constraint first. So
BEGIN TRANSACTION
ALTER TABLE .. DROP CONSTRAINT old_default
ALTER TABLE .. ADD COSNTRAINT new_default DEFAULT .. FOR column
COMMIT TRANSACTION
With the proper transaction isolation level, the transaction wrapper
prevent changes between the two statements.
Gert-Jan
Jeff User wrote:
> That is adding a new column. And it works well.
> But what about altering an existing column?
> Assuming there is no existing Default value:
> ALTER TABLE tester
> ALTER COLUMN fld9 varchar(30) NOT NULL DEFAULT 'hello'
> This doesn't work, I get error near DEFAULT.
> I think Adding DEFAULT has to be done seperately. This works:
> ALTER TABLE tester
> ADD CONSTRAINT makeup_a_name DEFAULT 'test value' FOR fieldName
> If there is a way though, to combine these, I would be mighty
> interested.
> Jeff
> On Fri, 6 Jan 2006 22:42:56 -0500, "Adam Machanic"
> <amachanic@.hotmail._removetoemail_.com> wrote:
>