Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
CoastalTom
Starting Member
4 Posts |
Posted - 2013-01-27 : 18:09:26
|
-- ADD a Primary KeyALTER TABLE CustNewADD PRIMARY KEY (Cust_id)--Select the name of the Primary Key fieldSELECT nameFROM sys.key_constraintsWHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'CustNew';GO-- Delete the primary key constraint.-- Here I don't know how to pass the output from the previous SELECT StatementALTER TABLE CustNewDROP CONSTRAINT PK__CustNew__A1B71F9000200768 -- this is the PK reference for the Cust_id Column (with 16 characters?)--DROP CONSTRAINT PK_CustNew_Cust_id; --I want to pass the output from the SELECT statement into this CONSTRAINT ParameterGO |
|
CoastalTom
Starting Member
4 Posts |
Posted - 2013-01-27 : 18:15:41
|
I wasn't sure how to add a header explanation to this topic, (my first posting to a forum). I can ADD a PRIMARY KEY to a Column in the CustNew Table.I can identify the Column name from the SELECT statement, although it has a 16 character suffix I am unaware of...I CANNOT pass this full Column name as a CONSTRAINT parameter...Thanks in advance to anyone who can assist me. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 18:54:29
|
You can use dynamic SQL to do this - not sure if there is a non-dynamic wayDECLARE @sql NVARCHAR(4000);SELECT @sql = 'alter table ' + QUOTENAME(N'CustNew') + ' drop constraint ' + QUOTENAME(name)FROM sys.key_constraintsWHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'CustNew';exec sp_executesql @sql; |
|
|
CoastalTom
Starting Member
4 Posts |
Posted - 2013-01-27 : 19:05:41
|
Thank you James...is works a treat!!Much appreciated!!As you can see my SQL repertoire is quite limited, but can understand how it executes.Without any more time being taken on your part, why the 16 character reference to the suffix of the Cust_id Column and just not a single reference to that field?Not a big one, didn't realise T_SQL was so particular.It looked easy at the beginning, lol...Cheers,Tom |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 19:24:44
|
SQL Server is adding a GUID to the end of the name to make sure that automatically generated constraint names would be unique. SQL Server requires that the names be unique in a schema, so if you had two tables with the same column name, and if you wanted to add constraints to each of those columns, if it simply used the column name without the GUID, there will be a name collision. See the example below - here I am explicitly providing a constraint name:CREATE TABLE dbo.CustNew(Cust_id INT NOT NULL);ALTER TABLE dbo.CustNew ADD CONSTRAINT PK_Cust_id PRIMARY KEY (Cust_id)CREATE TABLE dbo.CustNew2(Cust_id INT NOT NULL);-- next statement will cause an error because I am reusing the name PK_Cust_idALTER TABLE dbo.CustNew2 ADD CONSTRAINT PK_Cust_id PRIMARY KEY (Cust_id) |
|
|
CoastalTom
Starting Member
4 Posts |
Posted - 2013-01-27 : 19:32:36
|
Once again, thank you James for the time taken to educate me re:basic SQL syntax. I now understand the necessity for the GUID ensuring that you can address the correct Key Column.Above and beyond the call of duty...All the best from down-under! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 19:47:20
|
You are very welcome Tom - glad to be of help. "Down under" has been very much in focus (for me at least) as I was watching the struggle between Djokovic and Murray this morning. |
|
|
|
|
|
|
|