Alter column add constraint unique  
Author Message
jdimo





PostPosted: Fri Jan 06 19:36:32 CST 2006 Top

SQL Server Developer >> 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.
Thanks

SQL Server66  
 
 
Adam





PostPosted: Fri Jan 06 19:36:32 CST 2006 Top

SQL Server Developer >> Alter column add constraint unique No, 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
--




> 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


 
 
--CELKO--





PostPosted: Fri Jan 06 20:55:52 CST 2006 Top

SQL Server Developer >> Alter column add constraint unique 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.

 
 
Jeff





PostPosted: Fri Jan 06 21:34:22 CST 2006 Top

SQL Server Developer >> Alter column add constraint unique Then any other constraints will also have to be done seperately, for
instance - DEFAULT.
Correct?
Thanks for the replies
Jeff




>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

 
 
Adam





PostPosted: Fri Jan 06 21:42:56 CST 2006 Top

SQL Server Developer >> Alter column add constraint unique 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
--




> Then any other constraints will also have to be done seperately, for
> instance - DEFAULT.
> Correct?
> Thanks for the replies
> Jeff
>


>
>>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
>


 
 
Jeff





PostPosted: Fri Jan 06 22:06:26 CST 2006 Top

SQL Server Developer >> Alter column add constraint unique 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"


>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

 
 
Adam





PostPosted: Fri Jan 06 22:26:55 CST 2006 Top

SQL Server Developer >> Alter column add constraint unique 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
--




> 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"

>
>>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
>


 
 
Tony





PostPosted: Sat Jan 07 09:22:41 CST 2006 Top

SQL Server Developer >> Alter column add constraint unique 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




> 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.
>


 
 
Gert-Jan





PostPosted: Sun Jan 08 07:16:00 CST 2006 Top

SQL Server Developer >> Alter column add constraint unique 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



>
> 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"

>
> >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