Quick Help UNIQUE Constraint vs. PRIMARY Key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Quick Help UNIQUE Constraint vs. PRIMARY Key

Author

Topic

tudorH
Starting Member

13 Posts

Posted - 2012-07-12 : 14:21:57

Hi!

I need some quick help on a decision I have to make and I don't have much experience with SQL. Basically I need to create a table CustomerSatisfaction where a record is identified by year, quarter and productID...now my question is, do I make one of those values a PRIMARY KEY and two UNIQUE or just make all there into a composite PRIMARY KEY, what is recommended? (the product ID is a foreign key from a 'Product' table)

Should I do this:
CREATE TABLE CustomerSatisfaction (
year integer NOT NULL,
quarter nvarchar(50) NOT NULL,
productID integer references Product(productID) NOT NULL,
PRIMARY KEY(year, quarter, productID),
value float NOT NULL
);

Or this:
CREATE TABLE CustomerSatisfaction (
year integer NOT NULL,
quarter nvarchar(50) NOT NULL,
productID integer references Product(productID) NOT NULL,
value float NOT NULL,
UNIQUE (year,quarter,productID)
);

Also since I'm already asking, what would be the best data type for the year column?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-12 : 14:34:22

Either constraint will work since all the columns are declared NOT NULL.

Smallint will be fine if all you need is the year. Quarter absolutely does NOT need to be nvarchar(50), this could be tinyint.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 14:36:01

Conceptual PK and Unique are basically the same. The real difference is in the defaults; PKs are by default clusterd, etc.. If those three column truely identify a unique row, then it probably makes sense to put a composite PK. You might also consider the order of the PK.. For example if you think more queries will try to access the ProductID more than the Year, then you might want to moce ProductId to first column in the constraint.

tudorH
Starting Member

13 Posts

Posted - 2012-07-12 : 15:13:48

Thanks guys! I am using the composite PRIMARY KEY method. One other question...

Say I do something like this:
CREATE TABLE ProductTrans (
productID int references Product(productID) NOT NULL,
sprintID int references Sprint(sprintID) NOT NULL,

points float NOT NULL,
incident int NOT NULL,
devHours float NOT NULL,

PRIMARY KEY(productID)
);

and then I want to reference that table how would I do it? I tried this:
CREATE TABLE CustomerValue (
serviceTypeID int references ServiceType(serviceTypeID) NOT NULL,
productID int references ProductTrans(productID) NOT NULL,
sprintID int references ProductTrans(sprintID) NOT NULL,

billedHours float NOT NULL,
sales varchar(5) NOT NULL,

PRIMARY KEY(sprintID, productID, serviceTypeID)
);

Basically I need both the productID and sprintID (and the serviceTypeID) to identify a record under the CustomerValue table. How can I do this? Would this be somewhere that I need to use UNIQUE?

EDIT: To add a bit more info, the ProductTrans table is a table which is referenced by about 5 other tables and it brings together the Product and Sprint tables.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-12 : 15:35:00

you can either create a constraint that references to a column in the first table at the creation level of the table

this is just an example:
create table mytable
(
col1 int not null,
col2 int not null,
CONSTRAINT col2_FK
FOREIGN KEY (col2)
REFERENCES firsttable(column_name of the first table)
)

or you can add it after tyou create a second table.

alter table mytable
add constraint ...etc

note make sure to reference to a column that has the same data type.

--------------------------
Get rich or die trying
--------------------------

tudorH
Starting Member

13 Posts

Posted - 2012-07-12 : 16:25:44

quote:
Originally posted by xhostx

you can either create a constraint that references to a column in the first table at the creation level of the table

this is just an example:
create table mytable
(
col1 int not null,
col2 int not null,
CONSTRAINT col2_FK
FOREIGN KEY (col2)
REFERENCES firsttable(column_name of the first table)
)

or you can add it after tyou create a second table.

alter table mytable
add constraint ...etc

note make sure to reference to a column that has the same data type.

--------------------------
Get rich or die trying
--------------------------

Alright so I tried doing something like this with my table:

CREATE TABLE CustomerValue (
serviceTypeID int references ServiceType(serviceTypeID) NOT NULL,
productID int NOT NULL,
sprintID int NOT NULL,

billedHours float NOT NULL,
sales varchar(5) NOT NULL,

CONSTRAINT productID_FK FOREIGN KEY (productID) REFERENCES ProductTrans(productID),
CONSTRAINT sprintID_FK FOREIGN KEY (sprintID) REFERENCES ProductTrans(sprintID),

PRIMARY KEY(sprintID, productID, serviceTypeID)
);

But now I get an error "There are no primary or candidate keys in the referenced table 'ProductTrans' that match the referencing column list in the foreign key 'productID_FK'."

How can there be no primary keys in ProductTrans? I can see them in SQL Server Management Studio and the code I posted in the previous post definitely has them included. Am I missing something? or is this because its a table referencing a table which is referencing another table?

Thanks again!

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-12 : 16:35:01

According to your error, your primary key in the ProductTrans or some other columns aren't properly created or misspelled, this is a common problem

this is what I would suggest.

Since the order is matter here. try and delete all the tables.

Create the ProductTrans with Primary is succeeded, you can proceed. make sure that the table has PRIMARY KEY AFTER YOU CREATE IT.

then go ahead and create the second one referencing your key to the first table

--------------------------
Get rich or die trying
--------------------------

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-12 : 16:46:01

quote:
Since the order is matter here. try and delete all the tables.

Ummm, what? There's no need to delete (I think you mean drop) any tables.

tudorH
Starting Member

13 Posts

Posted - 2012-07-12 : 16:53:45

quote:
Originally posted by xhostx

According to your error, your primary key in the ProductTrans or some other columns aren't properly created or misspelled, this is a common problem

this is what I would suggest.

Since the order is matter here. try and delete all the tables.

Create the ProductTrans with Primary is succeeded, you can proceed. make sure that the table has PRIMARY KEY AFTER YOU CREATE IT.

then go ahead and create the second one referencing your key to the first table

--------------------------
Get rich or die trying
--------------------------

Alright well I pretty much just recreated all my tables, and the Primary Keys are in the ProductTrans table but I still can't use them as Foreign Keys. Take a look at the screenshot. Any other ideas?

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-12 : 18:28:01

try to create this table without constraint first then add them later.

--------------------------
Get rich or die trying
--------------------------

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-13 : 00:00:29

quote:
Originally posted by Lamprey

Conceptual PK and Unique are basically the same.

I know they have similar properties but I kind of disagree with the semantics of that statement. For me, a PK is the "what this thing is" and a unique constraint is simply something to check data.
For example, I would expect my employee ID to be a PK (and therefore unique) because it identifies me. However there are other things that should be unique that don't really identify me. Weak examples would be my email address, desk location or phone number.
A small logical difference perhaps, but examples of where you can have multiple unique constraints without them all being PKs.

tudorH
Starting Member

13 Posts

Posted - 2012-07-13 : 11:59:27

quote:
Originally posted by xhostx

try to create this table without constraint first then add them later.

--------------------------
Get rich or die trying
--------------------------

hmmm tried doing that as well but still getting the error
"There are no primary or candidate keys in the referenced table 'ProductTrans' that match the referencing column list in the foreign key 'FK__CustomerV__produ__19DFD96B'."

Is it not a good design to reference a key that is referenced in the table? Am I supposed to be doing this differently?

EDIT: this is the ALTER TABLE code I used.
ALTER TABLE CustomerValue ADD FOREIGN KEY (productID) REFERENCES ProductTrans(productID)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-13 : 14:47:38

quote:
Originally posted by LoztInSpace

quote:
Originally posted by Lamprey

Conceptual PK and Unique are basically the same.

I know they have similar properties but I kind of disagree with the semantics of that statement. For me, a PK is the "what this thing is" and a unique constraint is simply something to check data.
For example, I would expect my employee ID to be a PK (and therefore unique) because it identifies me. However there are other things that should be unique that don't really identify me. Weak examples would be my email address, desk location or phone number.
A small logical difference perhaps, but examples of where you can have multiple unique constraints without them all being PKs.

I hear what you are saying and I don't disagree. The only real difference is that SQL Server only lets you have one PK per table. So, from that perspective I can see how that'd be a "what this thing is." But, if you have multiple unique constraints, each of those "things" is what it is too. So, to me there really is no difference. I, like you, prefer to differentiate by using PKs. But, I've seen some rather large databases that didn't use PKs at all. All the "PKs" where just unique constraints.

I’m not sure if it just semantics, as I’ve seen people that think a Unique Index is different than a Unique Constraint. One is a relational concept (Constraint) and one is a manifestation of the implementation (Index). But, they are the same thing.

As an example, the following PK and Constraint declarations behave exactly the same. I’m not arguing either way, just showing an example.. tomato.. tomato.. :)

CREATE TABLE Test (ID INT NOT NULL)

ALTER TABLE TEST ADD CONSTRAINT PK_Test PRIMARY KEY (ID)

ALTER TABLE TEST ADD CONSTRAINT PK_Test UNIQUE CLUSTERED (ID)

tudorH
Starting Member

13 Posts

Posted - 2012-07-13 : 14:58:30

Finally fixed it! had to do it like this to create the constraint:
CONSTRAINT productID_FK FOREIGN KEY (productID, sprintID) REFERENCES ProductTrans(productID, sprintID)

Thank you all for the great info and help! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources