How to create composite key through SQL 2008 GUI

Prasad Godbole

15y3.4k

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Choudhary15y ago
Accepted 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.
0
Ananth prasath15y ago
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 (Column1,Column2)
GO

With Best RegardsNatarajan R (UI Designer)
0
Dinesh Beniwal15y ago
Hi Prasad,

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

Thanks
0
Prasad Godbole15y ago
Hi Amit,

Now its working fine through GUI. Not sure what was the problem.

Thanks and Regards
Prasad
0
Prasad Godbole15y ago
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 (Id,RegNo)

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
0