|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Alter column add constraint uniqueIs 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, you have to do it one at a time.
-- Show quoteAdam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 -- "Jeff User" <jeff31***@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. 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. Show quote "--CELKO--" <jcelko***@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. > 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 <jeff31***@hotmail.com> wrote: Show quote >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) -- Show quoteAdam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 -- "Jeff User" <jeff31***@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 <jeff31***@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 > 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: Show quote >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... -- Show quoteAdam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 -- "Jeff User" <jeff31***@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: > >>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 > 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: Show quote > > 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  |
|||||||||||||||||||||||
Other interesting topics