How to create composite key through SQL 2008 GUI

Question

Hi  We have a table and want to add composite key through SQL 2008 GUI The table is T_UserDetails and it conatins the following fields Field Data Type Id int RegNo varchar(100) UserFName varchar(50) UserLName varchar(50) We want that using Id and RegNo field the record should be uniquely identify. We have tried to set primary key to both columns by selecting in design mode but its disabling the primary key option. Also we have tried to do this through Manage Indexes and Keys but its not showing column names. Through SQL create table script we can set this but its not happening through SQL 2008 GUI Please suggest steps to do through GUI. Many Thanks Prasad

Amit Choudhary · Answer

Hi Prasad Are you able to create single column primary key in design mode? Have you tried altering table to apply the composite primary key constraint? if yes then what error are you getting. I guess this might be a db access permission issue. Thanks.

Ananth prasath · Answer

Hi Try this -- Sample Table create table myTable ( Column1 int not null  Column2 int not null ) GO -- Add Constraint ALTER TABLE myTable ADD CONSTRAINT pk_myConstraint PRIMARY KEY (Column1Column2) GO With Best Regards Natarajan R (UI Designer)

Dinesh Beniwal · Answer

Hi Prasad If Amit's reply will help you then Mark as accepted answer. Thanks

Prasad Godbole · Answer

Hi Amit Now its working fine through GUI. Not sure what was the problem. Thanks and Regards Prasad

Prasad Godbole · Answer

Hi Amit Yes I am able to create single primary key in design mode. Also following 2 scripts can create composite key without any error. Script 1: create table T_UserDetails ( Id int not null RegNo varchar(100) not null primary key (Id RegNo) ) Script 2: ALTER TABLE T_UserDetails ADD PRIMARY KEY (IdRegNo) I am trying to add the composite key through GUI but its not happening. The problems we are facing are 1) If I am trying to set primary key to more than 1 field by selecting it and then on right click primary key option is appearing disable. 2) When I am trying to add column through Manage Indexes and Keys option I am not getting all column names. I am only getting 1st column name. Many Thanks Prasad