5

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
Improve this question
10
  • I think that you need to include both columns in the unique constraint because you want to have references to this 2-column combination. DBMSs need a Unique constraint (and an index) on this combination so they can enforce the FK references (and do it fast). Commented Feb 10, 2012 at 20:23
  • @Aaron: I got that error in SSMS when I tried to create a FK to MyTable from a table I'm creating. Commented Feb 10, 2012 at 20:24
  • @ypercube: I think that's right. Although, as I pointed out, it doesn't seem like it should be needed because that combination will always be unique. Commented Feb 10, 2012 at 20:25
  • Does MyTable have a unique constraint or a primary key? On what columns? Commented Feb 10, 2012 at 20:26
  • 1
    I share your confusion. In this case it would seem appropriate to use the UNIQUE constraint (ID,type) as the primary key. The ID column would not then require an index since it is covered by the ID,Type composite index. Perhaps showing your table descriptions would lead us to a better understanding of your problem. Commented Feb 10, 2012 at 20:36

5 Answers 5

Reset to default
7

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

enter image description here

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:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks @Aaron, but I'm still struggling to incorporate this into what I'm familiar with. Can you help me understand how the steps above would be different from simply creating a composite primary key on those two columns?
For a primary key, you only have to click+select one or more columns, right-click, and say "Set Primary Key." I still suggest that learning how to do this through the UI is unimportant. Learn the DDL syntax because that changes less frequently than the UI, has far fewer bugs (0), and can be scripted and stored in source control (and therefore reproduced) much easier than your points and clicks in a UI.
I'll add another screen shot. If you already have a primary key, you can still add multiple unique indexes (but a table can only have a single primary key)
I understand your preference for using the DDL syntax. I'm not a newbie dev by any stretch but SQL is not my first language and most of my experience with it is using the UI. I'm happy to learn more DDL syntax, and I can do so by understanding how the syntax being discussed here correlates with the UI commands I'm familiar with.
BTW, I did create a composite primary key in MyTable and then successfully created the FK I've been trying to create. Perhaps the next step is to script the table and look at that.
|
7

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.

Improve this answer

2 Comments

Thanks @a1ex07. This seems to be the case even though the pair would always be unique anyway. Do you happen to know how to set this configuration using the SSMS GUI? Is it simply a composite primary key? Or is there a way to specify that the ID is the primary key, and then add another UNIQUE constraint that refers to both columns?
I think for SQLServer it's ok to have just composite primary key. As far as I remember SQLServer (at least up to 2008) doesn't let you create unique constraint without index.
4

  1. Good question. Theoretically you're right; there is no reason, a record can always be uniquely identified by its PK and the unique constraint will always be satisfied as long as this is true. However, if ID and Type have some relationship outside the bounds of the data layer (maybe this table is the data model for an Enum?), then it's unlikely that there would be two different IDs with the same Type because the uniqueness of Type is enforced elsewhere. The constraint also sets up an index that includes both ID and Type, making the table relatively efficient to be queried by that combination of columns.

  2. You set up a unique constraint using the "Manage Indexes and Keys" option. Yes, this will create an index and unique constraint for the primary key, and an index and unique constraint for the combination of PK and Type.

Improve this answer

Comments

4

I suspect the reason for having both columns in the UNIQUE constraint is related to the error message you mentioned. SQL Server (in common with other SQL DBMSs) has a limitation that a FOREIGN KEY constraint can only reference exactly the set of columns defined by a uniqueness constraint. So if a FOREIGN KEY constraint references two columns then those two columns must have a uniqueness constraint on them - even if other constraints already guarantee uniqueness. This is a pointless limitation but it is part of standard SQL.

The following example is quite similar and explains why a composite foreign key and nested uniqueness constraints can be useful.

http://consultingblogs.emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx

Improve this answer

1 Comment

Shame that SQL Server enforces the guaranteed true logical unique constraint by creating a redundant additional index though. I saw your connect item about INCLUDE d columns in index backing up Unique Constraints. That could be useful here. An index on UNIQUE(ID) INCLUDE ([Type]) could be useful for enforcing both logical constraints and backing up the FK.
4

Here you go:

  1. Cars and trucks have different attributes, so they do not belong in one table. This is why I have two tables, Cars and Trucks.

  2. Yet cars and trucks share some attributes, such as VIN (vehicle idenification number). More to the point, VIN is unique. This is why I need a table Vehicles. A vehicle cannot be both a car and a truck, so I must make sure it is not possible to enter both (VIN=123456789, Type=Car) and (VIN=123456789, Type=Truck). This is why I have a PK on VIN only.

  3. I must ensure that a vehicle cannot have corresponding rows in both Cars and Trucks tables. This is why I have Type column in Cars and Trucks, and this is why I want (VIN, Type) in child tables Cars and Trucks refer to the parent table Vehicles. The only reason why I need an additional unique constraint on (VIN, Type) is this: it is referred by FK constraints from child tables.

BTW, you could leave a comment on the blog - in that case sqlblog would send me a message. It is a coincidence that I noticed your question here; I was supposed to go skiing, only there is no snow.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.