Setting Relationship with CONSTRAINT clause causing error?

[Özden Irmak]

Hello,

I'm having a difficulty on setting a relationship with a sql CONSTRAINT
clause.

I get an error like 'Cannot create relationships to force referential
integrity.Existing data in table POS_TRAN_LINES violates this.'. My sql
clause is as follows :

ALTER TABLE [POS_TRAN_LINES] ADD CONSTRAINT [Reference18] FOREIGN KEY
([WAITER_ID]) REFERENCES [STAFF] ([ID])

I can set the reference inside Access's IDE when 'Enforce Referential
Integrity' is unchecked in edit relationships box. How can I mention this
option within this sql clause?

Thanks in advance,

Özden

 

[Tim Ferguson]

I get an error like 'Cannot create relationships to force referential
integrity.Existing data in table POS_TRAN_LINES violates this.'. My sql
clause is as follows :

This is because you have a Pos_tran_lines.Waiter_id value that does not
exist as a valid Staff.ID value, so you must clean your data first.

I can set the reference inside Access's IDE when 'Enforce Referential
Integrity' is unchecked in edit relationships box. How can I mention this
option within this sql clause?

Leaving the Ref Integrity box unchecked does not do anything for the data
model: it's only a hint for the query design grid later about where to put
the default joins if you happen to have the same two tables open together.
It's not a relationship, so there is no integrity checking.

Hope that helps


Tim F

 

[Özden Irmak]

Hello Tim,

I may be miss described the thing I want to achive so I'll ask in a
different way. Do you have any sample SQL clause which will make references
on two fields on two different tables without referencial integrity?

Thanks,

Özden

 

[Tim Ferguson]

Do you have any sample SQL clause which will make references
on two fields on two different tables without referencial integrity?

Perhaps it was me who did not explain clearly. Without referential
integrity there is no relationship. Relationships are methods of
constraining what can go in a field, what can be deleted, and so on -- they
are a method of maintaining the integrity of data. You cannot put a student
on course GA092 if there is no such course; and you cannot delete course
HI104 while studentss are still registered on it.

Putting a line in Access' relationships window and leaving the Ref Int box
clear achieves _absolutely nothing_. Read that again. There is no
constraining, no linking, no protection of the data. Put all the students
on course XZ999, sure, why not, the DBEngine won't know any better. The
only thing that happens is when you drop the tables into the query design
window, because Access will recognise them and put a join in for you,
saving at least four seconds' work. And half the time it'll be the wrong
join anyway, so you'll waste that much deleting it anyway.

Hope that makes a bit more sense.
B Wishes


Tim F

 

[Özden Irmak]

Hello Tim,

Although you say this, when I investigate the database with ADOX I can see
that relation!!!!!! I can see it in the keys collection which points out
that relation.

And you may wonder why i'm so interested about this, even you've told so
much about it's useless value, it's because of my tool, which compares and
synchronizes MSAccess database structures. This type of relationship makes
some trouble on my generated merge scripts so i somehow have to resolve it.

Anyway, thanks for your attention and the valuable info you've given so
far...

Best Regards,

Özden

 

[Dirk Goldgar]

Hello Tim,


Although you say this, when I investigate the database with ADOX I
can see that relation!!!!!! I can see it in the keys collection which
points out that relation.

And you may wonder why i'm so interested about this, even you've told
so much about it's useless value, it's because of my tool, which
compares and synchronizes MSAccess database structures. This type of
relationship makes some trouble on my generated merge scripts so i
somehow have to resolve it.

Although the Jet database structure allows you to create an unenforced
relationship and store that information in the database (solely for the
purpose of drawing join lines in the relationship window and
auto-creating joins in the query designer), a SQL CONSTRAINT clause can
only define an enforced relationship. Therefore, you will not be able
to script an unenforced relationship as a SQL CONTSTRAINT clause. The
unenforced relationship is an Access thing, and is not (AFAIK) defined
by the SQL standard.

 

[Özden Irmak]

Hello Dirk,

Thanks for this valuable info which I was looking for...

It seems that I'm going to create those kind of relations via ADOX...

Thanks again,

Özden