PK and Unique on 2 columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

PK and Unique on 2 columns

Author

Topic

kprasadreddy
Starting Member

41 Posts

Posted - 2006-11-15 : 17:49:56

I have 3 columns Col_1,col_2,col_3 and I have to create a table with script that satisfies

col_1 and col_2 should be PK (together)

col_1 and col_3 should be unique (together)

Let me know the T-SQL script.

thanks,
prasad

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 17:58:33

[code]CREATE TABLE dbo.Table1
(
col1 int NOT NULL,
col2 int NOT NULL,
col3 int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
col1,
col2
)

GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
IX_Table1 UNIQUE NONCLUSTERED
(
col1,
col3
)[/code]

kprasadreddy
Starting Member

41 Posts

Posted - 2006-11-15 : 18:02:30

Why did you choose Clustered for PK and NONClustered for UNQ keys?Is there a particular reason?How do I choose Clustered and NONClustered indexes?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 18:14:26

I didn't choose them, I just used the defaults because you didn't give enough info to warrant using non defaults. Search this site and Books Online for clustered vs non-clustered, they are very different, so you should understand both properly before you decide.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources