Set a Foreign key – DGI Host.com

Requirement:

I have two tables: country_list and state_list. The country_list table has a countryID field set as INT Primary Key, and the state_list table also has a countryID field. I want to set a foreign key (countryID) that references the country_list table’s countryID. How can I do this?

To set up a foreign key relationship between the countryID column in the state_list table and the countryID column in the country_list table, you need to follow these steps:

1. Ensure that the countryID column in the country_list table is defined as the primary key.
2. Add a foreign key constraint to the countryID column in the state_list table, referencing the countryID column in the country_list table.

Here’s how you can achieve this using SQL:

-- Assuming country_list table already exists with countryID as primary key

-- Create state_list table with countryID as foreign key
CREATE TABLE state_list (
    stateID INT PRIMARY KEY,
    stateName VARCHAR(100),
    countryID INT,
    FOREIGN KEY (countryID) REFERENCES country_list(countryID)
);

In this example:

• We first create the state_list table with columns stateID, stateName, and countryID.
• The countryID column in the state_list table is defined as a foreign key using the FOREIGN KEY constraint.
• The FOREIGN KEY (countryID) REFERENCES country_list(countryID) statement specifies that the countryID column in the state_list table references the countryID column in the country_list table.

This setup ensures that any value inserted into the countryID column of the state_list table must already exist in the countryID column of the country_list table, maintaining referential integrity between the two tables.

Requirement: I have two tables: country_list and state_list. The country_list table has a countryID field set as INT Primary Key, and the state_list table also has a countryID field. I want to set a foreign key (countryID) that references the country_list table’s countryID. How can I do this? To set up a foreign key relationship…