|
|
Posted by Fritz Franz on 11/22/07 23:52
"Erland Sommarskog" <esquel@sommarskog.se> wrote
> 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
Navigation:
[Reply to this message]
|