What is the problem with these tables?
x
create table informacia
(
adress_id int primary key,
street varchar(20),
city varchar(10),
state varchar(2),
zip_code int
);
I want to create foreign key for second one
create table information
(
FirstName varchar(10) ,
LastName varchar(15),
AdressLine1 varchar(20),
City varchar(10),
StateProvinceCode varchar(2),
PostalCode int,
foreign key(PostalCode)
references informacia(zip_code) on delete set null
);
Advertisement
Answer
In order for a table to add a foreign key constraint, the referenced column (zip_code
in this case) must be a PRIMARY KEY
or must have a UNIQUE
constraint (ideally with NOT NULL
).
I modified the SQL statements according to PostgreSQL database. You don’t mention the specific database and PostgreSQL implements a highly standard SQL. The script, modified with two changes, is:
create table informacia(
adress_id int primary key,
street varchar(20),
city varchar(10),
state varchar(2),
zip_code int not null, -- added NOT NULL
constraint uq_zip_code unique (zip_code) -- added the UNIQUE constranit
);
create table information(
FirstName varchar(10) ,
LastName varchar(15),
AdressLine1 varchar(20),
City varchar(10),
StateProvinceCode varchar(2),
PostalCode int,
foreign key(PostalCode) references informacia(zip_code) on delete set NULL
);
See running example at DB Fiddle.