Foreign key constraint against primary keys in two different table.

Foreign key constraint against primary keys in two different table.

Post by foolmelo » Sat, 16 Aug 2003 01:25:12


Say I have 3 tables A, B, C. Table A has primary key identity column
A_primary_key. Table B has primary key identity column B_primary_key.
C table has two columns: a foreign key column primary_key_from_A_or_B,
and a bool column is_A. How can I write a constraint that in table C:

1. if is_A is true, then primary_key_from_A_or_B must reference to
A_primary_key in A, and
2. if is_A is false, then primary_key_from_A_or_B must reference to
B_primary_key in B

Thank you very much for the help!
Bill
 
 
 

Foreign key constraint against primary keys in two different table.

Post by Scott Morr » Sat, 16 Aug 2003 02:14:16

You can't. The easiest way is to create the third table with a column for
each FK. If you don't want to do this, then triggers are your only option
AFAIK.

Another alternative is to create a fourth table that has the same general
structure. The 3rd is devoted to rows associated with table A, the fourth
is associated with rows from table B. You could then define a view that
unions the two tables if you need to see the rows together.

 
 
 

Foreign key constraint against primary keys in two different table.

Post by Joe Celk » Sat, 16 Aug 2003 03:54:34

First of all, the concept of "primary key identity column" is absurd. A
key is a set of attributes that unique identify an entity in the reality
from which you derived your data model. The IDENTITY property is
derived from the physical state of a particular machine that is holding
the data. There is no BOOLEAN datatype in SQL.

You are trying to fake an OO design in SQL, aren't you? This might be
what you are after --

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL UNIQUE
foo_flag CHAR(1) DEFAULT 'A' NOT NULL
CHECK(foo_flag IN ('A', 'B')),
...
PRIMARY KEY (foo_key, foo_flag));

CREATE TABLE Foo_A
(foo_key INTEGER NOT NULL UNIQUE
foo_flag CHAR(1) DEFAULT 'A' NOT NULL
CHECK(foo_flag = 'A'),
...
FOREIGN KEY (foo_key, foo_flag)
REFERENCES Foobar (foo_key, foo_flag),
PRIMARY KEY (foo_key, foo_flag));

CREATE TABLE Foo_B
(foo_key INTEGER NOT NULL UNIQUE
foo_flag CHAR(1) DEFAULT 'B' NOT NULL
CHECK(foo_flag = 'B'),
...
FOREIGN KEY (foo_key, foo_flag)
REFERENCES Foobar (foo_key, foo_flag),
PRIMARY KEY (foo_key, foo_flag));

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
 

Foreign key constraint against primary keys in two different table.

Post by Tony Roger » Sat, 16 Aug 2003 16:14:55

>>>>
The IDENTITY property is
derived from the physical state of a particular machine that is holding
the data
<<<<<

No it isn't, the IDENTITY property is simply a tool for generating an
auto-number. This is valid in the most recent ANSI standard.

An example of its use is (and I use your own schema)....

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL UNIQUE IDENTITY
foo_flag CHAR(1) DEFAULT 'A' NOT NULL
CHECK(foo_flag IN ('A', 'B')),


How else would you create foo_key? Perhaps you would reinvent the wheel and
write your own kludgy sequence generatation code? I tend to like to use
functionality that is proven and tested and is already available within the
product I'm writing for in our case Microsoft SQL Server.

Portability is not a concern - play the stats game - a very small fraction
of a percent of people actually require portability and even then,
portability is just a pipe dream and isn't really achievable in todays
client (and nor does it need to be speaking from a business perspective).

--
Tony Rogerson
SQL Server MVP
http://www.yqcomputer.com/ ?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
 
 
 

Foreign key constraint against primary keys in two different table.

Post by Joe Celk » Wed, 20 Aug 2003 01:08:23

>> None of the leading database vendors - Microsoft, IBM nor Oracle
support true portability - you cannot simply take a database schema
un-modified from one product to the next; nor can you do that with the
application either. <<

You might want to tell Janet Perna at IBM that she is doing the
impossible with the DB2 product line. Tell the DoD that the FIPS
Flagger does not help. If you begin with portability as a design goal
you can minimize almost all of the pain in the code transfers between
products. Setting up the physical configuration is more trouble.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
 

Foreign key constraint against primary keys in two different table.

Post by Joe Celk » Wed, 20 Aug 2003 01:08:25

>> Here in the UK car number plates are formed from an area code and
then a sequence number slapped on the end, no check digit - i remember
when I bought a new motor bike and was asked to choose my registration
number from a sheet of about 100. <<

The UK postal codes are also pretty bad. Each state over here has its
own auto tag system -- some with check digits and some without, but most
follow a 6-8 alphanumeric. You have to get new tags when you move to a
new state -- it is more about taxes than identifying property.

That is why we have a VIN number. I am sure that you did not have to
burn a VIN number on the engine block of the motorbike from a list of
about 100 choices.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
 

Foreign key constraint against primary keys in two different table.

Post by Tony Roger » Wed, 20 Aug 2003 03:30:19

>>>>You might want to tell Janet Perna at IBM that she is doing the
impossible with the DB2 product line. Tell the DoD that the FIPS
Flagger does not help. If you begin with portability as a design goal
you can minimize almost all of the pain in the code transfers between
products. Setting up the physical configuration is more trouble.
<<<<

If you start with portability as a design goal then you are ignoring the
more important business goal - what does the business need in order to
function?

All too often IT a specific group of IT 'professionals' adopt the approach
that IT is more important than the business - that is not true; taking that
approach is a major reason projects fail or come in greatly above cost.

Portability will add a considerable cost to a project in terms of design;
development; testing; implementation and maintainability - that needs to be
weighed up against the business justification and valued accordingly.

As a businessman and an IT professional myself I want a solution that meets
two requirements a) allows my business to function and b) can be implemented
and ongoing costs are cheaper than my competitors.

Portability should stay in educational establishments and taught as a 'this
is how the real world should operate' rather than 'this is how the real
world of business operates'.

--
Tony Rogerson
SQL Server MVP
http://www.yqcomputer.com/ ?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
 
 
 

Foreign key constraint against primary keys in two different table.

Post by foolmelo » Thu, 21 Aug 2003 00:28:47

Thank you Joe for the reply. I eventually wrote a trigger as follows.
Would you take a look to see if it is the proper way to achieve the
goal? -Bill-

CREATE TRIGGER My_Test_RI ON C
FOR INSERT, UPDATE AS
IF NOT EXISTS
(SELECT 'True'
FROM inserted, A
WHERE inserted.primary_key_from_A_or_B = A.A_primary_key
AND inserted.is_A = 1)
BEGIN
IF NOT EXISTS
(SELECT 'True'
FROM inserted, B
WHERE inserted.primary_key_from_A_or_B = B.B_primary_key
AND inserted.is_A = 0)
BEGIN
RAISERROR ('Error, invalid primary_key_from_A_or_B', 16, 1)
ROLLBACK TRAN
END
END