CHECK

Hi All,
In my table I have 'filial' attribute(field), and I need check this as
folow:
If 'filial' = TRUE the attributes 'cnpj_da_matriz' and 'numero_de_filiais'
can not be NULL else if 'filial' = FALSE 'cnpj_da_matriz' and
'numero_de_filiais' must be NULL.
How I can do this? Have any way. Can you show me?
Thanks
CREATE TABLE Estabelecimentos
(
CONSTRAINT pk_cnpj
PRIMARY KEY(cnpj),
uf CHAR(2) NOT NULL,
CONSTRAINT fk_uf
FOREIGN KEY(uf)
REFERENCES UnidadesFederativas(uf),
..
filial BIT
NOT NULL,
cnpj_da_matriz INTEGER
NULL,
numero_de_filiais TINYINT
NULL,
..
contrato INTEGER
NOT NULL,
)
GOHow about something like:
Create Table Estabelecimentos
(
PK_CNPJ ? Not Null Primary Key
, UF Char(2) Not Null
, Filial Bit Not Null
, cnpj_da_matriz Int Not Null
, numero_de_filiais TinyInt Null
, contrato Int Not Null
, Constraint Check CK_Validate
Check (Case
When Filial = 1 And 'cnpj_da_matriz Is Not Null And
numero_de_filiais Is Not Null Then 1
When Filial = 0 And 'cnpj_da_matriz Is Null And
numero_de_filiais Is Null Then 1
Else 0
End = 1)
)
HTH
Thomas
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23TPXj24kFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> In my table I have 'filial' attribute(field), and I need check this as fol
ow:
> If 'filial' = TRUE the attributes 'cnpj_da_matriz' and 'numero_de_filiais'
can
> not be NULL else if 'filial' = FALSE 'cnpj_da_matriz' and 'numero_de_filia
is'
> must be NULL.
> How I can do this? Have any way. Can you show me?
> Thanks
> CREATE TABLE Estabelecimentos
> (
> CONSTRAINT pk_cnpj
> PRIMARY KEY(cnpj),
> uf CHAR(2) NOT NULL,
> CONSTRAINT fk_uf
> FOREIGN KEY(uf)
> REFERENCES UnidadesFederativas(uf),
> ...
> filial BIT
> NOT NULL,
> cnpj_da_matriz INTEGER
> NULL,
> numero_de_filiais TINYINT
> NULL,
> ...
> contrato INTEGER
> NOT NULL,
> )
> GO
>|||Hi
You may have to resort to checking this in a trigger!
John
"ReTF" wrote:

> Hi All,
> In my table I have 'filial' attribute(field), and I need check this as
> folow:
> If 'filial' = TRUE the attributes 'cnpj_da_matriz' and 'numero_de_filiais'
> can not be NULL else if 'filial' = FALSE 'cnpj_da_matriz' and
> 'numero_de_filiais' must be NULL.
> How I can do this? Have any way. Can you show me?
> Thanks
> CREATE TABLE Estabelecimentos
> (
> CONSTRAINT pk_cnpj
> PRIMARY KEY(cnpj),
> uf CHAR(2) NOT NULL,
> CONSTRAINT fk_uf
> FOREIGN KEY(uf)
> REFERENCES UnidadesFederativas(uf),
> ...
> filial BIT
> NOT NULL,
> cnpj_da_matriz INTEGER
> NULL,
> numero_de_filiais TINYINT
> NULL,
> ...
> contrato INTEGER
> NOT NULL,
> )
> GO
>
>|||What about creating a trigger to validate the rule?
Example:
create table t1 (
c1 char(1) not null check (c1 in ('t', 'f', 'T', 'F')),
c2 int,
c3 int
)
go
create trigger tr_t1_ins_upd on t1
for insert, update
as
set nocount on
if exists(select * from inserted where c1 = 't' and (c2 is null or c3 is
null))
begin
rollback transaction
raiserror('when c1 = ''t'', c2 or c3 can not be null.', 16, 1)
return
end
if exists(select * from inserted where c1 = 'f' and (c2 is not null or c3 is
not null))
begin
rollback transaction
raiserror('when c1 = ''f'', c2 and c3 must be null.', 16, 1)
return
end
go
insert into t1 values('t', 1, 2)
insert into t1 values('f', null, null)
go
insert into t1 values('t', 3, null)
go
insert into t1 values('f', 4, null)
go
drop table t1
go
AMB
"ReTF" wrote:

> Hi All,
> In my table I have 'filial' attribute(field), and I need check this as
> folow:
> If 'filial' = TRUE the attributes 'cnpj_da_matriz' and 'numero_de_filiais'
> can not be NULL else if 'filial' = FALSE 'cnpj_da_matriz' and
> 'numero_de_filiais' must be NULL.
> How I can do this? Have any way. Can you show me?
> Thanks
> CREATE TABLE Estabelecimentos
> (
> CONSTRAINT pk_cnpj
> PRIMARY KEY(cnpj),
> uf CHAR(2) NOT NULL,
> CONSTRAINT fk_uf
> FOREIGN KEY(uf)
> REFERENCES UnidadesFederativas(uf),
> ...
> filial BIT
> NOT NULL,
> cnpj_da_matriz INTEGER
> NULL,
> numero_de_filiais TINYINT
> NULL,
> ...
> contrato INTEGER
> NOT NULL,
> )
> GO
>
>|||I'd like to add a table constraint like this:
ALTER TABLE [Estabelecimentos] ADD
CONSTRAINT [CK_Estabelecimentos]
CHECK ([filial] = 1 and [cnpj_da_matriz] is not null and
[numero_de_filiais] is not null
OR
[filial] = 0 and [cnpj_da_matriz] is null and
[numero_de_filiais] is null)
GO
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.