[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-03-03
15
Medium Priority
?
1,985 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:Rahul Agarwal
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Industry Leaders: 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!

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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