Composite unique key with one 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

Composite unique key with one primary key .

Author

Topic

Psyphormer
Starting Member

8 Posts

Posted - 2010-04-10 : 04:06:19

Hi I need some advice.

I'm trying to create the table below, but I want to insure that the game_code and game_no attributes don't have the same value. I've created my composite unique key but it's still allowing the two attributes to have the same value.

I don't want to create a composite primary key as my other table that references this table only has a single foreign key.

Any help please?

CREATE TABLE game
(
game_code NUMBER(4) references gameTitle,
game_no NUMBER (4),
condition VARCHAR (25),
CONSTRAINT game_uck UNIQUE (game_code,game_no),
CONSTRAINT copyno_pk PRIMARY KEY (game_no)
);

Thanks people.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-10 : 06:45:15

quote:
Originally posted by Psyphormer

Hi I need some advice.

I'm trying to create the table below, but I want to insure that the game_code and game_no attributes don't have the same value

That's not what a unique constraint/index does. Unique means that no 2 rows will have the same value for both columns. It does NOT mean that 2 columns will not have the same value within a single row.

To achieve that, you'll probably need to use a trigger, unless someone else here can think of another way. I can't. I don't think you can create a constraint that will do this. Apart from that, all I can think of is a trigger.

Have a look at triggers in BOL.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2010-04-10 : 10:22:03

[code]
constraint chk check (game_code <> game_no)
[/code]

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-10 : 10:43:36

quote:
Originally posted by LarsG

constraint chk check (game_code <> game_no)

I didn't think that would work, but apparently it does. Thanks LarsG

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Psyphormer
Starting Member

8 Posts

Posted - 2010-04-11 : 18:31:17

Hi thanks for your reply. Sorry about the previous post i was very tired when i wont that. Yea what is was is that i wanted summat like:

CUK CUK
PK
game_code game_no
1321 1232
1321 1224

And summat like
1321 1232 -- Will cause a violation. I assume this is where unique keys are useful?

If i'm not the right track please let me know.

Thanks for your help.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-11 : 18:49:21

That makes a little more sense. Yes, a unique constraint or index will prevent this kind of duplicate.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Psyphormer
Starting Member

8 Posts

Posted - 2010-04-11 : 19:55:28

Excellent. I'm glad i'm on the right tracks :-D

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources