Identifying WITH [NO] CHECK OPTION status programatically

[jgrogan]

Hi all

Is there a way to determine if a referential constraint is declaredWITH CHECK OPTION or WITH NO CHECK OPTION - programmatically - without writing a parser for the originating DDL?

This attribute appears to be lacking in the Data Dictionary tables, which leads me to think that the value is (probably) stored in the table header. If this is the case, how can I get at it?

Any thoughts, views (or even answers!) much appreciated.

BRgds
J.

 

[dnoeth]

AFAIK there's no way to get that info with a SELECT, as you already mentioned, it's stored only within the table header :-(

If the FK is a named constraint there's a column "State" returned by "help constraint tablename.fkname", which contains e.g. VALID/VALIDSOFT/VALIDBATCH, this is much easier to parse than the DDL.

And constraint names are stored within dbc.ConstraintNames:
sel * from dbc.ConstraintNames
where ConstraintType = 'R'

But if the FK has no name...

Dieter

 

[jgrogan]

Thanks Dieter,

You just confirmed by worst fears. I don't suppose you have the C struct for a table header handy...?

Regards
J.

 

[dnoeth]

Even if knew the C struct of a table header it would be useless, because it can't be accessed using C or any other programming language.
The only tool to access that information is probably Filer and you really don't want to deal with that ;-)

Dieter

 

[jgrogan]

Dieter

I concede it probably wouldn't be easy, but even Filer was written in something.

That aside, I find it limiting that details of a key feature of the database is unavailable in the DD/D. Almost everything else is there...

Kind regards
James