Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries

Question

I am facing the following problem:

I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country value in the form of a country code, countryID.

However, customer data from other countries should also be imported into this table. Although the customer numbers are unique within a country, they are not cross-country. Customer numbers should not be changed for organizational reasons.

How can a key still be ensured for each customer in this table?

score 0 · Answer 1 · answered Jan 29 '19 at 20:05

I would use countryID, CustomerID as your unique key as you have stated that you will have a unique customer id for each country. I would start with countryID as that will be unique in the system and you can add in customerID in order to get a unique key for each customer.

score 0 · Answer 2 · answered Jan 31 '19 at 10:21

It is possible to have a primary key consisting of multiple columns. This requires writing the constraint separately, as a table constraint:

CREATE TABLE customers (
    countryID   INTEGER,
    customerID  INTEGER,
    [...],
    PRIMARY KEY (countryID, customerID)
);

Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries

2 Answers2