Composite primary key

Question

Composite primary key

patla4u

SSCrazy

Points: 2103
More actions
June 1, 2011 at 1:34 pm

#241362

Hello,
I have 2 tables.
table :1
I have 3 columns and they are composite primary key.
customer_id,
customer_code
customer_br
Table 2
I have 2 columns and they are referred to table 1.
customer_id
customer_code
(I don't have customer_br column)
How can i join table 2 to table 1 ???
Thanks
L

Viewing 14 posts - 1 through 13 (of 13 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

you can join the two tables on two of the three criteria, but you can potentially get duplicates due to the third column

unless you assume/infer a specific customer_br?

...

LEFT OUTER JOIN Table2 T2

ON T1 customer_id = T2.customer_id

AND t1.customer_code = T2.customer_code

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

patla4u SSCrazy Points: 2103 More actions · Answer 2

I am sorry ,,,,but I want to create relationship with two tables.

Means composite primary key to composite foreign key ????(it's not working)

SO how can i create relationship with table 2 to table 1???

Thanks

L

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 3

You can create two different foreign keys but that won't require the two columns are on the same row. You could also create a trigger to enforce it or a scheduled job to report on rows that don't comply.

Lowell SSC Guru Points: 323482 More actions · Answer 4

patla4u (6/1/2011)
I am sorry ,,,,but I want to create relationship with two tables.
Means composite primary key to composite foreign key ????(it's not working)
SO how can i create relationship with table 2 to table 1???
Thanks
L

what's not working? are you getting a syntax issue, or are you not gettign the desired results?

help us help you...rememrber we can't look over your shoulder, just test whatever you paste here...

show me the query you tried that didn't work

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

patla4u SSCrazy Points: 2103 More actions · Answer 5

Hello,

I am desinging database. and Database design

I have 2 tables.

table :1

I have 3 columns and they are composite primary key.

customer_id,

customer_code

customer_br

Table 2

I have 2 columns and they are referred to table 1.

customer_id

customer_code

(I don't have customer_br column)

Table 1 is making composite primary key. and table 2 referring to table 1.

and I want to create relationship with two tables.....(like primary key and foreign key relation).

So how can i relate table 2 to table 1(like primary key and foreign key relation)??

Thanks

L

Lowell SSC Guru Points: 323482 More actions · Answer 6

don't just re-paste the same question...that doesn't help at all.

two tables can be JOINed via a SQL statement, and that join is not restricted at all...it can join on parts of that relationship you identified. that was the example i posted before.

But a foreign key must be based on a PRIMARY or UNIQUE constraint.

so you can join the two tables in a query or view, but you cannot create a foreign key constraint between them, unless you add all three primary cosntraint columns to your "Table2"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

patla4u SSCrazy Points: 2103 More actions · Answer 7

Thanks

i don't want to join tables but I want to create relation.

Is there any other way to create relation(foreing key constrain) like junction tables????

Thanks

L

patla4u SSCrazy Points: 2103 More actions · Answer 8

this is the error msg

The number of columns in the Primary key doesn't match with the Columns of the Foriegn key.

Please let me know...

Thanks

L

Lowell SSC Guru Points: 323482 More actions · Answer 9

patla4u (6/1/2011)
Thanks
i don't want to join tables but I want to create relation.
Is there any other way to create relation(foreing key constrain) like junction tables????
Thanks
L

nope. a foreign key must be on a PK or UQ.

you could create a CHECK constraint, but from what you are describing so far, there's no real constraint...just a partial relationship.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

patla4u SSCrazy Points: 2103 More actions · Answer 10

can you explian me how to create check constrint??

Thanks

L

Lowell SSC Guru Points: 323482 More actions · Answer 11

wouldn't do any good.

you need to help us help you.

go back to the beginning...we know table1 has a three column PK.

another table has two out of three columns of that same PK.

do you know what the difference is between a relationship(JOIN) and an enforced foreign key?

why are you sure you need a Foreign key?

show us a concrete SELECT ColumnList from SomeTable statement that typifies the issue you are trying to demonstrate.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

patla4u SSCrazy Points: 2103 More actions · Answer 12

Table:1 CS(customer)

CS_customer_id,

CS_customer_code

CS_customer_br

Table 2 CD(costomer desciption)

CD_customer_id

CD_customer_code

Lowell SSC Guru Points: 323482 More actions · Answer 13

patla4u (6/1/2011)
Table:1 CS(customer)
CS_customer_id,
CS_customer_code
CS_customer_br
Table 2 CD(costomer desciption)
CD_customer_id
CD_customer_code

no explanation whatsoever? another repeat of a schema?

if you cannot explain what you want, we cannot explain how to do it. please see my previous post, and answer the questions there.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!