|
|
Alter column add constraint unique |
|
Author |
Message |
jdimo
|
Posted: 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
|
Posted: 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--
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
|
|
|
|
|
|