CODEWITHSUNDEEP
mysqlsql
user3650408
user3650408

Reputation: 139

Unique Key on columns from different tables

Is it possible in MySQL create a unique index on two columns from the different table? I have two table - Address (addressID - pk, customerID, code) and Customer (customerID - pk, companyID). PK- primary key. The requirement is a unique pair of code-companyID - unique address code per company. Any suggestion? Can I implement this requirement in DB level or only in beck-end side?

Upvotes: 0

Views: 1169

Answers (1)

Shadow
Shadow

Reputation: 34232

No, indexes cannot span multiple tables. However, your do not seem to grasp the way relationships are implemented in sql, hence the question.

If a customer can have a single address only, then the address id should be a field in the customer table with a foreign key pointing to the address table. In this case you do not need a multi-column unique index.

If a customer can have multiple addresses (and obviously, multiple customers may have the same address), then you have a many-to-many relationship. This is resolved by creating a 3rd table (let's call it CustomersAddresses), which will have the customer id and address id as fields (as a minimum). You define the multi-column unique index over these two fields in this CustomersAddresses table, ensuring that address - customer pairs are unique.

The customer id and address id fields in the CustomersAddresses table will be foreign keys pointing to the respective standing tables.

Upvotes: 2

Related Questions