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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Pass row from SELECT statement as CONSTRAINT param

Author  Topic 

CoastalTom
Starting Member

4 Posts

Posted - 2013-01-27 : 18:09:26
-- 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

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.
Go to Top of Page

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

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
Go to Top of Page

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_id
ALTER TABLE dbo.CustNew2 ADD CONSTRAINT PK_Cust_id PRIMARY KEY (Cust_id)
Go to Top of Page

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!
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -