giddy


Hi,

How do i create a composite primary key through sql management studio

I have two tables:
Section : SectionID , Name
Rooms: RoomID , SectionID,Cost , MaxPersons

I know its unusual but i have sections in this hotel that have rooms , a section is alsmost like a seperate hotel in itself. Only the management for various sections is common.

I need to store rooms that belong to a particular section , yes there can be Duplicated RoomIds. How do i make a compiste key in SQL mStudio , or is there a better solution






Re: creating composite primary keys via sql management studio??

Madhu K Nair


Open table in Design mode, highlight/select multiple columns , press right button ... choose set primary key option

 Better solution is to create table and Primary key by script

BOL is the best resource for such kind of dobts

i am not sure this is what u r looking for........

CREATE TABLE [dbo].Section(

SectionID [int] IDENTITY(1,1) NOT NULL,

Name [nvarchar](50) NULL

CONSTRAINT [Section_PK] PRIMARY KEY NONCLUSTERED

(

[SectionID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].Rooms(

RoomID [int] NOT NULL,

SectionID int not null,

Cost int,

MaxPersons int

CONSTRAINT [Rooms_PK] PRIMARY KEY NONCLUSTERED

(

RoomID Asc,

[SectionID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

 

 

 

Madhu







Re: creating composite primary keys via sql management studio??

giddy

"Open table in Design mode, highlight/select multiple columns , press right button ... choose set primary key option"

yes , thats what i wanted. Thanks so much , sometimes some things are so simple!

Thanks for the script too;

I have two more problems:
  1. I created a database in visual studio 05 , Cant i edit it in sql management studio When i do that , sqlmstudio says "editor not avialible for <> Make sure the .mdf file is installed!"
  2. Also , when i did create a new sql db in VS [Add|New Item|Sql DB] .. i get xsc , xss ,and xsd files and a class with <dbname>DataSet created! What are they
My database name is HTLDATA , this is what it looks like:
http://gidsfiles.googlepages.com/sql_dataset.jpg








Re: creating composite primary keys via sql management studio??

Madhu K Nair

i am not sure what do  u mean by EDIT database.... In database terms it is Alter.. yes u can Alter Database...

 

Madhu






Re: creating composite primary keys via sql management studio??

giddy

hi ,

I rephrased my question.

By edit i mean , once i've created the database in VS , i want to add tables etc .. in SQL management studio. But i tried and i get an error.And what about (2) my second question


Thanks





Re: creating composite primary keys via sql management studio??

Madhu K Nair

can u post the error u gets... what u must do just look at BOL (Books Online ) for Create Table syntax... u should do it throgh script...

regarding the Q 2-- I am not good at VS... i am not able to simulate the situation... sorry

Madhu






Re: creating composite primary keys via sql management studio??

giddy

hi,

This is what it looks like when i open a database i made in vs:
http://gidsfiles.googlepages.com/sql_mstd_error.jpg

My second question is not a problem , or error. I'm just curious about what those auto generated files are , when i create a new database in visual studio [Add|New Item|Sql Db] :
http://gidsfiles.googlepages.com/sql_db_files.jpg
http://gidsfiles.googlepages.com/sql_dataset.jpg

Thanks