PDA

View Full Version : Database Question (Access)



AppleFan
07-06-2015, 04:01 PM
Hey i have a question on database . If i have a Department Table and that has primary key , like Department ID and so my question is if i have other tables like Branch , Customer , etc can i have say both Branch and Customer as a foreign key linked to the Department ID or it can be only one not both .

Please also correct me if something is wrong as im not good at this database stuff find it a bit hard to do relationship and etc .

Thanks

beama
08-06-2015, 03:22 PM
[QUOTE=AppleFan;1224517]Hey i have a question on database . If i have a Department Table and that has primary key , like Department ID and so my question is if i have other tables like Branch , Customer , etc can i have say both Branch and Customer as a foreign key linked to the Department ID or it can be only one not both .

Please also correct me if something is wrong as Im not good at this database stuff find it a bit hard to do relationship and etc .

Yes it can be a foreign key in one and a primary in another. But if I'm reading correctly what you want to do is to link two different field names in two different Tables No that wont work, to do want you want to do, both field names in the two Tables need to be the same ie primary and foreign keys. The foreign key in the linked table can be just a normal field name but must contain the same data so that when a query is run a data match is possible between the two tables example sql



These Sql statements my be out of date syntax as its been awhile since I've had anything to do with Access

SELECT [Department.Department Id],Department.Name,[Branch.Department Id],Branch.Name
FROM Department INNER JOIN Branch
On [Department.Department Id]=[Branch.Department Id]
ORDER BY Branch.Name
;


Notice the lack of Square brackets on two of the table field names The reason for the [] is that you have spaces in your field names.

inphinity
08-06-2015, 04:22 PM
Hey i have a question on database . If i have a Department Table and that has primary key , like Department ID and so my question is if i have other tables like Branch , Customer , etc can i have say both Branch and Customer as a foreign key linked to the Department ID or it can be only one not both .

Please also correct me if something is wrong as im not good at this database stuff find it a bit hard to do relationship and etc .

Thanks

You can have as many* foreign key constraints as you like. There are multiple ways it can be structured, but you cannot just use the Branch or Customer as FKs against a Department ID... the values must match, and the chance of all your customers having the same ID as their branch and department are pretty slim. Perhaps something like this.

6516

* some versions of Access and/or SQL may crap their pants if there are more than about 253 foreign key constraints

AppleFan
09-06-2015, 01:15 AM
Thanks for help .