SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Pass row from SELECT statement as CONSTRAINT param
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CoastalTom
Starting Member

Australia
4 Posts

Posted - 01/27/2013 :  18:09:26  Show Profile  Reply with Quote
-- ADD a Primary Key
ALTER TABLE CustNew
ADD PRIMARY KEY (Cust_id)

--Select the name of the Primary Key field
SELECT name
FROM sys.key_constraints
WHERE 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 Statement
ALTER TABLE CustNew
DROP 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 Parameter
GO

CoastalTom
Starting Member

Australia
4 Posts

Posted - 01/27/2013 :  18:15:41  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 01/27/2013 :  18:54:29  Show Profile  Reply with Quote
You can use dynamic SQL to do this - not sure if there is a non-dynamic way
DECLARE @sql NVARCHAR(4000);
SELECT @sql = 'alter table ' + QUOTENAME(N'CustNew') + ' drop constraint ' +
	QUOTENAME(name)
FROM 
	sys.key_constraints
WHERE 
	type = 'PK' AND OBJECT_NAME(parent_object_id) = N'CustNew';
exec sp_executesql @sql;

Go to Top of Page

CoastalTom
Starting Member

Australia
4 Posts

Posted - 01/27/2013 :  19:05:41  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 01/27/2013 :  19:24:44  Show Profile  Reply with Quote
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_id
ALTER TABLE dbo.CustNew2 ADD CONSTRAINT PK_Cust_id PRIMARY KEY (Cust_id)
Go to Top of Page

CoastalTom
Starting Member

Australia
4 Posts

Posted - 01/27/2013 :  19:32:36  Show Profile  Reply with Quote
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!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 01/27/2013 :  19:47:20  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000