Solved

how can i define relationship between two tables in MS SQL Server ??

Posted on 2011-03-03
15
1,964 Views
Last Modified: 2012-05-11
i want to know that how can i define relationship between two tables in MS SQL Server ??

please refer the below sql code in which i have creating two tables users and question_bank

create table users ( 
    uid     int  IDENTITY  NOT NULL,
    uname   varchar(10) not null,
	pwd     varchar(10) not null,
    age     int
);

insert into users values ('Parth','tABs$TUNs=', 20);
insert into users values ('Atul','Bins]cLa', 21);
insert into users values ('Margi','TAV!fuNd', 20);
insert into users values ('Tejas','Duns83PA', 19);
insert into users values ('Ishan','cully78a', 22);
insert into users values ('Jigar','doss43aF', 18);
insert into users values ('Nirali','sEbUM48r', 21);
insert into users values ('Nirav','LuTed[wA', 20);
insert into users values ('Ranjan','OAT&chi2', 20);
insert into users values ('Kanu','slutS11V', 21);

create table Questions_Bank
(
	QueID int   IDENTITY  not null,
	Question   varchar(200)  not null,
	Comments     varchar(50)
)

insert into Questions_Bank values ('When planning a computer program, what should a programmer always do ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('What is the term for computer languages that allow programmers to write code by focusing on how different objects interact with one another ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('What is the name of a program that takes instructions written using mnemonics and translates them into a language that the computer can understand ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('Which of the following characters ends every C# statement ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('To compile a C# source code file named MyFile.cs, what would you type at the command prompt ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('What does it mean if you compile your C# source code file and you only get copyright information about Microsoft as a response ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('What is the output from the following line of code? System.Console.Out.WriteLine( 1.7 + (int) 1.9 / 3); ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('Assume that you have an int variable named number that is storing the value 4. What is the output from the following line of code? System.Console.Out.WriteLine("number " + number); ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('Which of the following lines of code will correctly attempt to convert a String variable named age to an int and store that value in a variable named ageAsInt ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('Which of the following lines of code will display the value of the variable base raised to the power of the value of the variable power ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('How many values can a value-returning method use in its return statement ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('Which of the following lines of code will return the contents of the result variable to the calling method ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('Which of the following statements about void methods is correct ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('Which of the following method headers receives a reference to an int variable and the value of an int variable ?', 'C# Multiple Choice Questions');
insert into Questions_Bank values ('In C#, by default all variables are passed how ?', 'C# Multiple Choice Questions');

Open in new window


now how can i define relationship between above two tables (using foreign key or any other key ) ??

for ex.
how many questions a user attempt ??


0
Comment
Question by:Parth48
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +3
15 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35034049
You mean to say you want a relation ship like question to User
and one question can be with two users right ? means many-to-Many relationship
In this case you need to create third table which is having combination of user and questions

create table user_Questions
(
      uid     int ,
      QueID int   ,
Foreign Key (uid) references users(uid),
Foreign Key (QueID) references Questions_Bank(QueID)      
)
0
 
LVL 26

Expert Comment

by:tigin44
ID: 35034050

--now how can i define relationship between above two tables (using foreign key or any other key ) ??

to define a relation between two tables foreign key table should have a field related to the primary key table in your case which does not exists. In this structure you cant define a foreign key.


--how many questions a user attempt ??
If you are willing to kept track of the user question relation then you need a third table to store the student and question info that user attemted to answer. To simlify this process you should add an identity field to the questions table and store the studentId and questionId in the third table.
0
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 35034056
Both the tables are completely disjoint and there is no way to relate these two tables, although if you take 3rd table in which you take userid and QueID then its possible to make a relationship between them.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:robasta
ID: 35034060
add an intemediate table. see attached image.


add a relationship between the User.uid and User_Questions.uid
add a relationship between the Questions_Bank.QuesID and User_Questions.QuesID
DB-Design.PNG
0
 

Author Comment

by:Parth48
ID: 35034067
yes @tigin44: u r right , and relationship between user -> questions and it's one to many means 1 user
attempt multiple questions , but how can i change in my sql code , means i have to use primary key instead of identity ??
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 35034069
Hi Parth48,

You will need separate table, that joins users and questions.

For example:

CREATE TABLE question_attempts
(
	RecordID INT IDENTITY(1,1)  PRIMARY KEY,
	[uid] INT NOT NULL,
	QueID INT NOT NULL,
	AttemptTime DATETIME NOT NULL,
	AttemptResult BIT NOT NULL,
) 

ALTER TABLE question_attempts WITH CHECK ADD CONSTRAINT FK_question_attempts_Questions_Bank FOREIGN KEY(QueID) REFERENCES Questions_Bank (QueID)
ALTER TABLE question_attempts CHECK CONSTRAINT FK_question_attempts_Questions_Bank


ALTER TABLE question_attempts  WITH CHECK ADD CONSTRAINT FK_question_attempts_users FOREIGN KEY([uid]) REFERENCES users ([uid])
ALTER TABLE question_attempts CHECK CONSTRAINT FK_question_attempts_users

Open in new window

0
 

Author Comment

by:Parth48
ID: 35034073
Thanks @robasta:
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35034078
create table users (
    uid     int  PRIMARY KEY,
    uname   varchar(10) not null,
      pwd     varchar(10) not null,
    age     int
);

create table Questions_Bank
(
      QueID int  PRIMARY KEY,
      Question   varchar(200)  not null,
      Comments     varchar(50)
)

create table user_Questions
(
      uid     int ,
      QueID int   ,
Foreign Key (uid) references users(uid),
Foreign Key (QueID) references Questions_Bank(QueID)      
)
0
 

Author Comment

by:Parth48
ID: 35034085
hi @Rimvis: Thanks for the help , but i got error in your code ...

please refer the below error ..

Msg 1776, Level 16, State 0, Line 10
There are no primary or candidate keys in the referenced table 'Questions_Bank' that match the referencing column list in the foreign key 'FK_question_attempts_Questions_Bank'.
Msg 1750, Level 16, State 0, Line 10
Could not create constraint. See previous errors.


what can i do now , i have to change the column fields ??
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 35034092
drop the tables and try this

create table users (
    uid     int  PRIMARY KEY,
    uname   varchar(10) not null,
      pwd     varchar(10) not null,
    age     int
);

create table Questions_Bank
(
      QueID int  PRIMARY KEY,
      Question   varchar(200)  not null,
      Comments     varchar(50)
)

create table user_Questions
(
      uid     int ,
      QueID int   ,
Foreign Key (uid) references users(uid),
Foreign Key (QueID) references Questions_Bank(QueID)      
)
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 35034098
You don't have to drop yor tables.

Try this:
ALTER TABLE Questions_Bank ADD CONSTRAINT PK_Questions_Bank PRIMARY KEY CLUSTERED(QueID	) 
ALTER TABLE users ADD CONSTRAINT PK_users  PRIMARY KEY CLUSTERED([uid]) 

Open in new window


And then add foreighn keys:
ALTER TABLE question_attempts WITH CHECK ADD CONSTRAINT FK_question_attempts_Questions_Bank FOREIGN KEY(QueID) REFERENCES Questions_Bank (QueID)
ALTER TABLE question_attempts CHECK CONSTRAINT FK_question_attempts_Questions_Bank


ALTER TABLE question_attempts  WITH CHECK ADD CONSTRAINT FK_question_attempts_users FOREIGN KEY([uid]) REFERENCES users ([uid])
ALTER TABLE question_attempts CHECK CONSTRAINT FK_question_attempts_users

Open in new window

0
 

Author Comment

by:Parth48
ID: 35034119
hi @pratima_mcs: thanks for the help

it's worked ...
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35034131
good to know it helps you
0
 

Author Comment

by:Parth48
ID: 35034140
hi @@robasta:  how can u draw that diagram bcz it's really helpful ??

can u please tell me ??
0
 
LVL 14

Expert Comment

by:robasta
ID: 35034166
>In SQLServer Management Studio (2008, not sure about 2005) on the database, there is 'Database Diagrams', create a new diagram (right click), and add tables.

if you have already setup relationships, they will be shown by the lines. If you havent, you can drag a column from table to another, to setup a relationship.

>you can also use Visual Studio but the procedure is slightly different
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question