CODEWITHSUNDEEP
sqlsql-server
srujal patel
srujal patel

Reputation: 1

Fix foreign key issue: There are no primary or candidate keys in the referenced table

CREATE TABLE Customer_MDM.membership_registration
(
    registration_num INT Primary key,
    registration_location VARCHAR(25),
    customerID INT /* FOREIGN KEY Customer details */,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    dob DATE,
    marital_status VARCHAR(25),
    gender VARCHAR(25),
    occupation VARCHAR(25),
    income_level VARCHAR(25),
    date_enrolled VARCHAR(25),
    phone_number VARCHAR(25),
    email VARCHAR(30),
    city VARCHAR(25),
    state VARCHAR(25),
    zipcode INT
);

Then:

CREATE TABLE Customer_MDM.loyalty_data
(
    customerID INT,
    loyalty_level INT,
    customer_age INT,
    reward_points INT,
    average_amount_spend INT,
    first_name VARCHAR(25) /* FOREIGN KEY ('Membership_registration') */,
    last_name VARCHAR(25) /* FOREIGN KEY ('Membership_registration') */,
    dob DATE /* FOREIGN KEY ('Membership_registration') */,
    marital_status VARCHAR(25) /* FOREIGN KEY ('Membership_registration') */,
    gender VARCHAR(25) /* FOREIGN KEY (‘Membership_registration') */,
    occupation VARCHAR(25) /* FOREIGN KEY (Membership_registration'') */,
    income_level VARCHAR(25) /* FOREIGN KEY (‘Membership_registration') */, 
    city VARCHAR(25) /* FOREIGN KEY ('Membership_registration') */,
    state VARCHAR(25) /* FOREIGN KEY ('Membership_registration’) */,
    zipcode INT /* FOREIGN KEY ('Membership_registration') */,
    PRIMARY KEY(customerID,loyalty_level)
);

Finally:

ALTER TABLE Customer_MDM.loyalty_data 
    ADD FOREIGN KEY (first_name) 
        REFERENCES Customer_MDM.membership_registration(first_name);

Trying to execute the ALTER TABLE statement I get these errors:

Msg 1776, Level 16, State 0, Line 42
There are no primary or candidate keys in the referenced table 'Customer_MDM.membership_registration' that match the referencing column list in the foreign key 'FK__loyalty_d__first__634EBE90'

Msg 1750, Level 16, State 1, Line 42
Could not create constraint or index. See previous errors.

Upvotes: 0

Views: 958

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 12959

Instead of all these complexities, simply create foreign key for membership table, once. Then all the other columns can be easily referenced.

CREATE TABLE Customer_MDM.loyalty_data (
    customerID INT,
    loyalty_level INT,
    customer_age INT,
    reward_points INT,
    average_amount_spend INT,
    registration_num INT,
    PRIMARY KEY(customerID,loyalty_level),
    FOREIGN KEY (registration_num) REFERENCES Customer_MDM.membership_registration(registration_num)
);

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

When creating the table membership_registration you need to make sure the column first_name is a key. It can be a primary key or a unique constraint.

Since the table already has a primary key, you can define this column with a unique constraint. For example:

CREATE TABLE Customer_MDM.membership_registration(
  registration_num INT Primary key,
  registration_location VARCHAR(25),
  customerID INT /* FOREIGN KEY Customer details */,
  first_name VARCHAR(25),
  last_name VARCHAR(25),
  dob DATE,
  marital_status VARCHAR(25),
  gender VARCHAR(25),
  occupation VARCHAR(25),
  income_level VARCHAR(25),
  date_enrolled VARCHAR(25),
  phone_number VARCHAR(25),
  email VARCHAR(30),
  city VARCHAR(25),
  state VARCHAR(25),
  zipcode INT,
  constraint uq1 UNIQUE (first_name) -- added constraint here
);

See running example at db<>fiddle.

In any case, first names are not good choices for a key since most likely your data model will need to accomodate multiple people with the same name.

Upvotes: 0

Belaroth
Belaroth

Reputation: 31

You need to create a unique index on first name in the membership_registration table first. Hopefully you're not actually trying to foreign key something by the first name and this is just testing foreign keys.

Upvotes: 0

Related Questions