• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1999
  • Last Modified:

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

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
Parth48
Asked:
Parth48
  • 5
  • 4
  • 2
  • +3
1 Solution
 
Pratima PharandeCommented:
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
 
tigin44Commented:

--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
 
Rahul AgarwalTeam LeaderCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
robastaCommented:
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
 
Parth48Author Commented:
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
 
RimvisCommented:
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
 
Parth48Author Commented:
Thanks @robasta:
0
 
Pratima PharandeCommented:
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
 
Parth48Author Commented:
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
 
Pratima PharandeCommented:
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
 
RimvisCommented:
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
 
Parth48Author Commented:
hi @pratima_mcs: thanks for the help

it's worked ...
0
 
Pratima PharandeCommented:
good to know it helps you
0
 
Parth48Author Commented:
hi @@robasta:  how can u draw that diagram bcz it's really helpful ??

can u please tell me ??
0
 
robastaCommented:
>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now