Create CHECK over FK field

Question

Post reply

Create CHECK over FK field

Miquel Martí

Ten Centuries

Points: 1217
More actions
February 15, 2006 at 7:34 am

#90603

hi!
I was just throwing a script to create a CHECK constraint, but it shows an error like this:
ALTER TABLE Service
    ADD CONSTRAINT CK_Service_ServiceRestrictionElement
    CHECK ( ServiceRestrictionElement is NULL OR
           (ServiceRestrictionElement is not NULL AND IsConsolidated = 0)
                 )
    Servidor: mensaje 1759, nivel 16, estado 1, línea 174
    Invalid column 'ServiceRestrictionElement' is specified in a constraint or computed-column definition.
Can't I create a CHECK over a FK field?
I'm stuck now, and Books Online seems not to mention this. Could you please confirm?
Thank you

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

JeffB Hall of Fame Points: 3541 More actions · Answer 1

What is the table definition for Service, specifically the ServiceRestrictionElement column?

Miquel Martí Ten Centuries Points: 1217 More actions · Answer 2

Should I suppose it's not possible to constraint a check over a FK field??? See error below

Since 'Service' table is too long to be listed here,

this is the field involved in the issue:

ALTER TABLE Service

ADD ServiceRestrictionElement int NULL

ALTER TABLE Service

ADD CONSTRAINT FK_Service__ServiceRestrictionElement

FOREIGN KEY (ServiceRestrictionElement)

REFERENCES ServiceRestrictionElement(ServiceRestrictionElement)

ALTER TABLE Service

ADD CONSTRAINT CK_Service_ServiceRestrictionElement

CHECK ( ServiceRestrictionElement is NULL OR

(ServiceRestrictionElement is not NULL AND IsConsolidated = 0)

)

Servidor: mensaje 1759, nivel 16, estado 1, línea 174

Invalid column 'ServiceRestrictionElement' is specified in a constraint or computed-column definition.

Miquel Martí Ten Centuries Points: 1217 More actions · Answer 3

Miquel Martí

Ten Centuries

Points: 1217

February 15, 2006 at 5:43 pm

#621239

nobody?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Check constraints cannot include a computed column, See below for the first set of DDL that does not have a computed column and then with a computed column.

This makes sense as the value of the computed column is not known until the row is know to be valid which is after all contraints are run.

create table ServiceRestrictionElement

(ServiceRestrictionElement int not null primary key )

go

create table Service

( ServiceRestrictionElement int NULL

, IsConsolidated int NULL

)

go

ALTER TABLE Service

ADD CONSTRAINT FK_Service__ServiceRestrictionElement FOREIGN KEY (ServiceRestrictionElement)

REFERENCES ServiceRestrictionElement(ServiceRestrictionElement)

go

ALTER TABLE Service

ADD CONSTRAINT CK_Service_ServiceRestrictionElement

CHECK ( ServiceRestrictionElement is NULL

OR (ServiceRestrictionElement is not NULL AND IsConsolidated = 0 ) )

go

This causes the error:

Server: Msg 1759, Level 16, State 1, Line 1

Invalid column 'IsConsolidated' is specified in a constraint or computed-column definition.

alter table Service

drop CONSTRAINT CK_Service_ServiceRestrictionElement

go

alter table Service

drop column IsConsolidated

go

alter table Service

add TestIt int NULL

go

alter table Service

add IsConsolidated as coalesce(TestIt, 0 )

go

ALTER TABLE Service

ADD CONSTRAINT CK_Service_ServiceRestrictionElement

CHECK ( ServiceRestrictionElement is NULL

OR (ServiceRestrictionElement is not NULL AND IsConsolidated = 0 ) )

go

SQL = Scarcely Qualifies as a Language

Miquel Martí Ten Centuries Points: 1217 More actions · Answer 5

ThanKs Carl !!!

I will be forced to use a trigger to make this simple check

Transac! You are ugly!

PD: would be great if some T-guru could explain why Foreigns are'nt checked before Checks