CONSTRAINT that adds parameter to FOREIGN KEY?

Question

CONSTRAINT that adds parameter to FOREIGN KEY?

Navigation

#1 by (3 votes)
#2 by (1 votes)

5

I have the following code for creating tables:

CREATE TABLE pessoa (
cod_pessoa int NOT NULL IDENTITY PRIMARY KEY,
nm_pessoa varchar (50) NOT NULL,
tp_pessoa char(1) NOT NULL,
endereco_pessoa varchar(50) NOT NULL,
CONSTRAINT pessoa_tipo CHECK (tp_pessoa = 'F' or tp_pessoa = 'J')
);

CREATE TABLE pessoa_fisica (
cod_pessoaf int NOT NULL PRIMARY KEY,
cpf int NOT NULL UNIQUE,
FOREIGN KEY(cod_pessoaf) REFERENCES pessoa(cod_pessoa)
);

CREATE TABLE pessoa_juridica (
cod_pessoaj int NOT NULL PRIMARY KEY,
cnpj int NOT NULL UNIQUE,
FOREIGN KEY(cod_pessoaj) REFERENCES pessoa(cod_pessoa)
);

My problem is in the pessoa_fisica and pessoa_juridica tables. As you could see, the pessoa table has a field that indicates its type tp_pessoa (F for physical and J for legal), I would like to add a CONSTRAINT or something of that type in the tables pessoa_fisica and pessoa_juridica delimiting the foreign keys only for the correct type of person. Ex (code between **):

CREATE TABLE pessoa_fisica (
cod_pessoaf int NOT NULL PRIMARY KEY,
cpf int NOT NULL UNIQUE,
FOREIGN KEY(cod_pessoaf) REFERENCES pessoa(cod_pessoa) **WHERE pessoa.tp_pessoa = 'F'**
);

Can you do this?

sql-server constraint

asked by anonymous 26.11.2014 / 18:10

2 answers

1

There is a way using a foreign key, but In this answer I explain why this is not a good idea .

The correct one, as stated in the same answer, is that you prevent direct manipulation of data by your bank users, and create Stored Procedures to make this conference for you.

There is also an alternative using Triggers , but from experience I do not recommend it because of false positives (mainly involving multiple records inserted or updated at the same time) / p>

In practice:

Create PROCEDURE dbo.InserirPessoaFisica
 @CodPessoa INT,
 @Cpf INT
AS
BEGIN

 INSERT INTO pessoa_fisica (cod_pessoaf, cpf)
 SELECT p.cod_pessoa, @Cpf
 FROM pessoa p
 WHERE p.cod_pessoa = @CodPessoa
 AND p.tp_pessoa = 'F'

END
GO

Create PROCEDURE dbo.AtualizarPessoaFisica
 @CodPessoa INT,
 @Cpf INT
AS
BEGIN

 UPDATE pessoa_fisica
 SET cpf = @Cpf
 FROM pessoa p
 WHERE p.cod_pessoa = @CodPessoa
 AND p.cod_pessoa = pessoa_fisica.cod_pessoaf
 AND p.tp_pessoa = 'F'

END
GO

26.11.2014 / 18:48

How do I pass the session from context A to context B within Tomcat 05 Design pattern Observer applied to events

score 3 · Accepted Answer

As I do not know the structure of your application and I do not know the permissions of the database and who will have access, to do what you need I would do so:

Function for the constraint

CREATE FUNCTION dbo.ValidaPessoa (@id integer, @tipo varchar(1))
RETURNS int
AS
BEGIN
  DECLARE @retorno int;
  select @retorno = count(*) from pessoa where cod_pessoa = @id and tp_pessoa = @tipo;
  RETURN(@retorno);
end;

Constraint

alter table pessoa_fisica add constraint pessoa_fisica_ck check (dbo.ValidaPessoa(cod_pessoaf, 'F') = 1)

alter table pessoa_juridica add constraint pessoa_fisica_ck check (dbo.ValidaPessoa(cod_pessoaj, 'J') = 1)

Explanation

When trying to save the registry, the system will get the value of cod_pessoaj and / or cod_pessoaf and will call the created function, doing SQL and returning the error if it is not with the correct registry. >

This way you can ensure that direct inserts in the database are also validated. In my opinion, the store procedure only increases the complexity of something simple and can easily be fooled.