Primary or unique constraint required on main table

[ruijorgefe]

I am trying to use Libreoffice Base to create a DB. To begin, I created 2 tables:
Table1 called “UF” with 2 fields:

• UF (TEXT Fix CHAR, Length=2) - this is the primary key with unique values
• estado (TEXT Varchar)
  Table2 called “Municipios” with 22 fields, among which:
• IdIBGE (INTEGER) - This is the primary key with unique values
• UF (TEXT Fix CHAR, Length=2)

When I try to create a relationship 1:N between these 2 tables, it gives an error: Primary or unique constraint required on main table: “UF” in statement [ALTER TABLE “Municipios” ADD FOREIGN KEY (“UF”) REFERENCES “UF” (“UF”)]

What am I doing wrong?
The UF field in the UF table is already a primary key unique.

Thank you

[Ratslinger]

Hello,

For one, giving different items the same name is sure to cause confusion in the future. Also, it is best for a primary key to be an auto increment numeric value - if not internally most databases will create another value to be used.

So if you have ‘Table1’ with:

Primary key ID Integer    Auto Incremented
          st_abbreviation TEXT Fix CHAR, Length=2
          state_name Varchar(50)

Then you have ‘Table2’ with:

Primary key ID Integer    Auto Incremented
      link_to_state   Integer
      municipality Varchar(100)

The relationship is Table2.link_to_state to Table1.ID

Additionally, you do not need 22 fields in Table2 for municipalities - a potential waste of space. Instead just create new linked records for each municipality entered. The auto increment primary key will keep all unique and you can have as many or few as needed.

[ruijorgefe]

Thank you. When I followed your comments and changed the fields to INTEGER it allowed them to relate.