I explain. I have the following tables which are Personas
and Clientes
.
In the Persons table I have the rut (unique identification number for each person in my country) as PK. In addition to other common attributes.
CREATE TABLE Personas(
rut int primary key,
nombre varchar(20),
correo varchar(60)
);
On the other hand I have the Customers table . And this is where my question comes in. Can I have the rut as FK in this table (which comes from the Persons table), but at the same time leave it as PK? That is, leave it as the primary key of the Customers table at the same time. Which would be as follows.
CREATE TABLE Clientes(
rut_persona int primary key,
CONSTRAINT fk_Rut FOREIGN KEY (rut_persona) REFERENCES Personas (rut)
);
With this, just by leaving the rut as PK, can it no longer be repeated despite being both FK? (since that's what I'm really looking for).
The constraint I want to achieve in my model is that a person is ONE customer and a customer is ONE person.
So would it be correct/valid? I've seen this solution in a post, and the truth is that it really freaked me out, since I hadn't heard or seen this type of solution before.