Clever Geek Handbook

Correct one-to-one comparison table

I want to create a table that just has two integer columns that will serve as a mapping table between two different datasets, and I want to put the correct constraints on it.

I initially set two columns as a composite primary key, but then realized that it represents many, many, and only duplicates from many to many mappings.

How can I specify that both columns are unique integers across all rows? I am using MS SQL, but I suppose this is a general question about database design.

0


source to share


3 answers


Create a primary key for 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]
+1


source


The creation of another table is done to create a many-to-many relationship, so you probably shouldn't create one (unless I'm missing something).



A one-to-one required parameter is the same as the two tables are separate tables. The only thing that is acceptable for one is optional and you just need to link these two tables, there is no need for a third table.

+2


source


Use a UNIQUE column constraint for each column.

0


source






More articles:


All Articles