[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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

0
lapucca
Asked:
lapucca
  • 2
  • 2
1 Solution
 
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
 
chapmandewCommented:
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
 
lapuccaAuthor Commented:
Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now