So, I have an Employee table, where your PK is this Cod_Func.
Then I created this Seller table, I created the attributes manually, I did not put the Seller Table Cod_Func as PK,
because this is an attribute that already exists in the Employee Table.
What is defined in the Employee table is valid only for it; is not inherited by the Vendor table, even though one of the columns in the Vendor table references another column in the Employee table.
To define an existing column as the primary key, it must not allow the absence of values; that is, the column must be declared NOT NULL. The demonstration is simple.
Considering a 1: 1 relationship between Employee and Vendor, and that the following table declarations have already been defined:
-- código #1
CREATE TABLE Funcionário (
Cód_Func int,
Nome_Func varchar(50),
constraint I1_Funcionário primary key (Cód_Func)
);
and
-- código #2
CREATE TABLE Vendedor (
Cód_Func int,
Num_Percentual_Comissão numeric(3,1),
constraint FK_Vend_Func foreign key (Cód_Func)
references Funcionário (Cód_Func)
);
When executing code with ALTER TABLE statement, to create the primary key of the Vendor table
-- código #3
ALTER TABLE Vendedor
add constraint I1_Vendedor primary key (Cód_Func);
The following error messages are displayed:
Mensagem 8111, Nível 16, Estado 1, Linha 1
Não é possível definir a restrição PRIMARY KEY em coluna anulável na tabela 'Vendedor'.
Mensagem 1750, Nível 16, Estado 0, Linha 1
Não foi possível criar a restrição. Consulte os erros anteriores.
That is, the column Func_Code of the Salesperson table must be set to NOT NULL, which is simple to execute:
-- código #4
ALTER TABLE Vendedor
alter column Cód_Func int not null;
Once this is done, you can re-run code # 3 above that the Salesperson table will accept the Func_Code column as the primary key.
Normally, when declaring the columns of a table, it is defined as NOT NULL all those that are mandatory, even if it is not used to compose the primary key. In code # 1, the Func_Code column in the Employee table was purposely not declared NOT NULL. But the statement in the table contains the I1_Functional constraint as primary key , which implicitly makes the Fc_Code column NOT FULL.
Ideally, you should explicitly define the attributes of each object during the declaration:
-- código #5 v2
CREATE TABLE Funcionário (
Cód_Func int not null,
Nome_Func varchar(50) not null,
constraint I1_Funcionário primary key (Cód_Func)
);
CREATE TABLE Vendedor (
Cód_Func int not null,
Num_Percentual_Comissão numeric(3,1) not null,
constraint I1_Vendedor primary key (Cód_Func),
constraint FK_Vend_Func foreign key (Cód_Func)
references Funcionário (Cód_Func)
);