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

2

I have a table Funcionário , where its PK is this Cod_Func .

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

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

When I have to use

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

It generates an error telling me not to set this constraint on this table, using the foreign key it works fine.

    
asked by anonymous 25.11.2017 / 17:07

3 answers

0

Primary Key - > Ensures Uniqueness

Foreign Key - > Ensures the binding of one or more records to a field of a table ...


Possible Solution:

Create an FK in the vendor table, and then create a trigger to prevent two vendors from connecting 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;
/

This trigger checks before making Update or Insert in the table if there is already a vendor who has an FK linked to the employee
If there is already a salesperson who is using the FK of the employee already registered, it aborts the Insert or Update

-

I'm a student, I think this helps!

    
25.11.2017 / 17:59
0

I put the employee table here to see if it gets better to understand what I'm wanting. I have the employee table and I have this seller table, the employee has the Cod_Func which is the PK. And the seller is also an employee, so he "inherits" this COD_FUNC from the employee table, so I make the compound key, in the seller tabala I wanted it to be PK and FK.

    
26.11.2017 / 21:41
0
  

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)
);
    
26.11.2017 / 12:08