[2005] unique columns

[jlbantang]

create table mytable
(
recordId int identity(1,1) primary key,
col2 int,
col3 int,
col4 int
)

basically i created the primary key recordID which will be unique in value. now how will i be able to set col2 and col3 have unique values too.

[kevchadders]

unique in what way... composite key, clustered index on all 3 fields, all counting up in increments of 1? etc...

for example, with the primary key recordID being unique, you will have records starting with:

1...
2...
3... etc

as the other fields are INT, what would you expect them to count up the same way?

e.g.

1,1,1
2,2,2
3,3,3... etc

Could you give me a quick example of how the table would look.

ps. here is an example of the first field being an IDENTITY field, with the two other fields being part of the same primary key making up a composite key.

CREATE TABLE [dbo].[Table_1]
(
	[recordId] [int] IDENTITY(1,1) NOT NULL,
	[col2] [int] NOT NULL,
	[col3] [int] NOT NULL,
	[col4] [int] NULL,
	
	CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
	(
		[recordId] ASC,
		[col2] ASC,
		[col3] ASC
	) ON [PRIMARY]

) ON [PRIMARY]

[jlbantang]

hi, basically the col2 and col3 will accept input values from user. i can be a random int nos but again it must be unique.

[kevchadders]

Ok.

I have created a quick table, with a identity field on the first row, and a UNIQUE INDEX on col2 and col3.

Run this code in the query window and test to see if that's what your after

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Table_1]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Table_1](
	[col1] [int] IDENTITY(1,1) NOT NULL,
	[col2] [int] NULL,
	[col3] [int] NULL
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Table_1]') AND name = N'col2')
CREATE UNIQUE NONCLUSTERED INDEX [col2] ON [dbo].[Table_1] 
(
	[col2] ASC
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Table_1]') AND name = N'col3')
CREATE UNIQUE NONCLUSTERED INDEX [col3] ON [dbo].[Table_1] 
(
	[col3] ASC
) ON [PRIMARY]

[jlbantang]

perfect! i never expect the tsql be very long... is there any way around to make this shorter