CONSTRAINT that adds parameter to FOREIGN KEY?

Asked

Viewed 844 times

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 tables pessoa_fisica and pessoa_juridica. As you can see the table pessoa has a field indicating its type tp_pessoa (F for physics and J for legal), I would like to add a CONSTRAINT or something like that in the tables pessoa_fisica and pessoa_juridica that delimites the Foreign Keys only to 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'**
);

You can do it?

sql-server constraint
  • 1

    One CHECK CONSTRAINT would be enough?

  • @Guilhermeportela How can it be created? I can’t use a subquery inside a Constraint, can I? Or reference columns from other tables? I think it should be something in the Constraint Foreign key itself or a check applied to it, but how?

  • @Marianasempe I don’t know if these tables are just to illustrate the need. In being a real need, I would solve with another modeling that dispensed with this type of Constraint.

  • @Caffé this is the real need! As you suggest?

  • @Marianasempe Considering only these fields, you can simply disappear with the tables pessoa_fisica and pessoa_juridica and add a field cpf_cnpj in pessoa; would be correct because CPF and CNPJ are in fact the same type of information (unique national identification of a person). If the other fields of these two tables are rg and inscricao_estadual, does not yet justify the complexity of the new tables. Note that some companies are exempt from state inscr.and some people use other identification than RG; these Docs are only attributes and not part of a person’s identity.

  • @Marianasempe If there are many more exclusive fields for pessoa_fisica or pessoa_juridica, inform in your question that will help suggest a modeling.

  • @Caffé could have several (this is a work), the problem is that it needs to show normalization (here 2FN) so of the two tables. I only put Cpf/cnpj to simplify the registration later.

  • @Marianasempe I understand... Solving a problem that does not exist is more difficult. It is difficult to normalize person because it is very simple. Perhaps you can choose an entity easier to normalize, and the easiest to exercise normalization is with complex entities. A "client", for example, could have category, type of relationship, % allowed income commitment... And some of these attributes may be related in order to give rise to new tables, facilitating the demonstration of normalization.

Show 3 more comments

2 answers

4


As I do not know the structure of your application and do not know the database permissions 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 record, the system will pick up the value of cod_pessoaj and/or cod_pessoafand will call the function created, doing SQL and returning the error if it is not with the right registration.

This way you can ensure that direct entry into the database is also validated. In my opinion the store will only increase the complexity of something simple and can be easily circumvented.

  • +1 The nice thing about this solution is that it keeps the insertion of records symmetrical instead of entering now by INSERT in some tables and pray for stored Procedure in others. I just don’t think it’s any harder to cheat than the Stored Procedure. And where did you get that name Validausuario? :D

  • @Caffé Now that I saw that I smoked legal writing Validausuario. Edited.

1

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

The correct, as stated in the same answer, is for you to prevent direct manipulation of the data by the users of the database, and create Stored Procedures to hold this conference for you.

There is still an alternative using Triggers, but from experience do not recommend because of false positives (mainly involving multiple records entered or updated at the same time) and performance, which falls too.

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

Browser other questions tagged sql-server constraint

You are not signed in. Login or sign up in order to post.