Solved

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

Posted on 2011-03-03
15
1,943 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
  • 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now