Creating a relationship

[david7777777777]

I have a SQL 2000 database and I'm trying to create a relationship between two tables through Enterprise Manager in the Diagrams view. I have one table (tbl_Departments) that holds a list of departments. I have another table that holds all the information about an employee, including a field for their department. I want to make what I believe is known as a one-to-many relationship between these two tables. Both tables have a primary key field that is an auto-generated integer.

Exactly which column should I use from each table to make the relationship? I've tried everything I can think of and it keeps giving me errors related to data type and precision. Thanks.

 

[TheInsider]

Both your primary and foreign key fields must be of exactly the same type and precision. I.e. you can't define one as type integer and the other of type char.

You should have something like a DepartmentID in your Department table which is the primary key. This field should only allow unique values. The Employee table can have a primary key as well, however it should definitely have a DepartmentID field that is exactly the same type as the one in the Departments table, except this one should accept duplicates... at least in a one to many relationship it should... and you don't want to specify an increment value for this field. This is your foreign key. You want to define the relationship between these two fields.

 

[david7777777777]

Are you saying that the name of the fields, such as DepartmentID, MUST be identical in both tables, along with data type, in order to create a relationshipe between them?

 

[TheInsider]

no, the names of the fields can be different.