RE: Table Relationship Integrity - Null Values

Hello Alan,

I was not able to reproduce the issue on Access 2003 SP2/WinXP

1. Create tablea/tableb

create table tablea (col1 uniqueidentifier primary key, colb
uniqueidentifier null, a int, b varchar(20))

create table tableb (colb uniqueidentifier primary key default newid())

alter table tablea add constraint fk1 foreign key (colb) references tableb

select * from tablea

insert tablea values (newid(), null, 2,'ddd')

insert tablea values (newid(), newid(), 1, 'ddd') --I received error about
foreign key

sp_help tablea

select * from tablea

2. created a ADP from the existing table, I was able to input new item by
removing the guid created auotmatically in colb when editing tablea.

Best Regards,

Peter Yang
Microsoft Partner Online Support

Get Secure! -

When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.

| Thread-Topic: Table Relationship Integrity - Null Values
| thread-index: AcaF7SXQqRB3+gh7T5G9+Pes3CihSg==
| X-WBNR-Posting-Host:
| From: =?Utf-8?B?c3RlZWw=?= <steel@xxxxxxxxxxxxxxxx>
| Subject: Table Relationship Integrity - Null Values
| Date: Thu, 1 Jun 2021 19:35:01 -0700
| Lines: 17
| Message-ID: <A5F082EF-4B52-400F-8A9E-6CABCBBFE742@xxxxxxxxxxxxx>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| Newsgroups: microsoft.public.access.adp.sqlserver
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.adp.sqlserver:24723
| NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
| We have been building databases for replication and therefore using
| keys of type uniqueidentifier. The GUI is developed in Microsoft Access
| which doesnâ??t seem to allow us to insert NULL values into the foreign
| (of type uniqueidentifier) that reference these primary keys. This means
| our referential integrity is compromised as we are forced to enter a
| value into the foreign key, or else remove the relationship from the SQL
| table.
| How can we assign NULL values to uniqueidentifier fields in Access 2003
| projects? We saw one help that suggested an explicit assignment in the
| BEFOREUPDATE event, but this doesnâ??t appear to be working for us.
| Thanks.
| --
| Regards,
| Alan


Relevant Pages