Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

ACCESS SQL Query to Create Relationship

I had two tables Customer & Source which are linked by SourceID. The Join was an OuterJoin. There was no referential integrity between them.

In the Source Table, SourceID datatype was Number. I would like it change to AutoNumber Without any problem to existing data.

I am using ADO to connect to MDB file.

I did this one using Query...

I removed the relationship between the Customer and Source.
Created a TempTable with Source Table Structure with AutoNumber.
Deleted the Source Table
Renamed the temp table to Source

Till this it is working fine. But when I try to bring back the relationship,  it has a referntial integirty problem with Customer table and query fails. When relationship is added exixting data are checked and the query fails.

How can u create the relationship between tables with NO REFERENTIAL INTEGRITY and with OUTER JOIN as the default join type. How do i create a query for this .

 I need all this one in a query, since this will be executed together with my aplication and all the client database will update their database.
0
nishikanth
Asked:
nishikanth
  • 2
  • 2
2 Solutions
 
leonstrykerCommented:
Lets see your query.

Leon
0
 
nishikanthAuthor Commented:
Convert Number to Autonumber

'First Drop Relationship between Customer and Source
           
           strSQL = "Alter Table Customer Drop Constraint SourceCustomer"

'Create the New Table   - Temporary Table ---
           
            strSQL = "Create TABLE Source1 (SourceName Text(50) Not Null)"

'Transfer Contents from OLD Table to TEMP Table
           
            strSQL = "Insert Into Source1(SourceName) Select SourceName from Source"
           
'Remove the Old Table
           
            strSQL = "Drop TABLE Source"
           
'Create the Table   - With Original Name + AutoNumber ---

            strSQL = "Create TABLE Source (SourceID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY , SourceName Text(50) Not Null)"

'Transfer Contents from Temp Table to New Table
           
            strSQL = "Insert Into Source(SourceName) Select SourceName from Source1"
            g_clsAdo.ExecuteSQL (strSQL)

Bring Back the RelationShip            
            strSQL = "ALTER TABLE Customer ADD CONSTRAINT  SourceCustomer FOREIGN KEY (SourceID) REFERENCES Source(SourceID)"

'''' CANNOT CRREATE REALTIONSHIPO OVER HERE
Cannot create relationships to enforce referential integrity.  Existing data in table 'Customer' violates referential integrity rules in table 'Source'.            

I need to bring back the rlnship with JoinType OUTER JOIN.
0
 
leonstrykerCommented:
0
 
nishikanthAuthor Commented:
How can u create the relationship between tables with NO REFERENTIAL INTEGRITY and with OUTER JOIN as the default join type. How do i create a query for this.


0
 
ppswetCommented:
Try disabling the foreign key constraint to create a non-referential relationship.

ALTER TABLE Customer DISABLE CONSTRAINT SourceCustomer FOREIGN KEY (SourceID) REFERENCES Source(SourceID)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now