Two primary keys in one table.

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

Two primary keys in one table.

Author

Topic

Psyphormer
Starting Member

8 Posts

Posted - 2010-04-07 : 09:54:46

Hi i'm new to sql and i'm having trouble practising with some question from a book which i'm going through.

I've got a table which one of the attributes is the "PRIMARY KEY" and another attributes is the "PRIMARY KEY coupled with a FOREIGN key" the FOREIGN references another table.

My question how can you have a "PRIMARY KEY with a FOREIGN KEY together" is there another name for this?

Thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 10:02:22

You can specify multiple columns as a primary key. Only of these columns can also be a foreign key. Can you post the table's CREATE script?

There are 10 types of people in the world, those that understand binary, and those that don't.

Psyphormer
Starting Member

8 Posts

Posted - 2010-04-07 : 10:34:18

CREATE TABLE GameItem
{
gameCode NUMBER(5) not null references GameTitle,
copyNumber NUMBER(4) not null,
gameCondition VARCHAR(50) not null,
purchaseDate DATE not null,
preOwned VARCHAR(1) not null,
CONSTRAINT gameCode_pk PRIMARY KEY (gameCode),
CONSTRAINT copyNumber_pk PRIMARY KEY (copyNumber)

}

I'm trying to have the attribute gameCode as a primary key and also a foreign key that references an attribute within another table, and at the same time have copyNumber also a primary key.

Would i have to have a gameCode a UNIQUE key at all?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 12:34:50

Well, you can't have 2 primary keys. You can have a single primary key that is made up of more than 1 column.

Also, what version of SQL Server are you running? I'm not familiar with the NUMBER and DATE datatypes.

You could be looking for something like this:


CREATE TABLE GameItem
(
	gameCode INT not null references GameTitle(GameTitleID),
	copyNumber INT not null,
	gameCondition VARCHAR(50) not null,
	purchaseDate DATETIME not null,
	preOwned VARCHAR(1) not null,
	CONSTRAINT gameItem_pk PRIMARY KEY (gameCode, copyNumber)
)

Here, gameCode and copyNumber together form the primary key. gameCode references the GameTitleID in the GameTitle table.

There are 10 types of people in the world, those that understand binary, and those that don't.

Psyphormer
Starting Member

8 Posts

Posted - 2010-04-07 : 13:26:43

I assume thats a composite key. Is it possible to have one the attributes that form the composite key determine data within another table like:

gameItem(gameCode*^, copyNumber^, gameCondition,purchaseDate,preOwned)
|
_______|
|
gameHistory(copyNumber*^, purchase_date,employee_id,customer_id)

Where ^ is a primary key, and the * is a foreign key.

Psyphormer
Starting Member

8 Posts

Posted - 2010-04-07 : 13:46:47

Am i correct that a UNIQUE key, with a primary key can be used to check that a row of data isn't the same as another row of data when i use INSERT/UPDATE etc?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 13:47:54

If I understand you correctly, that's pretty much what the example in my last post does.

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 13:51:54

quote:
Originally posted by Psyphormer

Am i correct that a UNIQUE key, with a primary key can be used to check that a row of data isn't the same as another row of data when i use INSERT/UPDATE etc?

A UNIQUE CONSTRAINT/INDEX specifies that no 2 rows can contain the same values within the indexed columns.

There are 10 types of people in the world, those that understand binary, and those that don't.

Psyphormer
Starting Member

8 Posts

Posted - 2010-04-07 : 13:55:38

I tried tht but when i reference the copyNumber attribute with a foreign within another table it complains about copyNumber not fount. But i think i've sorted it now with using the UNIQUE key with the copyNumber attribute.

Does pairing a unique key with with one of the primary keys (composite key) make sense?

I just wanna make sure i'm on the right track.

thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 13:58:35

quote:
Originally posted by Psyphormer
Does pairing a unique key with with one of the primary keys (composite key) make sense?

I just wanna make sure i'm on the right track.

Post the DDL for the 2 tables as it is now that you've modified it.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources