Table Datatype with primary key
-
What is the syntax to add a primary key based on both fields to the below.
DECLARE @test-2 TABLE(
[Test_WK] [int],
[Test_CODE] [varchar] (4)
)
Can't seem to get it right.
-
Hi Stefan,
Are you creating the table from scratch?
If so this should work just fine.
CREATE TABLE TableName ([PrimaryKey] [int] IDENTITY (1, 1) NOT NULL, [ColumnName] [int] NOT NULL,
[ColumnName] [varchar(4)] NOT NULL) ON [PRIMARY]
ALTER TABLE TableName WITH NOCHECK ADD CONSTRAINT
[PrimaryKey_TableName] PRIMARY KEY CLUSTERED
([PrimaryKey]) ON [PRIMARY]
If the table already exists, just use the second bit.
Why the @test-2? You doing this dynamically?
Have fun
Steve
We need men who can dream of things that never were.
-
Doesn't this work?
DECLARE @test-2 TABLE(
[Test_WK] [int] PRIMARY KEY,
[Test_CODE] [varchar] (4)
)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Oops, forget it.
Reread it that you want it on both columns. Sorry.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
That must be it
DECLARE @test-2 TABLE(
[Test_WK] [int],
[Test_CODE] [varchar] (4)
PRIMARY KEY (Test_WK, Test_CODE)
)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Double Oops,
I missed that as well, must be a Friday thang.....
Revised answer:-
ALTER TABLE [TableName] WITH NOCHECK ADD
CONSTRAINT [PK_tableName] PRIMARY KEY CLUSTERED
(
[ColumnName1],
[ColumnName2]
 
ON [PRIMARY]Am I being daft or is it as easy to create the table in Enterprise Manager, script it - then have a look at the script to see how Sequel Server does it?
Have fun
Steve
We need men who can dream of things that never were.
-
Thanks Frank, that's it.
-
Steve, agree that scripting in Enterprise Manager or QA best way to learn how to create tables. But in this case I am using a table variable.
-
Stefan,
Yep - only picked it up after the posting. I was right, it's a Friday thang....
Have a good weekend all
Steve
We need men who can dream of things that never were.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply