Referencing a composite foreign key

I can't find any examples on how to do the below. Can anyone advise?
I have an 'Address' table which has a primary key constructed from
'postcode' and 'house no'.
I have numerous other tables that reference this table with a foreign
key - how do I reference a composite foreign key such as this - It
isn't a matter of just entering an integer as I won't know what the
composite value for a postcode and house no is.
Am I right in thinking that I need to make the 'postcode' and 'house
no' a composite UNIQUE to ensure these are unique and add an additional
integer primary key to the address entity which the other tables can
then reference?
Thanks,
DJWYou can reference a composite key via:
alter table MyTable
add constraint FK1_MyTable foreign key (postcode, houseno)
references OtherTable (postcode, houseno)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"dwj" <danielwatkinslearn@.hotmail.com> wrote in message
news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>I can't find any examples on how to do the below. Can anyone advise?
> I have an 'Address' table which has a primary key constructed from
> 'postcode' and 'house no'.
> I have numerous other tables that reference this table with a foreign
> key - how do I reference a composite foreign key such as this - It
> isn't a matter of just entering an integer as I won't know what the
> composite value for a postcode and house no is.
> Am I right in thinking that I need to make the 'postcode' and 'house
> no' a composite UNIQUE to ensure these are unique and add an additional
> integer primary key to the address entity which the other tables can
> then reference?
> Thanks,
> DJW
>|||How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>|||Precisely. As long as ('QW1 4BP', 90) exists in OtherTable, then the INSERT
will succeed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:438d4ecc$1_1@.glkas0286.greenlnk.net...
How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>