UNIQUE FOREIGN KEY Constraint?!?! - Microsoft Access / VBA

Jonathan Scott via AccessMonster.com

I have an application who's backend has a relationship defined one to one. I
need to update the LIVE version of the database to reflect this for the new
version. How can I express such a foreign key constraint in DDL? I tried
creating a foreign key constraint, and then making a unique index on the
field in the foreign table, and vice versa, and neither has worked. My schema
diff utility tells me there is still a one to many relationship at the end of
the update.

Any help would be highly appreciated!
Jonathan Scott
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200508/1

Nov 13 '05 #1

Subscribe Reply

2094

Jonathan Scott via AccessMonster.com

I have tried to make a special command (non-DDL) that can be used to ask DAO
to make the Index unique.

Public Sub makeIndexUnique (dbConnection As Database, SQL As String)
Dim tableDefinition As TableDef
Dim indexDefinition As Index
Dim tableName As String
Dim indexName As String
Dim commandArgs As Collection

Set commandArgs = argumentsOfComm andAsCollection (SQL)
tableName = commandArgs(1)
indexName = commandArgs(2)
Set tableDefinition = dbConnection.Ta bleDefs(tableNa me)
Set indexDefinition = tableDefinition .Indexes(indexN ame)
indexDefinition .Unique = True
End Sub

This however does not work, throwing an error at the last line, saying the
index's unique property cannot be modified.

Any other possibilities? :'(
Jonathan Scott

Jonathan Scott wrote:

I have an application who's backend has a relationship defined one to one. I
need to update the LIVE version of the database to reflect this for the new
version. How can I express such a foreign key constraint in DDL? I tried
creating a foreign key constraint, and then making a unique index on the
field in the foreign table, and vice versa, and neither has worked. My schema
diff utility tells me there is still a one to many relationship at the end of
the update.

Any help would be highly appreciated!
Jonathan Scott

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200508/1

Nov 13 '05 #2

Similar topics

11195

Foreign key refer to unique constraint????

by: KULJEET | last post by:

foreign key also refer to unique constraint. (GREAT...) 1.then table that containt unique constraint act as master table????? 2.IS unique constraint will replace with primary key?? 3.Is unique constraint gives all fuctionality as primary key constraint??? thanks kuljeet pal singh

Oracle Database

16713

Difference between unique constraint and unique index?

by: aj | last post by:

DB2 WSE 8.1 FP5 Red Hat AS 2.1 What is the difference between adding a unique constraint like: ALTER TABLE <SCHEMA>.<TABLE> ADD CONSTRAINT CC1131378283225 UNIQUE ( <COL1>) ; and adding a unique index like:

DB2 Database

495

MS access - Unique table for several cash & bank accounts

by: Amos | last post by:

Dear Sirs I am trying to build a cash flow software, first I thought to build one table for each cash and bank account, but talking to some people they suggested me to build one unique table for all bank and cash accounts, putting one more column to identify each bank or cash account. The unique table has this columns structure: ID Date B/C account FinCode Description Debit Credit

Microsoft Access / VBA

1188

Interesting Unique Index Needed - Functional?

by: Dennis Gearon | last post by:

Given: CREATE TABLE Usrs( usr_id SERIAL NOT NULL PRIMARY KEY, usr VARCHAR(64) NOT NULL UNIQUE ); CREATE TABLE Emails( email_id SERIAL NOT NULL PRIMARY KEY, email VARCHAR(128) NOT NULL UNIQUE );

PostgreSQL Database

3006

Constraints to Guarantee unique across tables with foreign key?

by: Benjamin Smith | last post by:

I have two tables like following: create table attendancereport ( id serial unique not null, staff_id integer not null references staff(id), schoolyear varchar not null references schoolyear(year), students_id integer not null references students(id) ); // schoolyear.year in format "2003 - 2004".

PostgreSQL Database

14670

unique constraint and unique index

by: Laurence | last post by:

Hi there, How to differentiate between unique constraint and unique index? These are very similar but I cannot differentiate them? Could someone give me a hand? Thanks in advance

DB2 Database

3771

Using Foreign Key To Ref A Unique Index

by: ken_knee | last post by:

I have a PARENT/CHILD I'm attempting to attach another CHILD to. When I try to add a FOREIGN KEY to the 3rd table referring to CHILD1 (AGTERNS), I get error SQL0573N A column list specified in the references clause of constraint "RCAGTRNZ" does not identify a unique constraint of the parent table "VCSTSTNT.AGTERNS". Since CHILD1 has multiple constraints, a unique index and a foreign key, is there a gotcha that I'm not aware of? Thanks...

DB2 Database

4311

Can I create unique index on selected records ?

by: Alvin SIU | last post by:

Hi all, I have a table in Db2 v8 like this: Team Name Role ------ -------- --------------------- A Superman Leader A Batman Member A WonderWoman Member B Alvin Leader

DB2 Database

15064

[SQLServer] Violation of UNIQUE KEY constraint

by: rorajoey | last post by:

Violation of UNIQUE KEY constraint 'IX_surveyQuestions'. Cannot insert duplicate key in object 'dbo.surveyQuestions'. This might seem like a simple matter of trying to insert a row with ID=20 when there's already one with that ID, but the problem is a bit more complicated. The table is supposed to auto-increment the value for the primary key when a new record is inserted. But no matter what I do, I can't seem to insert more than one record...

Microsoft SQL Server

8253

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8189

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8497

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7182

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6116

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5570

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4192

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1802

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1499

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General