SQL Server Performance Forum – Threads Archive
Link with a part of a table
Tell me please how can I make a foreing key constraint for a table using from another not all primary key or not all data. For example, I need to link a table "subject" with primary key "dscp_num, depart" using depart as a foreing key. But there is no table with such primary key, there is only table "codif_values" where selecting records by condition "codif_num = 1" gives the relation with necesary primary key to link. Also I need to link "subject" table as a primary key table with the table "ses_curr". But this table doesn’t include "depart" field. It includes field "reg_num" that is a primary key in table "students". This table include field "stgroup" as a foreing key for table "groups". The last table includes "speciality" field that is a foreing key for table "specialities". And only this table includes "depart" field. Tell me please how I can make a foreing key constraint for table "ses_curr" by table "subject". cogito ergo sumSeehttp://databases.about.com/od/administration/a/databasefund.htm
orhttp://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html
orhttp://www.sqlservercentral.com/columnists/bkelley/normalization.asp Every column in a table should be related to the primary key of that table. Rather than have Deptment stored in multiple tables Should there be a department table? That depends if there is other information to store about departments is in Dept_id, Department name, Department head etc. Then other tables like subject have a foreign key to Dept_id in Dept table. Ask youself what is a specialty? Is is a characteristic of a department or can the same specialty be in multiple departments? If it can be in multiple departments and especuially if there are other attributes of specialty it needs to be a separate table again with a foreign key to the department table.Then ask youself what is the relationship between sess_current and Subject? There doesn’t appear to be a direct relationship, rather a relationship via steudents, groups and specialty. Or if this isn’t right maybe a column is missing. Try diagramming out your tables and look for relationships. Good luck
]]>