Setting up relationships correctly

Question

Post reply

Setting up relationships correctly

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Can you show me an example of two identical column names in one table? Thanks

I wasn't born stupid - I had to study.

Darren Srodzinski SSC Enthusiast Points: 115 More actions · Answer 2

Am i losing it?? I thought two column names were not possible in a table.

Am i right?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Let me correct that the columns are renamed in the second table which was not really two identical columns. Sorry about that.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Gift

Am I understanding you correctly? Are you saying that, to create a foreign key, the name of referencing and referenced columns must be identical? Is this new to SQL 2005, because as far as I know it isn't true in SQL 2000? What about self-referencing tables?

Thanks

John

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

EDIT

That is not what I am saying because we are not talking about foreign key but DRI you can have a foreign key that is not DRI but to be DRI if b references a then a must exist because they are linked. Self referencing table again another topic. The link below will explain it better than I can. The code below is from SQL Server 2000 BOL (books online) Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_92ib.asp

CREATE TABLE order_part

(order_nmbr int,

part_nmbr int

FOREIGN KEY REFERENCES part_sample(part_nmbr)

ON DELETE CASCADE,

qty_ordered int)

GO

CREATE TABLE order_part

(order_nmbr int,

part_nmbr int

FOREIGN KEY REFERENCES part_sample(part_nmbr)

ON UPDATE CASCADE,

qty_ordered int)

GO

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

I thought we were talking about foreign keys? At least that is what Darren's original question refers to. I'm afraid I don't understand how you can have a foreign key that isn't DRI. You say that a has to exist, but does that mean it must have the same name as b?

Thanks

John

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

Yes we are talking foreign keys but this foreign key is used for Cascade on delete and Cascade on update, I think you should check the BOL (books online) for the restrictions on both actions. And if the table is self referencing how will the action be a Cascade?

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

I understand the restrictions on cascading updates and deletes... I think this is what I tried to explain to Darren. Self-referencing tables aren't to do with this problem, just an example about whether referencing and referenced columns have to have the same name. I think we are indeed thinking along the same lines - but we got our wires crossed somewhere!

John