A Proper One-to-One Mapping Table

Question

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.

Answer 1

Use a UNIQUE column constraint on each column.

Answer 2

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]

Answer 3

Creating another table is done to create a many to many relationship, so you probably shouldn't have created it (unless I am missing something).

One to one mandatory is the same as having the two tables as a single table. The only acceptable one to one is optional, and you simply have to relate the two tables, no third table necessary.