I'm using PostgreSQL under ubuntu 14.04. I have two tables as below:
create table material_base_name (
id bigserial,
base_name varchar(50),
constraint pk_materials_base_name primary key (id)
);
comment on table material_base_name is 'Materials base names';
insert into material_base_name (base_name) values
('Aluminum'),
('Beryllium'),
('Columbium (niobium)'),
('Copper'),
('Ferrous'),
('Magnesium'),
('Molybdenum'),
('Nickel'),
('Tantalum'),
('Titanium'),
('Tungsten'),
('Zirconium'),
('Zinc'),
('Iron'),
('Carbon steel'),
('Alloy steel'),
('Stainless steel'),
('Tool steel'),
('Aluminum alloys'),
('Copper alloys'),
('Magnesium alloys'),
('Nickel alloys'),
('Zinc alloys'),
('Tin alloys'),
('Lead'),
('Precious metals');
Inserting data ok.
create table material_alloy (
id bigserial,
alloy_name varchar(50),
base_name varchar(50),
constraint pk_material_alloy primary key (id),
constraint fk_material_alloy foreign key (id) references material_base_name
);
comment on table material_alloy is 'Materials alloys names';
comment on column material_alloy.alloy_name is 'Materials alloy names';
comment on column material_alloy.base_name is 'Materials base names';
insert into material_alloy (alloy_name) values
('Cast and wrought alloys'),
('213.F (CH3-F)'),
('520.0-T4 (220-T4)'),
('217-T4'),
('1100-H12'),
('1100-0'),
('520.0-F (220-F)'),
('Unalloyed'),
('Unalloyed, wrought'),
('Wrought alloys'),
('Brass, high leaded (342)'),
('Brass, medium leaded (340)'),
('Phosphor bronze, FC (544)'),
('Yellow brass (268)'),
('Naval brass (464)'),
('Aluminum bronze (614)'),
('Carbon and allory steels 121.14'),
('C1212'),
('C1119'),
('C1114'),
('C1020'),
('C1040'),
('4140 resulfurized'),
('4140'),
('Stainless steels'),
('416'),
('430 F'),
('203'),
('303'),
('410'),
('430'),
('431'),
('ASTM-AZ-61'),
('ASTM-AZ-91'),
--('Unalloyed, wrought'),
('Nickel 200 to 233'),
('Monel alloy 400 to 404'),
('Monel alloy 501'),
('Monel alloy K500'),
('Inconel alloy 600'),
('Hastelloy B'),
('Inconel X-750'),
('Superalloys'),
--('Unalloyed, wrought'),
('Commercially pure '),
('T1-6A1-4V'),
('T1-13V-HCr-3A1'),
--('Unalloyed, wrought'),
('Unalloyed, sintered'),
('Commercially pure'),
('ASTM-AG40A (XX111');
When I try to insert the above data an error occurs:
ERROR: insert or update on table "material_alloy" violates foreign key constraint "fk_material_alloy" DETAIL: Key (id)=(27) is not present in table "material_base_name".
If I use other primary keys in the tables it works, such as:
constraint pk_materials_base_name primary key (base_name),
constraint pk_material_alloy primary key (alloy_name),
How come this?
My objective is to have a relation between material_base_name (metal type) and material_alloy (metal alloy type) tables since there are several metal alloy types for each metal type. If I try the query below:
select material_base_name.base_name, material_alloy.alloy_name
from material_base_name
inner join material_alloy
on material_base_name.id <> material_alloy.id
It returns the complete cartesian product between the tables: 1248 rows.
How to solve this?
