Import DDL database structure to Access database

[jaryszek]

thank you very much Pat.

ALTER TABLE tblInvoices
   ADD CONSTRAINT FK_LastName FOREIGN KEY ([Last Name])
      REFERENCES tblCustomers ([Last Name])

What i am doing wrongly ?

Database example in attachment

Jacek

 

[CJ_London]

The error message tells you where the problem is. tblCustomers ([Last Name]) is not unique but part of a composite PK. So you cannot create a relationship. Make lastname a unique field and it should then work - but then you can only have one customer with a last name of 'Smith'.

Better to create a unique primary key (autonumber) and link to that

 

[jaryszek]

The error message tells you where the problem is. tblCustomers ([Last Name]) is not unique but part of a composite PK. So you cannot create a relationship. Make lastname a unique field and it should then work - but then you can only have one customer with a last name of 'Smith'.

thanks i checked it using steps:
1) i created primary key manually on [Last Name] in both tables and still have:

2) Better to create a unique primary key (autonumber) and link to that - i know from other topic but i would like to learn to do this with single primary key and composite primary key - just in case becasue my model has composite keys inside

Best,
Jacek

 

[jaryszek]

Ok i found the method to relate FK to PK:

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_Test FOREIGN KEY ([PK]) REFERENCES tblCustomers ([PK])

It create one to many relationship:

I have question:
1) How to make this 1:1 relationship from SQL level?

2)

and how to make this kind relationship using sql ?

Please help,
Jacek

 

[Minty]

That is a poor example, you should store the CustomerID in table invoices.
In fact, surely the CustomerID should be the PK in tblCustomers?

 

[jaryszek]

Thank you Minty for getting involved!

Sorry for example, it is poor but i do not need design it properly and i cant share fields name here.
Can you please help with only technical solution, i.e. providing SQL which solve the issues from #33 ?

Best Wishes,
Jacek

 

[Minty]

Field Names would not be giving away your trade secrets I'm sure unless you called them BankAcctNum3215798 SortCode 32-65-98

In reality, you would not be able to use such a join as you can never have a null on the Primary key, and during the insertion of a new record, one or other of those joins would be to a null.

You cannot enforce such a one to one join. Think about it - one table wouldn't have an entry or the other one. Impasse.

 

[CJ_London]

agree with Minty - having created your tables, have you tried entering some data to test it?

 

[jdraw]

Agree with minty and CJ. I think you should provide meaningful data when seeking help/advice even if you have to mock up an example. People here are not trying to "steal your data/secrets" - in fact they don't care about your real data. Use test data to confirm/resolve an issue/solution, then apply it to your "real database".

 

[jaryszek]

Hello,

thank you Guys.
Minty awesome explanation.
Ok so no problem with this.
But still would be nice how to create SQL 1:1 - just to know how to solve this technically.

Plus sql for composite key 1 to many relationships plus 1 to 1 composite keys, these relationships are working:

it is curious that 1:1 with only 2 primary keys is not working but for composite key is working...

Thanks for help,
Jacek

 

[Minty]

I am not sure you can create that relationship in code.
Have a read here https://docs.microsoft.com/en-us/of...relationships-between-tables-using-access-sql

 

[jaryszek]

You mean in code?
I could use VBA and ADO statements but i have to know what to pass

I tried link attached but i failed - please see post #33 :

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_Test FOREIGN KEY ([PK]) REFERENCES tblCustomers ([PK])

but this is relating only 1 to many...

Please help somebody how to write this.

Jacek

 

[Minty]

About halfway down that page, it describes setting up a one to one constraint on one field (Shipping table to customer table)

I have no idea if you could include two fields in it. I doubt it.

 

[jaryszek]

Thank you Minty.

i am trying with this all the time but i have only errors returned...

Best Wishes,
Jacek

 

[jaryszek]

Thank you Pat for good lesson for me.

I know that relationship is wrong.
I only need technical solution how to write Access SQL to create composite key relationship plus 1 to 1 relationship.
And if this is possible?

Best
Jacek

 

[Minty]

Thank you Pat for good lesson for me.

I know that relationship is wrong.
I only need technical solution how to write Access SQL to create composite key relationship plus 1 to 1 relationship.
And if this is possible?

Best
Jacek

The Link I posted in post #40 has examples of both of the above, what have you tried and what is the error?

 

[jaryszek]

Hi Minty,

as i wrote before i tried:

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_Test FOREIGN KEY ([PK]) REFERENCES tblCustomers ([PK]) --> worked

and tried:

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_LastName FOREIGN KEY ([Last Name])
REFERENCES tblCustomers ([Last Name]) -- no unique index found

and tried:

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_LastName PRIMARY KEY ([Last Name]) REFERENCES tblCustomers ([Last Name])
ADD CONSTRAINT FK_FirstName PRIMARY KEY ([First Name]) REFERENCES tblCustomers ([First Name]) -->> syntax error in SQL statement

ALTER TABLE tblCustomers
DROP CONSTRAINT PrimaryKey --> worked.

And some others but it didnt work for me...

Jacek

 

[Galaxiom]

Refer to the SQL DDL Reference. Constraint Clause

 

[jaryszek]

Hi,

thank you for the clue. I used VBA and DAO engine.

This worked for me but creating many to one:

 Sub CreateTableX1()

Dim dbs As Database
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = CurrentDb
    
    On Error Resume Next
    sqlS = "ALTER TABLE tblCustomers DROP CONSTRAINT NewOne"
    dbs.Execute sqlS
    On Error GoTo 0
    
    sqlS = "ALTER TABLE tblCustomers ADD CONSTRAINT NewOne FOREIGN KEY (LastName) REFERENCES tblInvoices (LastName) "
 
    ' Create a table with two text fields.
    dbs.Execute sqlS
 
End Sub

When want to create 1 to 1 i get error "Syntax error in ALTER statement"
This is what i tried:

 Sub CreateTableX1()

Dim dbs As Database
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = CurrentDb
    
    On Error Resume Next
    sqlS = "ALTER TABLE tblCustomers DROP CONSTRAINT NewOne"
    dbs.Execute sqlS
    On Error GoTo 0
    
    sqlS = "ALTER TABLE tblCustomers ADD CONSTRAINT NewOne FOREIGN KEY (LastName) REFERENCES tblInvoices (LastName) "
 
    ' Create a table with two text fields.
    dbs.Execute sqlS
 
End Sub

and for SQL to create composite relationship i am getting stil the same error:

 sqlS = "ALTER TABLE tblCustomers ADD CONSTRAINT NewOne FOREIGN KEY (FirstName,LastName) REFERENCES tblInvoices (FirstName,LastName) "

"No Unique index found for the referenced field of primary table"

Please help anybody,
Jacek

 

[Minty]

You would have to have created the primary constraint on tblInvoices first, on BOTH fields.
The error code is telling you the issue.

In your code, you are only creating the initial constraint on one field.