how to create foreign reference key for compositio

Question

Suppose we have the following two tables

Create table Bank (
   Bank_ID Numeric Not Null,
   Bank_Card Numeric Not Null,
   Primary Key(Bank_ID, Bank_Card)
)

Create table Customer (
   Customer_ID Numeric Not Null,
   Name varchar(30) Not Null,
   Primary key(Customer_ID)
)

Where Customer_ID is generated by concatenating Bank_ID and Bank_Card. How can I set foreign key Customer_ID to reference Bank_ID and Bank_Card

Answer 1

What you want is a constraint, but it's not a FK (foreign key) constraint.(A FK constraint says that values for a column list appear elsewhere as PK/UNIQUE.) To enforce it declaratively you could add redundant generated (computed/calculated) column Customer_ID to Bank and a FK to it in Customer. To enforce it without adding redundant columns you need triggers. But smart keys are a bad idea.