I want to make a table that simply has two integer columns to serve as a mapping table between two different data sets and I wish to put the correct constraints on it.
I initially set the two columns as a compound primary key, but then realized that represents a many to many, only keeping duplicate many to many mappings from occurring.
How do I specify I want both columns to be unique integers in all rows? I'm using MS SQL, but I suppose this is a general database design question.
Create a Primary Key on one column and another separate unique constraint or unique index, on the other.
CREATE TABLE [dbo].[test]( [x] [int] NOT NULL, [y] [int] NOT NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [x] 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 UNIQUE NONCLUSTERED INDEX [IX_test] ON [dbo].[test] ([y] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]