PRIMARY KEYs vs. UNIQUE Constraints

Question

In an Alexander Kuznetsov article, he presents the follow code snippet:

CREATE TABLE dbo.Vehicles(
    ID INT NOT NULL, 
    [Type] VARCHAR(5) NOT NULL,
    CONSTRAINT Vehicles_PK PRIMARY KEY(ID),
    CONSTRAINT Vehicles_UNQ_ID_Type UNIQUE(ID, [Type]),
    CONSTRAINT Vehicles_CHK_ValidTypes CHECK([Type] IN ('Car', 'Truck'))
);

This snippet raises a few questions for me.

1. Why is it necessary to include both ID and Type in the unique constraint? If just ID is unique, then the combination of the two columns will always be unique as well.

2. Also, I know how to set a primary key and specify if it unique in SSMS. But how would I specify a primary key on one column, and make a unique constraint on a combination of columns? Does this create two indexes?

This came up because I'm trying to implement similar code, which does not create a composite primary key, and I get the following error. So I'm trying to understand this code better.

The columns in table 'MyTable' do not match an existing primary key or UNIQUE constraint.

---

EDIT

I was able to get this working by simply creating a composite primary key in MyTable. The actual table definition is shown below. Again, this works. But it is not the same as the code quoted above. And I'm not sure if it would be better if I did it the other way.

CREATE TABLE [dbo].[MessageThread](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [MessageThreadType] [int] NOT NULL,
    CONSTRAINT [PK_MessageThread_1] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC,
        [MessageThreadType] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MessageThread]  WITH CHECK ADD  CONSTRAINT [CK_MessageThread_ValidType] CHECK  (([MessageThreadType]=(2) OR [MessageThreadType]=(1)))
GO

ALTER TABLE [dbo].[MessageThread] CHECK CONSTRAINT [CK_MessageThread_ValidType]
GO

Answer 1

1 : I am not sure of the specific purpose of the given schema. But note that a unique constraint can be applied for multiple reasons, most commonly: (a) to enforce uniqueness and (b) to provide the optimizer with more information to base decisions.

2 : A unique constraint does not create two indexes. It creates a single index with one of the columns as the leading key column. It enforces uniqueness on both. So a unique constraint on a,b could have:

a    b    
---- ----
1    1
1    2
2    1
2    2

Notice that neither of the columns enforce uniqueness individually. I am not a big fan of using the table designer in SSMS (it has tons of bugs and doesn't support all functionality) but here is how to do it:

a) right-click the grid and choose Indexes/Keys...

b) choose multiple columns using the [...] button in the Columns grid

c) change Type to Unique Key

d) change the Name if desired

Here's an example of a table that already has a primary key. I could add one or more unique indexes if I wanted to:

Answer 2

In my understanding, the reason for unique constraint on ID,[Type] is let detail tables to refer ID,[Type] as foreign key. Usually parent table is required to have unique constraint on columns used for foreign key. For instance, the table in the question can have 2 detail tables:

CREATE TABLE dbo.CARS( 
....
vehicle_id INT NOT NULL,
[Type] VARCHAR(5) NOT NULL,
CONSTRAINT CAR_CHK_TYPE CHECK [Type]='Car',
CONSTRAINT CAR_FK_VEHICLE FOREIGN KEY (vehicle_id,[Type]) REFERENCES Vehincle(id,[Type]));

CREATE TABLE dbo.TRUCKS( 
....
vehicle_id INT NOT NULL,
[Type] VARCHAR(5) NOT NULL,
CONSTRAINT CAR_CHK_TYPE CHECK [Type]='Truck',
CONSTRAINT CAR_FK_VEHICLE FOREIGN KEY (vehicle_id,[Type]) REFERENCES Vehincle(id,[Type]));

This way Cars will have details only about Car type, whereas TRUCKS only about Truck.

Such design is used to avoid polymorphic relationship, for instance

CREATE TABLE dbo.VEHICLE (
...,
ref_id INT NOT NULL,
-- PK of 'master' table
ref_name VARCHAR(20) NOT NULL, 
-- here we put 'truck' or 'car', so we virtually have 2 parents; 
-- in this case we cannot use FK constraint, the only thing that may 
-- somehow enforce the logical constraint is writing a trigger

Update

Your updated table definition looks good to me. I guess the sample table was initially designed for Oracle and then ported to SQLServer. In Oracle, that unique constraint and primary key can use the same index, so there is no penalty for having both PK and Unique constraint.