Create Relationships Between Tables OR Create Foreign Key Relationships

Here I am going to show, how to create multiple tables with relations between tables (Country, State and City)

Method 1: New Tables

create table Country

(

       ID int identity(1,1) primary key,

       Name nvarchar(500) Not NULL,

       ShortName nvarchar(15)

)

create table State

(

       ID int identity(1,1) primary key,

       StateName nvarchar(500) Not NULL,

       CountryID int,

       foreign key (CountryID) references Country(ID)

)

create table City

(

       ID int identity(1,1) primary key,

       CityName nvarchar(500) Not NULL,

       StateID int,

       foreign key (StateID) references State(ID)

)

Method 2: Existing Tables

create table Country

(

       ID int identity(1,1) primary key,

       Name nvarchar(500) Not NULL,

       ShortName nvarchar(15)

)

create table State

(

       ID int identity(1,1) primary key,

       StateName nvarchar(500) Not NULL,

       CountryID int

)

create table City

(

       ID int identity(1,1) primary key,

       CityName nvarchar(500) Not NULL,

       StateID int

)

above Tables are not having Foreign Key Relations.

so we need to alter State and City Tables as follows.

Alter Table State Add CONSTRAINT FK_State FOREIGN KEY (CountryID) references Country(ID)

In the same way for City also

Alter Table City Add CONSTRAINT FK_City foreign key (StateID) references State(ID)