i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.
my database structure is
table1 - students table
table 2 - subjects table
table 3 - subjects opted by students
in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.
table structure
students
ID -primary key, FirstName, LastName, ........
subjects
ID->primary key, subject
student_subject s
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.
now if in the student_subject s table i have a record with data
ID-1000, StudentID-100, sub1-1, sub2-2......sub6-6,
how can i join table in SQL query to access the student_subject s table
so that i can display the result as
ID->studentID (name not required)
but from sub1 to sub6 i should display the actual subject name that is
in the subjects table ?
for example :
ID sub1 sub2
sub3 ............... ......... sub6
1000 telecom. cryptography
networking ............... ........ cgi/perl 6 14289
The 3rd table is not right.
Table 3 needs fields:
ID primary key
StudentID foreign key
SubjectID foreign key
Now you have need only one relationship to the Subject table.
The idea is to have many records here, instead of repeating fields. So if
Student 24 enrols in subjects 2,4, and 7, you have records like this:
StudentID SubjectID
24 2
24 4
24 7
(It is actually possible to create multiple relationships from one table to
another by adding multiple copies of the table into the Relationships
window, but that is to solve a different problem.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<ra***********@ gmail.comwrote in message
news:11******** **************@ y66g2000hsf.goo glegroups.com.. .
>i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.
my database structure is
table1 - students table
table 2 - subjects table
table 3 - subjects opted by students
in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.
table structure
students
ID -primary key, FirstName, LastName, ........
subjects
ID->primary key, subject
student_subject s
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.
now if in the student_subject s table i have a record with data
ID-1000, StudentID-100, sub1-1, sub2-2......sub6-6,
how can i join table in SQL query to access the student_subject s table
so that i can display the result as
ID->studentID (name not required)
but from sub1 to sub6 i should display the actual subject name that is
in the subjects table ?
for example :
ID sub1 sub2
sub3 ............... ......... sub6
1000 telecom. cryptography
networking ............... ........ cgi/perl
On Thu, 22 Mar 2007 10:02:57 +0900, "Allen Browne"
<Al*********@Se eSig.Invalidwro te:
Two fields is enough:
StudentID
SubjectID
and don't forget to create a primary key over the combination of those
two fields.
The extra ID column can come in handy if you're linking this table to
other tables, but if it's only used as a "junction table", there is no
need.
-Tom.
>The 3rd table is not right.
Table 3 needs fields:
ID primary key
StudentID foreign key
SubjectID foreign key Now you have need only one relationship to the Subject table. The idea is to have many records here, instead of repeating fields. So if Student 24 enrols in subjects 2,4, and 7, you have records like this:
StudentID SubjectID
24 2
24 4
24 7
(It is actually possible to create multiple relationships from one table to another by adding multiple copies of the table into the Relationships window, but that is to solve a different problem.)
Perhaps I misread the original question, Tom.
The way I read it, there might be occasions when the StudentID + SubjectID
is not unique. For example, the student might submit 6 preferences this
year, do 4 subjects, and then submit one of the other two as a preference
again next year.
If you don't need that flexibility, ravichoudhari, then Tom's suggestion is
better.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Tom van Stiphout" <no************ *@cox.netwrote in message
news:cj******** *************** *********@4ax.c om...
On Thu, 22 Mar 2007 10:02:57 +0900, "Allen Browne"
<Al*********@Se eSig.Invalidwro te:
Two fields is enough:
StudentID
SubjectID
and don't forget to create a primary key over the combination of those
two fields.
The extra ID column can come in handy if you're linking this table to
other tables, but if it's only used as a "junction table", there is no
need.
-Tom.
>>The 3rd table is not right.
Table 3 needs fields: ID primary key StudentID foreign key SubjectID foreign key Now you have need only one relationship to the Subject table. The idea is to have many records here, instead of repeating fields. So if Student 24 enrols in subjects 2,4, and 7, you have records like this: StudentID SubjectID 24 2 24 4 24 7
(It is actually possible to create multiple relationships from one table to another by adding multiple copies of the table into the Relationships window, but that is to solve a different problem.)
On Mar 22, 3:07 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
Perhaps I misread the original question, Tom.
The way I read it, there might be occasions when the StudentID + SubjectID
is not unique. For example, the student might submit 6 preferences this
year, do 4 subjects, and then submit one of the other two as a preference
again next year.
If you don't need that flexibility,rav ichoudhari, then Tom's suggestion is
better.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Tom van Stiphout" <no.spam.tom7.. .@cox.netwrote in messagenews:cj* *************** *************** *@4ax.com...
On Thu, 22 Mar 2007 10:02:57 +0900, "Allen Browne"
<AllenBro...@Se eSig.Invalidwro te:
Two fields is enough:
StudentID
SubjectID
and don't forget to create a primary key over the combination of those
two fields.
The extra ID column can come in handy if you're linking this table to
other tables, but if it's only used as a "junction table", there is no
need.
-Tom.
>The 3rd table is not right.
>Table 3 needs fields:
ID primary key
StudentID foreign key
SubjectID foreign key Now you have need only one relationship to the Subject table. The idea is to have many records here, instead of repeating fields. So if Student 24 enrols in subjects 2,4, and 7, you have records like this:
StudentID SubjectID
24 2
24 4
24 7
>(It is actually possible to create multiple relationships from one table to another by adding multiple copies of the table into the Relationships window, but that is to solve a different problem.)
the solution of having studentid & subjectid as you have specified can
hold multiple records of a student and gives lot of flexibility (in
fact a good design of table), but what i need in my application
displaying the records of each student in a flex grid along with the
the subjects (subject names) as a single record in a row
ex:
st1 sub1 sub2 ........
st2 sub1 sub2 ........
where sub1 ..... should be actual names of subjects
(subjetcs.subje ct).
So you want to concatenate the subject names back together as one long
string again for display purposes?
See:
Return a concatenated list of sub-record values
at: http://www.mvps.org/access/modules/mdl0004.htm
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<ra***********@ gmail.comwrote in message
news:11******** **************@ y80g2000hsf.goo glegroups.com.. .
On Mar 22, 3:07 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
>Perhaps I misread the original question, Tom.
The way I read it, there might be occasions when the StudentID + SubjectID is not unique. For example, the student might submit 6 preferences this year, do 4 subjects, and then submit one of the other two as a preference again next year.
If you don't need that flexibility,rav ichoudhari, then Tom's suggestion is better.
"Tom van Stiphout" <no.spam.tom7.. .@cox.netwrote in messagenews:cj *************** *************** **@4ax.com...
On Thu, 22 Mar 2007 10:02:57 +0900, "Allen Browne"
<AllenBro...@Se eSig.Invalidwro te:
Two fields is enough:
StudentID
SubjectID
and don't forget to create a primary key over the combination of those
two fields.
The extra ID column can come in handy if you're linking this table to
other tables, but if it's only used as a "junction table", there is no
need.
-Tom.
>>The 3rd table is not right.
>>Table 3 needs fields: ID primary key StudentID foreign key SubjectID foreign key Now you have need only one relationship to the Subject table. The idea is to have many records here, instead of repeating fields. So if Student 24 enrols in subjects 2,4, and 7, you have records like this: StudentID SubjectID 24 2 24 4 24 7
>>(It is actually possible to create multiple relationships from one table to another by adding multiple copies of the table into the Relationships window, but that is to solve a different problem.)
the solution of having studentid & subjectid as you have specified can
hold multiple records of a student and gives lot of flexibility (in
fact a good design of table), but what i need in my application
displaying the records of each student in a flex grid along with the
the subjects (subject names) as a single record in a row
ex:
st1 sub1 sub2 ........
st2 sub1 sub2 ........
where sub1 ..... should be actual names of subjects
(subjetcs.subje ct).
That is an invalid table structure. It is not in normal form and it is
prone to error.
Since there are many subject for the student and many student for the subjet
you need a join table with student id and subject id. To make it function
properly. Basically you have a table inside a table and that does not work.
<ra***********@ gmail.comwrote in message
news:11******** **************@ y66g2000hsf.goo glegroups.com.. .
>i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.
my database structure is
table1 - students table
table 2 - subjects table
table 3 - subjects opted by students
in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.
table structure
students
ID -primary key, FirstName, LastName, ........
subjects
ID->primary key, subject
student_subject s
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.
now if in the student_subject s table i have a record with data
ID-1000, StudentID-100, sub1-1, sub2-2......sub6-6,
how can i join table in SQL query to access the student_subject s table
so that i can display the result as
ID->studentID (name not required)
but from sub1 to sub6 i should display the actual subject name that is
in the subjects table ?
for example :
ID sub1 sub2
sub3 ............... ......... sub6
1000 telecom. cryptography
networking ............... ........ cgi/perl This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: pb648174 |
last post by:
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.
Is there any way to specify a foreign key that will allow for the
different types indicating which table the...
|
by: Bart |
last post by:
Hello
Sometimes i need to drop one of my tables. DROP TABLE removes all
foreign keys connected with that table. After this i need to recreate FK.
It is not easy to find dropped keys, so i have script to create all keys
in database (ALTER TABLE ... ). It works good, but when FK exists, it
will create another (exacly the same).
|
by: Eva Hovelsrød |
last post by:
Hi, I just realized that foreign keys to an inherited table don't work when
the record was inserted into a child table. That is postgresql-7.3.2-3.
Does anyone know whether anyone is working on that area or if there are any
plans to?
Or is there a simple workaround, like in the foreign key definition to say
that a column should reference the parent table OR the child table (i.e.,
providing a list of tables to look for references in)?
|
by: Thomas T. Thai |
last post by:
I'm looking for a better way to make use of foreign keys. Here is a
sample setup:
-- TESTING Foreign Keys
create table mod (
mod_id int not null primary key,
name varchar(32) not null default ''
);
|
by: sonal |
last post by:
Hi all,
I hv started with python just recently... and have been assigned to
make an utility which would be used for data validations...
In short we take up various comma separated data files
for eg: area.txt, school.txt, students.txt.... and so on (ok?!?)
now,
1. area code used in the school.txt must be defined in the area.txt
(Primary key in area => area_code defined in area.txt
& Foreign key on school => area_code defined in...
| |
by: chrisek |
last post by:
Hello,
I have 2 tables:
- Customers with columns:
customerID(prim_key),
customerName(with customer's names)
- Deliveries with columns:
deliveryID(primKey),
sender(ref_key to CustomerID from Customers),
receiver(also ref_key to CustomerID from Customers);
|
by: rsrinivasan |
last post by:
Hi,
1. I have some table. But i do not know how to find primary key column, forign key column in that table. Is any procedure or function is available in oracle to find this?.
Thanks,
Srinivasan r
|
by: Giulio Simeone |
last post by:
Hello,
when I try to insert a new record in a certain table I get the error
Cannot add or update a child row: a foreign key constraint fails
The table has three foreign keys and six other fields, but this error
appairs with every record I try to insert in the table, even if I
don't give any values to the foreign keys.
What can be the problem?
|
by: shreedhan |
last post by:
I have two tables table1 and table2
Table1 has a foreign key which references key of table2
Table2 also has a foreign key which references key of table1
Now when I try to insert into any of those 2 tables, violation of foreign key occurs.
How can I insert into both tables without violating foreign key
Is there any way to insert both tables simultaneously?
thanks
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |