Primary or unique constraint required on main table:

[crhaag]

Hi all,

New database user here. I am trying to create a basic construction estimator. I was just trying to create my first relationship. I have a customer table and a projects table. I would like to be able to pull the customers name into the projects table. I have brought them both up in the relationships screen, but when I try to drag the field titled "last name" from the customer table to the projects table I get a message stating:

SQL Status: S0011
Error code: -170

Primary or unique constraint required on main table: "Customers" in statement [ALTER TABLE "Projects" ADD FOREIGN KEY ("Customer Name") REFERENCES "Customers" ("LastName")]

Any help would be most appreciated

[Arineckaig]

I would like to be able to pull the customers name into the projects table

Welcome to the forum.

A Relational Database permits a table to be related to another through the use of its Foreign Key field whose data type must match that of a Primary Key field in the other table. It would appear from the error message that the "LastName" field in the "Customers" table is not defined as a Primary Key field - it has not been set to prevent duplicates by requiring unique values in that field for all records in the table.

In this particular case, it is quite likely that more than one customer could have the same LastName, so that field is not necessarily suitable to meet the requirements of a Primary Key. A common, and perhaps simplest, device would be to include a token "CustomersID" field in a "Customers" table. If this field is defined as an Auto-Increment Integer data type it provides a semi-automatic - simple, safe and suitable - Primary Key. In the "Projects" table the Foreign Key field could then be a matching "CustomersID" whose data type should also be an Integer.

A useful exposition of this and other principles that underpin a Relational Database can be obtained from:
https://wiki.documentfoundation.org/ima ... torial.pdf

Please come back if you have any questions or problems with this explanation or with the Tutorial.