hat's the tsql code for creating primary key out of the 2 columns

Hi, I'm altering an existing table to a manyTomany table.  
1.  Does manyTomany need Pk?
2.  What's the tsql code for creating primary key out of the 2 columns, personID, Position_ID?
Thanks.  I know I have to drop the current primary key first, PersonIs_ID
Below is the table definition.  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[personis](
	[PersonIs_ID] [int] IDENTITY(4221,1) NOT NULL,
	[personID] [int] NOT NULL DEFAULT ((0)),
	[Position_ID] [int] NOT NULL DEFAULT ((0)),
	[Position_Title] [nvarchar](max) NULL,
	[PrimarySecondary] [nvarchar](20) NULL DEFAULT (N'Primary'),
	[Position_Bio] [nvarchar](max) NULL,
	[Position_Narrative] [nvarchar](max) NULL,
	[Position_Narrative_short] [nvarchar](200) NULL DEFAULT (NULL),
	[web_visible] [smallint] NULL DEFAULT ((1)),
	[picture] [nvarchar](100) NULL DEFAULT (NULL),
 CONSTRAINT [PK_personis_PersonIs_ID] PRIMARY KEY CLUSTERED 
(
	[PersonIs_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[personis]  WITH CHECK ADD  CONSTRAINT [FK_personis_person] FOREIGN KEY([personID])
REFERENCES [dbo].[person] ([personID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[personis] CHECK CONSTRAINT [FK_personis_person]

Open in new window

lapuccaAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
I already have it in there...but, a separate statement would be:

alter table personis
add CONSTRAINT [PK_personis_PersonIs_ID] PRIMARY KEY CLUSTERED
(
      [PersonIs_ID] ASC, Position_ID ASC
)

0
 
chapmandewCommented:
CREATE TABLE [dbo].[personis](
      [PersonIs_ID] [int] IDENTITY(4221,1) NOT NULL,
      [personID] [int] NOT NULL DEFAULT ((0)),
      [Position_ID] [int] NOT NULL DEFAULT ((0)),
      [Position_Title] [nvarchar](max) NULL,
      [PrimarySecondary] [nvarchar](20) NULL DEFAULT (N'Primary'),
      [Position_Bio] [nvarchar](max) NULL,
      [Position_Narrative] [nvarchar](max) NULL,
      [Position_Narrative_short] [nvarchar](200) NULL DEFAULT (NULL),
      [web_visible] [smallint] NULL DEFAULT ((1)),
      [picture] [nvarchar](100) NULL DEFAULT (NULL),
 CONSTRAINT [PK_personis_PersonIs_ID] PRIMARY KEY CLUSTERED
(
      [PersonIs_ID] ASC, Position_ID ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[personis]  WITH CHECK ADD  CONSTRAINT [FK_personis_person] FOREIGN KEY([personID])
REFERENCES [dbo].[person] ([personID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[personis] CHECK CONSTRAINT [FK_personis_person]
0
 
lapuccaAuthor Commented:
The create table code is just to show you the table information.  I'm looking for tsql code to create a PK using those 2 columns. Thank you.
0
 
lapuccaAuthor Commented:
Thank you.
0
All Courses

From novice to tech pro — start learning today.