Foreign keys in ORM

[Rickard Axne]

Hi. Using Visio Orm Source Model,

1. How do I tell Visio not to bother reporting an error on my
overlapping uniqueness constraints. I need this and question 2 in order
to declare referential integrity for sub/super types.

2. Is it possible to control the way referencing occurs for foreign keys
in the ORM source model. I need to reference the referenced table using
two columns with a uniqueness constraint instead of the primary key.

3. If not directly available in ORM, Should I set these constraints in
the logical model? Is "Merge" or "Update" the ORM source model needed to
prevent them from being over written during subsequent builds?

4. Is this (declaring foreign keys which reference unique constraints)
compliant with ANSI92 SQL?

Thanks, Rickard

 

[Scot Becker]

Hi Rickard,

1. How do I tell Visio not to bother reporting an error on my
overlapping uniqueness constraints. I need this and question 2 in order
to declare referential integrity for sub/super types.

Could you give an example of what you are trying to do in your model? There
might be another way....

2. Is it possible to control the way referencing occurs for foreign keys
in the ORM source model. I need to reference the referenced table using
two columns with a uniqueness constraint instead of the primary key.

No. Visio assumes that the reference mode from an object will serve as it's
primary key (if that object get's mapped as a table) and then that primary
key is used in any FK relationships.

3. If not directly available in ORM, Should I set these constraints in
the logical model? Is "Merge" or "Update" the ORM source model needed to
prevent them from being over written during subsequent builds?

I haven't tried this but I don't think it will work. I would assume Visio
would either report an error or mess up the ORM side as it tries to map
those FKs into ORM constructs.

4. Is this (declaring foreign keys which reference unique constraints)
compliant with ANSI92 SQL?

Not sure. It can be done in most DBMSs and ER/Table level modeling tools. As
a "best practice" I have never implemented a system using FKs in this way,
however. In my experience, needing to do something like this is indicative
of another problem that, once resolved, will eliminate the need for such a
structure.

Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Rickard Axne]

Hi Scot,

Was hoping I'd get a reply from you

To illustrate what I'm trying to accomplish, I will cut & paste some DDL
written by Joe Celko, hope he doesn't mind.

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);

CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

The reason I'm not just going along with the auto-generated DDL orm
does, is because I feel it lacks integrity. Orm-generated DDL doesn't
prevent a vechicle of type "SED" to be in the SUV table, for instance.

I realise I can never get as far as ensuring there really exists a
"SEDAN" instance for a vehicle of type "SED", at least not using SQL
Server. However, I want to achieve as much integrity as I can, and the
DDL above seems to me as it does a decent job.

Thanks,

Rickard

 

[Scot Becker]

Hi Rickard,

I can't think of a way of doing this in ORM without changing the reference
mode of Vehicle to be the combination of the VIN and type, which would also
alter the PK, loosen up the uniqueness of the VIN, and so on (thus, I don't
recommend doing that).

When I have implemented subtypes like this in the past, I have ensured type
consistency (e.g. SUVs only go to the SUV table) via application logic,
stored procedures, and the like. I think you could put a check clause on the
vin column in each child table that is a sub-query to the parent. For
example, something like:

vin CHAR(17) NOT NULL PRIMARY KEY CHECK(vin in (select vin from vehicles
where vehicle_type = 'suv')),

I think this is possible in SQL server, but maybe not in other DBMSs.

Hope that helps,
Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Scot Becker]

My check clause suggestion below will probably not work on most DBMSs. I was
thinking that SQL server would allow it, but it appears I was incorrect. If
possible to call a SP or function from a check constraint in your DBMS, I
would consider doing a similar query there to ensure type consistency. You
could also author a trigger to do the same check.

I would shy away from your approach of placing the type column in each child
table as that values in it will be constant and as such it seems to be a
fairly unnecessary denormalization. Further, you are likely to need to
implement other application logic that checks for that condition anyway,
so....

FYI,
Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Rickard Axne]

My check clause suggestion below will probably not work on most DBMSs. I was
thinking that SQL server would allow it, but it appears I was incorrect. If
possible to call a SP or function from a check constraint in your DBMS, I
would consider doing a similar query there to ensure type consistency. You
could also author a trigger to do the same check.

It appears that you indeed can call a UDF from a check constraint. But
that doesn't really give me what I want; yes, referential integrity will
be checked upon creation but not for the duration. If I were to change
the referenced table, that check constraint would not be fired.

I would shy away from your approach of placing the type column in each child
table as that values in it will be constant and as such it seems to be a
fairly unnecessary denormalization. Further, you are likely to need to
implement other application logic that checks for that condition anyway,
so....

I don't agree as it's required to keep a consistent state of the DB. No
matter how good the app logic is, sooner or later somebody runs a DTS or
some tech deletes a row / changes the vehicle_type and everything falls
apart.

Anyway I have to put this aside for the moment. But I will be monitoring
your website in case you decide to discuss inheritance in ORM!

Thanks Scot,

Rickard