Sql Server Programming :: ALTER/DROP/ADD syntax confusion


	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: - Hide quoted text - > 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 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: - Hide quoted text - > 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 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... - Hide quoted text - > thanks very much folks, but how do you name your PK constraint > can you name all constraints? ok all, many thx for a top notch service and response time regards, CharlesA