altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:

> Is there a way to alter a unique clustered index in a table to a primary k
ey
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just t
o
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...[vbcol=seagreen]
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary key
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just to
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>> Is there a way to alter a unique clustered index in a table to a primary
>> key
>> with some magic alter statement?
>> What I want to avoid (if possible) is to run drop/create statement, just
>> to
>> make already unique clustered index to a Primary key.
>> I appreciate your reply. I have sql server 2000 SP4.
>>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

ALTER TABLE (ADD column question)

Hi All,
I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
column with values?
Have any way to do this?
SQL Server 2005
--
ThanksHi
I cannot test it on SQL Server 2005 right now but I did some testing on SQL
Server 2000
CREATE TABLE #Test (col1 INT)
--Insert some data
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
--Alter table
ALTER TABLE #Test ADD col2 INT IDENTITY(1,1) NOT NULL
GO
ALTER TABLE #Test ADD CONSTRAINT my_coms UNIQUE NONCLUSTERED (col2)
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>|||"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:<#xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl>...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
You can add a non-nullable column by specifying a default and then dropping
it afterwards. Example:
ALTER TABLE tbl
ADD x INTEGER NOT NULL
CONSTRAINT df_tbl_x DEFAULT (0) ;
ALTER TABLE tbl DROP CONSTRAINT df_tbl_x ;
As for adding the unique constraint, obviously you'll have to populate the
column with unique values first. You haven't told us what this data is or
where it comes from so it's hard to help you with that. Is this supposed to
be a surrogate key? Are you aware of the IDENTITY feature in SQL Server?
David Portas
SQL Server MVP
--|||As David Says, If you want to add a new column which is not null, you MUSt
provide a default non null value - otherwise what will the value for the
existing value for rows be - except null... Afterwords, you can drop the
default if you wish...
This may run long, becuase will have to re-write all of the existing rows.
Also watch your tran log..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ReTF" wrote:

> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
>|||>> I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE , how I can add this column wit
h values? <<
You can use an ALTER TABLE to add the columns. If you also have a
DEFAULT, you will get a single value; if not, you will get a NULL. Use
the NULL, so you can find problems after the UPDATE.
You have a serious problem because someone missed a key in their data
model. You will need to update the new column with the new key, based
on some rule that matches it to the existing key.
Once those values are in place, you then need to check to see that
there are no NULLs and that all values are unique. Then use another
ALTER TABLE to add UNIQUE NOT NULL constraints.
I did this once when merging two inventory systems that used different
part numbers for the same items. It is a pain and you will probalby
have some errors.|||> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
Not unless you use the IDENTITY property or NEWID().
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136385177.110647.17120@.o13g2000cwo.googlegroups.com...
> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
> You have a serious problem because someone missed a key in their data
> model. You will need to update the new column with the new key, based
> on some rule that matches it to the existing key.
> Once those values are in place, you then need to check to see that
> there are no NULLs and that all values are unique. Then use another
> ALTER TABLE to add UNIQUE NOT NULL constraints.
> I did this once when merging two inventory systems that used different
> part numbers for the same items. It is a pain and you will probalby
> have some errors.
>

Alter table

Need some help with the following
My goal is to create a unique field by Concatenating two columns.
I am getting the following error
"Warning: The table 'Copy_AFS' has been created but its maximum row size
(928267) exceeds the maximum number of bytes per row (8060). INSERT or UPDAT
E
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
Warning! The maximum key length is 900 bytes. The index 'ak1_some_key' has
maximum length of 16000 bytes. For some combination of large values, the
insert/update operation will fail."
Please let me know if I have the correct statement
ALTER TABLE Copy_AFS
ADD CONSTRAINT Key_TDM
UNIQUE ([CORP-NUM],[REFERENCE-NUMBER]);
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:56E90F9A-EF19-4879-9930-A5229C3F1BFF@.microsoft.com...
> Need some help with the following
> My goal is to create a unique field by Concatenating two columns.
> I am getting the following error
> "Warning: The table 'Copy_AFS' has been created but its maximum row size
> (928267) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> Warning! The maximum key length is 900 bytes. The index 'ak1_some_key' has
> maximum length of 16000 bytes. For some combination of large values, the
> insert/update operation will fail."
>
> Please let me know if I have the correct statement
>
> ALTER TABLE Copy_AFS
> ADD CONSTRAINT Key_TDM
> UNIQUE ([CORP-NUM],[REFERENCE-NUMBER]);
>
> Thanks
>
That's not an error, it's a warning. Your table and index allow data that is
larger than the supported maximum. That means you'll receive an error if you
try to populate those columns with data that is too large. You can ignore
the message and continue but the more prudent course of action would be to
change your table design.
There are some solutions but first it would help if you could state what
version and edition of SQL Server you are using.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

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

Alphanumeric Autonumber Primary Key

Hi there,
The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002

Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?the only sane way of doing it is to have an ordinary integer identity column, then produce the alphanumeric value in a view

create view myview as
select 'ABC'+right(cast(pkey as varchar(9)),4) as myalnumkey ...