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

How 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


Show quoteHide quote

Hi

You may have to resort to checking this in a trigger!

John

Show quoteHide quote

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

Show quoteHide quote

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.

Creating a trigger using a cursor
JDBC client - CURSOR as OUT parameter -supported ?
Just Venting
SQL Join and Where to Put the 'ON'
RUE._What's_wrong?