foreign keys - on which kind of keys do the base on?

[Fritz Franz]

Hello!

I have a table A with fields id,startdate and other fields. id and startdate
are in the primary key.
In the table B I want to introduce a Foreign key to field id of table A.

Is this possible? If yes, which kind of key I have to build in table A?

Thx in advance,

Fritz

[Erland Sommarskog]

Fritz Franz (fr**********@hotmail.com) writes:

I have a table A with fields id,startdate and other fields. id and
startdate are in the primary key.
In the table B I want to introduce a Foreign key to field id of table A.

Is this possible? If yes, which kind of key I have to build in table A?

If I understand this correctly, you have this:

CREATE TABLE A(id int NOT NULL,
startdate datetime NOT NULL,
otherfield varchar(89) NULL,
PRIMARY KEY (id, startdate))

Now you want to create a table B with a column id, and you want to add a
check that the values in B.id corresponds to a value of id that also in in
A. You cannot do this with DRI (Declarative Referential Integrity), but
you would have to use a trigger. A foreign key must refer to all columns
of the primary key in the other table; it cannot be a partial key.

I don't know about your data model, but I would hold it as likely that
either you have an incorrect design, or you have a misconception of what
you want to do. I have ran into the situation that I wanted a partial
FK myself, but it's a very rare scenario.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Fritz Franz]

"Erland Sommarskog" <es****@sommarskog.sewrote

If I understand this correctly, you have this:

CREATE TABLE A(id int NOT NULL,
startdate datetime NOT NULL,
otherfield varchar(89) NULL,
PRIMARY KEY (id, startdate))

Now you want to create a table B with a column id, and you want to add a
check that the values in B.id corresponds to a value of id that also in in
A.

Yes, that's all right.

>You cannot do this with DRI (Declarative Referential Integrity), but
you would have to use a trigger. A foreign key must refer to all columns
of the primary key in the other table; it cannot be a partial key.

I don't know about your data model, but I would hold it as likely that
either you have an incorrect design, or you have a misconception of what
you want to do. I have ran into the situation that I wanted a partial
FK myself, but it's a very rare scenario.

OK, I understand. Yes, you ar right, the design of DB is not normalized
correctly. But for some reasons we decided to do it this way. It is not that
important to have the FK defined in the DB, the applications will ensure the
integrity of the data.

Thanks for your help!

Regards,

Fritz

[--CELKO--]

>OK, I understand. Yes, you are right, the design of DB is not normalized correctly. But for some reasons we decided to do it this way. <<

Then go back and do it right. Would you accept an automobile mechanic
telling you that he did not put your tires on right, but as long as
you don't go too fast or turn too sharply, there will not be any
problems?

>It is not that important to have the FK defined in the DB, the applications will ensure the integrity of the data. <<

How do you plan to hire only perfect programmers now and in the future
who will never subvert your intended business rules buy always writing
perfect application code? How do you plan on keeping people away from
QA and other tools that go directly to this disaster waiting to
happen?

[RRR]

On Nov 22, 4:41 pm, Erland Sommarskog <[e-mail]:

I don't know about your data model, but I would hold it as likely that
either you have an incorrect design, or you have a misconception of what
you want to do. I have ran into the situation that I wanted a partial
FK myself, but it's a very rare scenario.

A foreign key should reference the (primary) "key, the whole key and
nothing but the whole key, so help me Codd!"

[Ed Murphy]

Fritz Franz wrote:

OK, I understand. Yes, you ar right, the design of DB is not normalized
correctly. But for some reasons we decided to do it this way.

Please post the specific reasons, so that we can specifically
debunk them.

It is not that
important to have the FK defined in the DB, the applications will ensure the
integrity of the data.

Celko is right, this will fail as soon as one of the application
programmers makes a mistake. DRI should be enforced directly in
the DB. More complex business logic may be enforced at the
application layer, but you should still look for ways to
centralize it.

[Erland Sommarskog]

Fritz Franz (fr**********@hotmail.com) writes:

OK, I understand. Yes, you ar right, the design of DB is not normalized
correctly. But for some reasons we decided to do it this way. It is not
that important to have the FK defined in the DB, the applications will
ensure the integrity of the data.

An application cannot ensure data integrity. An application can add its
own checks, and sometimes it has to make messages user-friendly. But it
cannot ensure data integrity, because sooner or later someone will run
an UPDATE/INSERT/DELETE directly from SQL when the application is not
watching.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx