sql problem (query and relationship)

Question

0.00/5 (No votes)

See more:

VB

SQL

how to make relationship and query in my sql?????

Posted 6-Apr-11 20:46pm

zeroseven08

Add a Solution

Comments

ZeeroC00l 7-Apr-11 2:54am

Can you be more specific ?? Did you mean, how to create a relation between two tables ?

zeroseven08 7-Apr-11 2:57am

yes,,,i want to create a relation between two table and
at the same time want to determine the type of relation,
whether one to one, one to many or many to many..
please help me,,senior

zeroseven08 7-Apr-11 3:46am

thx u brother for ur solution,,
oya..
i want to create store procedure in my sql..
but i don't know how to create it?
can u help me senior..

ZeeroC00l 7-Apr-11 4:37am

With pleasure. But can you please post it as a new question so that others browsing the site looking for the same detail can find it easily.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Om Prakash Pant · Accepted Answer · 2011-04-06T20:55:00

Solution 1

Please check the following:

http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html[^]

Posted 6-Apr-11 20:55pm

Om Prakash Pant

*ZeeroC00l* · Accepted Answer · 2011-04-06T21:11:00

For creating a relation between the tables you can make use of PRIMARY KEY and FOREIGN KEY.
Depeding on which engine is being run you you might have to make a very minute adjustment to your query.

SQL

CREATE TABLE temp1(
       slNumber INT(4) NOT NULL AUTO_INCREMENT,
       .....
       PRIMARY KEY(slNumber)
)

SQL

CREATE TABLE temp2(
       slNumber INT(4) NOT NULL AUTO_INCREMENT,
       idNumber INT(4) NOT NULL AUTO_INCREMENT,
       .....
       PRIMARY KEY(idNumber),
       FOREIGN KEY(slNumber) REFERENCES temp1(slNumber)
)ENGINE=INNODB

Specifying the

ENGINE=INNODB

is necessary as MYISAM engine does not support the FOREIGN KEY.

For getting the relationship details like One-One, One-Many or Many-Many, i would suggest you make use of MySql Workbench[^] As it provides you with a GUI that can be used to get the details of relationship.

There are few more things that you need to be careful about before you make use of the FOREIGN KEY.
Its always advisable to make use of

ON DELETE CASCADE

and

ON UPDATE CASCADE

after the table with the FOREIGN KEY is created.
And make sure that the size of the FOREIGN KEY matches the size of the PRIMATY KEY.

BR//
Harsha Narayana