How to use alter table by modifying an attribute to PK and FK?

Question

I have a table Funcionário, where your PK that’s the one Cod_Func.

Then I created this table Vendedor, created the attributes manually, did not put the Cod_Func table Vendedor as PK, because this is an attribute that already exists in the Table Funcionário.

Only I have a relationship between the two tables where the Table Vendedor the Cod_Func is PK and FK.

When I have to use the

ALTER TABLE [dbo].[VENDEDOR]
ADD CONSTRAINT PK_COD_FUN_VENDEDOR PRIMARY KEY(COD_FUN)

it generates me an error saying that do not define this constraint in this table, using the Foreign key it works fine.

Answer 1

So I have an Employee table, where your PK is this Cod_func.

After I created this Seller table, I created the attributes manually, I did not put Cod_func from the Seller Table as PK, because this is an attribute that already exists in the Employee Table.

What is defined in the Employee table is only valid for it; it is not inherited by the Seller table, even if one of the columns of the Seller table refers to another column in the Employee table.

---

To define an existing column as a primary key it is necessary that it does not allow the absence of values; that is, the column must be declared as NOT NULL. The demonstration is simple.

Considering the relationship 1:1 between Employee and Seller, and the following table declarations have already been defined previously:

-- 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 instruction, to create the primary key of the Seller 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 Code column of the Seller table must be defined as 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 run code #3 above again and the Seller table will accept the Cód_func column as the primary key.

---

When declaring the columns of a table, the usual is to define as NOT NULL all those that are mandatory, even if it is not used to compose the primary key. In code #1, the Code_func column of the Employee table was purposely not declared as NOT NULL. But in the statement of the table there is the restriction I1_employee as Primary key, which implicitly makes the Code column function as NOT FULL.

The ideal is to 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)
);

Answer 2

I put the employee table here to see if it gets better to understand what I’m looking for. I have the employee table and I have this seller table, the employee has the Cod_func that is the PK. And the seller is also an employee, so he "inherits" this COD_FUNC from the employee table, so I make the composite key, in the seller tabala I wanted it to stay as PK and FK.

Answer 3

Primary Key (Primary Key) -> Ensures Oneness

Foreign Key (Foreign Key) -> Ensures the connection of one or several records to a table field...

Possible Solution:

Create an FK in the seller table, then create a Rigger to prevent two vendors from being linked to the same employee.

Oracle PL/SQL

CREATE TRIGGER tg_vendedor
BEFORE INSERT OR UPDATE
ON vendedor
FOR EACH ROW
REFERENCING NEW AS NEW
DECLARE cod_f number;

BEGIN
 SELECT COUNT(cod_func) INTO cod_f FROM vendedor WHERE cod_func = :new.cod_func;

 IF cod_f > 0 THEN
  raise_application_error( -20001, 'O funcionário já e um vendedor!' );
 END IF;

END;
/

Is Rigger checks before doing Update or Insert on the table if there is already a seller who has an FK linked to the employee
If there is already a seller using the employee’s FK already registered, he aborts Insert or Update

--

I’m a student, I think it helps you!