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
(colb)

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
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security

=====================================================
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: 210.50.131.97
| 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 10.40.2.250
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| We have been building databases for replication and therefore using
primary
| keys of type uniqueidentifier. The GUI is developed in Microsoft Access
2003,
| which doesnâ??t seem to allow us to insert NULL values into the foreign
keys
| (of type uniqueidentifier) that reference these primary keys. This means
that
| our referential integrity is compromised as we are forced to enter a
valid
| 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