Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

ALTER/DROP/ADD syntax confusion


hi folks,
I'm using SQL server 2005 and I'm trying to drill myself into remembering
how to CREATE, ALTER, DROP constraints, indexes, foreign keys, add columns,
alter columns all that kind of stuff

I hate books online (sorry for the purists out there but i find them
over-verbose and confusing, plus I hate syntax trees with hundreds of
optional arguments) does anyone know of a decent link to a site that just
goes through the typical scenarios...

for example, I'm currently trying to remove the PRIMARY KEYness of a column
but all attempts tell me that it's using an index and attempts to delete the
index tell me that it is being used by the PK... ;-)

so a decent common scenarios web page please

much appreciated and thx in advance,
CharlesA

Could you script out the table in its entirety as well as the DROP statement
that you're using?

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"CharlesA" <Charl@discussions.microsoft.com> wrote in message

news:6B209DEC-B23B-42BF-B3A2-1D39D32C1C85@microsoft.com...
hi folks,
I'm using SQL server 2005 and I'm trying to drill myself into remembering
how to CREATE, ALTER, DROP constraints, indexes, foreign keys, add columns,
alter columns all that kind of stuff

I hate books online (sorry for the purists out there but i find them
over-verbose and confusing, plus I hate syntax trees with hundreds of
optional arguments) does anyone know of a decent link to a site that just
goes through the typical scenarios...

for example, I'm currently trying to remove the PRIMARY KEYness of a column
but all attempts tell me that it's using an index and attempts to delete the
index tell me that it is being used by the PK... ;-)

so a decent common scenarios web page please

much appreciated and thx in advance,
CharlesA

-----------------------------------------------Reply-----------------------------------------------

Hi Tom,

I had deleted my script and when I tried to recreate it, I couldn't even get
the error back

CREATE TABLE Customer
(CustId INT NOT NULL IDENTITY PRIMARY KEY,
 Last VARCHAR(100) NOT NULL,
 First VARCHAR(100) NOT NULL)

can't figure out how to take the PK away (using DDL)

if I do

DROP INDEX customer.PK_Customer

I get

explicit DROP INDEX not allowed on customer.PK_customer, it is being used
for PRIMARY KEY constraint enforcement

which is fair enough, but how to you use DDL to do these things typically,
sql is great at complaining but the error messages whilst painfully clear
don't give you any pointers

any website that could be generally helpful here?

Regards and thanks for your post
CharlesA

-----------------------------------------------Reply-----------------------------------------------

First of all, you should *always* give your constraints a user-friendly
name, thus making it easier to drop them later.  When you create a
constraint without giving it a name, one is generated for you and the name
is ugly.

Run:

sp_help 'Customer'

You'll see the name of the constraints.   Cut and paste the name for the
primary key as follows:

ALTER TABLE Customer
DROP CONSTRAINT [the ugly name that you cut and paste]

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"CharlesA" <Charl@discussions.microsoft.com> wrote in message

news:06CC9EF4-485E-4B32-A63C-B525E46539B3@microsoft.com...
Hi Tom,

I had deleted my script and when I tried to recreate it, I couldn't even get
the error back

CREATE TABLE Customer
(CustId INT NOT NULL IDENTITY PRIMARY KEY,
 Last VARCHAR(100) NOT NULL,
 First VARCHAR(100) NOT NULL)

can't figure out how to take the PK away (using DDL)

if I do

DROP INDEX customer.PK_Customer

I get

explicit DROP INDEX not allowed on customer.PK_customer, it is being used
for PRIMARY KEY constraint enforcement

which is fair enough, but how to you use DDL to do these things typically,
sql is great at complaining but the error messages whilst painfully clear
don't give you any pointers

any website that could be generally helpful here?

Regards and thanks for your post
CharlesA

-----------------------------------------------Reply-----------------------------------------------

On Jun 5, 8:47 am, CharlesA <Charl@discussions.microsoft.com>
wrote:

1. Figure out the name of your constraint:

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_NAME = 'Customer'

ALTER TABLE Customer DROP CONSTRAINT <name of your PK goes here>

Note: it is a good practice to explicitly name your constraints in
your DDL.

-----------------------------------------------Reply-----------------------------------------------

On Jun 5, 6:47 pm, CharlesA <Charl@discussions.microsoft.com>
wrote:

Since you have not provided constraint name SQL Serve will
automatically generate constraint name.
you can get it using

sp_helpconstraint <tablename>
or
select * from sys.key_constratints where object_id = object_id(<table
name>)

then run
alter table <tablename>
drop constratint <constraint name>

-----------------------------------------------Reply-----------------------------------------------

thanks very much folks, but how do you name your PK constraint

can you name all constraints?

-----------------------------------------------Reply-----------------------------------------------

Try:

CREATE TABLE Customer
(CustId INT NOT NULL IDENTITY CONSTRAINT PK_Customer PRIMARY KEY,
 Last VARCHAR(100) NOT NULL,
 First VARCHAR(100) NOT NULL)

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"CharlesA" <Charl@discussions.microsoft.com> wrote in message

news:7CCF6809-2CB0-4939-B867-BEDE91D6FED2@microsoft.com...
thanks very much folks, but how do you name your PK constraint

can you name all constraints?

-----------------------------------------------Reply-----------------------------------------------

When creating a table:

CREATE TABLE dbo.table_name
(
    column_name INT,
    CONSTRAINT constraint_name PRIMARY KEY(column_name)
);
http://msdn2.microsoft.com/en-us/library/ms174979.aspx

For an existing table:

ALTER TABLE dbo.table_name ADD CONSTRAINT constraint_name PRIMARY KEY
(column_name);
http://msdn2.microsoft.com/en-us/library/ms190273.aspx

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"CharlesA" <Charl@discussions.microsoft.com> wrote in message

news:7CCF6809-2CB0-4939-B867-BEDE91D6FED2@microsoft.com...

ok all,
many thx for a top notch service and response time
regards,
CharlesA
Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc