Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Relationship
- Thread starter MSSQL123
- Start date
mrdenny
Programmer
- May 27, 2002
- 11,595
r937, That's an awsome answer, I don't think it's quite the direction that the op is looking for.
123,
Typically there will be a PK/FK relationship defined between the primary key of the column to the forign key. This will be normally be done if the relationship is on one table or two tables.
The code to see this information will vary depending on database platform. Based on your name I assume that you are using Microsoft SQL Server. On SQL Server this code will give you all the constraints in the current database.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
123,
Typically there will be a PK/FK relationship defined between the primary key of the column to the forign key. This will be normally be done if the relationship is on one table or two tables.
The code to see this information will vary depending on database platform. Based on your name I assume that you are using Microsoft SQL Server. On SQL Server this code will give you all the constraints in the current database.
Code:
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEDenny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
mrdenny
Programmer
- May 27, 2002
- 11,595
Key Columns not constraints sorry.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
mrdenny, indeed they are very deep questions
here's a sample table:
create table mrdenny
( col1 integer not null primary key
, col2 varchar(99)
, col3 varchar(99)
);
now, can you tell me how you would find a relation between col2 and col3 in this table?
me, i would first of all do a GROUP BY on them and examine the results
what if the results of such a GROUP BY included the following:
Toronto, IA
Toronto, KS
Toronto, OH
Toronto, ON
Toronto, SD
what is the relationship between col2 and col3 now?
how about their relationships to col1, the PK?
see? context matters
r937.com | rudy.ca
here's a sample table:
create table mrdenny
( col1 integer not null primary key
, col2 varchar(99)
, col3 varchar(99)
);
now, can you tell me how you would find a relation between col2 and col3 in this table?
me, i would first of all do a GROUP BY on them and examine the results
what if the results of such a GROUP BY included the following:
Toronto, IA
Toronto, KS
Toronto, OH
Toronto, ON
Toronto, SD
what is the relationship between col2 and col3 now?
how about their relationships to col1, the PK?
see? context matters
r937.com | rudy.ca
mrdenny
Programmer
- May 27, 2002
- 11,595
Oh course context matters. I guess I broke my own rule. I made assumptions as to what direction the op was going without clarifying first. Consider my hand slapped.
Op, can you please clarify.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
Op, can you please clarify.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
1. There should be no relationship between 2 columns in the same table. If there is any, the structure might be wrong. In any case, I don't think any database engine has any clue about such relationships.
2. Information_Schema might be of help...
HTH
![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")
Daniel Vlas
Systems Consultant
2. Information_Schema might be of help...
HTH
Daniel Vlas
Systems Consultant
elsenorjose
Technical User
Sorry but I had to pipe in with my .02 
/pedantry on
The poster's question wasn't about relationships, it was about relations. Now, I'm fairly certain it was intended to be a question about relationships but none of the subsequent posts asked for the clarification.
A relation is, loosely speaking, the physical implementation of the entity, the 'table'. When speaking of a relation, we speak of how each column in the 'table' is somehow related to the entity being modeled. Hence, a 'customer' entity or table can have name, account number, and address which all relate to the customer.
A relationship is different in that it describes how different tables in a database are related to each other. Typically this is through the use of common keys between the tables but can also exist in a non-key relationship. Examples would be relationships between salary and bonus structures where the bonus is set but based on a range in the salary table.
/pedantry off
/pedantry on
The poster's question wasn't about relationships, it was about relations. Now, I'm fairly certain it was intended to be a question about relationships but none of the subsequent posts asked for the clarification.
A relation is, loosely speaking, the physical implementation of the entity, the 'table'. When speaking of a relation, we speak of how each column in the 'table' is somehow related to the entity being modeled. Hence, a 'customer' entity or table can have name, account number, and address which all relate to the customer.
A relationship is different in that it describes how different tables in a database are related to each other. Typically this is through the use of common keys between the tables but can also exist in a non-key relationship. Examples would be relationships between salary and bonus structures where the bonus is set but based on a range in the salary table.
/pedantry off
danvlas said:
What if you have an employee table that stores the employee's supervisor?
Employee
EmpID
FName
LName
SupervisorID (Fk to EmpID)
now I have a relationship between EmpID and SupervisorID in the same table. Not a problem!
Leslie
Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual
Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins
Rudy, you have a point.
However, I (stubborn me) stick with the idea that no column depends on another column. Otherwise changing one value would imply altering the value in the other.
Primary key is indeed an exception, but all columns/column combination depend on it. Not because any value in the record needs to be changed when PK value changes, but because the identity of the record is different.
Leslie, I also disagree with your example.
The employee is the same no matter who his supervisor is. Changing the name as a result of marriage does not change the supervisor. On the other hand, a new supervisor will not change any other attribute of the employee.
What you gave as an example shows the case of a table that acts as 2 different relations.
Moreover, the structure you presented loses all subordination history (how many supervisors an employee had, who were they and when).
The foreign key SupervisorID in this table is just a little better than a boolean HasSupervisor field.
A 'Supervision' table would solve history, but it would remove the SupervisorId FK from Employees table:
supID (PK)
supEmployeeID (FK to employees)
supSupervisorID (FK to employees)
supDateSet
Of course, a constraint on the last 3 fields would be necessary.
Needlessto say, removing the field from the table means removing the self-join, thus the 'relationship' between 2 fields in the same table.
Daniel Vlas
Systems Consultant
However, I (stubborn me) stick with the idea that no column depends on another column. Otherwise changing one value would imply altering the value in the other.
Primary key is indeed an exception, but all columns/column combination depend on it. Not because any value in the record needs to be changed when PK value changes, but because the identity of the record is different.
Leslie, I also disagree with your example.
The employee is the same no matter who his supervisor is. Changing the name as a result of marriage does not change the supervisor. On the other hand, a new supervisor will not change any other attribute of the employee.
What you gave as an example shows the case of a table that acts as 2 different relations.
Moreover, the structure you presented loses all subordination history (how many supervisors an employee had, who were they and when).
The foreign key SupervisorID in this table is just a little better than a boolean HasSupervisor field.
A 'Supervision' table would solve history, but it would remove the SupervisorId FK from Employees table:
supID (PK)
supEmployeeID (FK to employees)
supSupervisorID (FK to employees)
supDateSet
Of course, a constraint on the last 3 fields would be necessary.
Needlessto say, removing the field from the table means removing the self-join, thus the 'relationship' between 2 fields in the same table.
Daniel Vlas
Systems Consultant
dan, it is not a question of changing a value, but rather, if you pick a different value for the one column, does that give you a different value of the other column
the classic example:
employees
empid deptid deptname
here the pk is empid, and there is a relationship between deptid and deptname
r937.com | rudy.ca
the classic example:
employees
empid deptid deptname
here the pk is empid, and there is a relationship between deptid and deptname
r937.com | rudy.ca
Which combination should not exist in the employees table...
DeptName should be in Departments, PK DepID, referenced by the foreign key in Employees
Daniel Vlas
Systems Consultant
DeptName should be in Departments, PK DepID, referenced by the foreign key in Employees
Daniel Vlas
Systems Consultant
yes, but dan, that's only if the tables are properly normalized, which was my point!!
what if they aren't?![[neutral]](/data/assets/smilies/neutral.gif "[neutral] [neutral]")
what if the purpose of asking the question in post #1 of this thread was "how do you detect a relationship between two columns?"
if you don't believe me, go back and read that first post
me, i'm not going to say very much more about this besides context matters and wait for the original poster, who probably didn't understand half the replies in this thread, to come back and clarify for us
r937.com | rudy.ca
what if they aren't?
what if the purpose of asking the question in post #1 of this thread was "how do you detect a relationship between two columns?"
if you don't believe me, go back and read that first post
me, i'm not going to say very much more about this besides context matters and wait for the original poster, who probably didn't understand half the replies in this thread, to come back and clarify for us
r937.com | rudy.ca
- Status
Similar threads
- Locked
- Question
- Locked
- Question