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.
If it is valid and can have its logic. I guess the most important thing is to see what he does behind the scenes to understand why he is like that.
And we can enter data without problems.
Behind the engine has created.
Two different objects in the customers table. The object
PK_Clientes_6f....
where the information of the columns that make up the table will be, and an additional index that isfk_Rut
, which will be recorded in a separate structure of the table.You can also make Inserts in the same transaction.
and they are functional
And to make the model more meaningful, add information specific to customers and not to people.
But if what you are looking for is that a person cannot be a customer twice, the same thing happens with this model, as with the one
Personas
with attributecliente bit
. The option to not have duplicates, involves forcing to cover some data, typecorreo
and that it be unique (Unique Constraint )So two people cannot have the same email.