0

I am now working on a new exercise and have been given the whole ERD, just have to make the database and do some queries. A note was given that Rates table has a Composite Primary Key. I know how to make Composite Primary Keys CONSTRAINT [name] PRIMARY KEY ([Col1],[Col2]) Col1 is an Int, Col2 is a Varchar

This table is then foreign keyed into the main table. To my knowledge there is no way to do this. Does anyone happen to know if there is a way?

  • 4
    check this https://stackoverflow.com/questions/3996774/foreign-key-relationship-with-composite-primary-keys-in-sql-server-2005 – Mukesh Arora May 13 '20 at 05:30

1 Answers1

1

Here is an example for composite keys. The database contains companies identified by some code for instance the EIN in the USA. The companies have departments and each company can decide for unique codes to identify their departments. Company A may use BD for their buying department, while company B uses BD also, but for their base department. Then each company has employees and each company uses some employee number to identify them. Employee #123 in comapny A is another person than employee #123 in company B. One employee works in one department in one company.

create table company
(
  company_no decimal(9,0),
  company_name varchar(100),
  primary key (company_no)
);

create table department
(
  company_no decimal(9,0),
  department_code varchar(20),
  name varchar(100),
  primary key (company_no, department_code),
  foreign key(company_no) references company(company_no)
);

create table employee
(
  company_no decimal(9,0),
  employee_no decimal(9,0),
  first_name varchar(50),
  last_name varchar(50),
  department_code varchar(20),
  primary key (company_no, employee_no),
  foreign key(company_no, department_code) references department(company_no, department_code)
);

We could also add a constraint

  foreign key(company_no) references company(company_no)

to the employee table, but that would be superfluous, as we already have the constraint on a department belonging to a company.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • dang i was so close to getting it right the first time. Thank you for the reply –  May 13 '20 at 10:00