Import DDL database structure to Access database

[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

 

[Pat Hartman]

The relationship in your picture is WRONG. CustomerID should be the PK of tblCustomers and CustomerID should be the FK in tblInvoices. LastName and FirstName should NOT be in tblInvoices.

Also,
-- it is poor practice to include spaces or other punctuation symbols in your object names
-- naming all your primary keys as PK just obfuscates your schema
-- If a table has an autonumber, the autonumber should be the PK. Otherwise, there is no reason for the autonumber to exist. So if you have CustomerID (an autonumber) and CustomerNum (a candidate key), CustomerID should be the PK and is what all relationships will be based on and CustomerNum (the visible customer number) is just data. It should be indexed, probably with a unique index in the customer table to prevent duplicates and to facilitate searching since searching will almost certainly be on CustomerNum.

 

[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.

 

[Pat Hartman]

Have you tried?

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_LastName PRIMARY KEY ([Last Name], [First Name]) REFERENCES tblCustomers ([Last Name], [First Name])

 

[jaryszek]

Thank you so much for your help,

Still didnt work.
I used your sql Pat:

sqlS = "ALTER TABLE tblInvoices ADD CONSTRAINT FKLastName PRIMARY KEY ([LastName], [FirstName]) REFERENCES tblCustomers ([LastName], [FirstName])"

But i am getting syntax error in ALTER statement...
Maybe it is not working at all or it is crazy SQL

Best,
Jacek

 

[Pat Hartman]

I wasn't saying it would work. I asked you to try it. I can't find any example of the correct syntax. I'll keep looking.

 

[jaryszek]

Thank you. I tried. Maybe microsoft didnt implement this feature?

 

[The_Doc_Man]

This is a delicate issue. There is almost never a time when you would seriously implement a 1-to-1 relationship in any table, manually or through code. With very few exceptions, having such a relationship makes it questionable as to whether the tables involved should be separate tables or one table. Seeking to make a 1/1 relationship makes it almost impossible to enforce relational integrity through any SQL actions that would alter one but not the other of the tables. It would become nearly unusable from a purely procedural viewpoint.

And you should note that there is nothing wrong with having a 1/many relationship between the tables if you put a separate index constraint on the second table so that you keep it unique. If there is a specified order in creating records in the two tables, put the one that is created first as the "ONE" side of the 1/many relationship. Then the other table is the MANY side. You do this because "MANY" includes "NONE" - so you can create data for the ONE side first and the MANY side second. Then just constrain the PK on the MANY side to remain unique. Everything will still work OK

 

[jaryszek]

thank you The_Doc_Man.

I have even issues with creating one to many relationships between composite keys.
I am getting errors.

Best,
Jacek

 

[Minty]

It's because a single field isn't unique in a composite index. Plain and Simple.
You can't set a constraint on a non-unique field to a non-unique field.

 

[jaryszek]

i have composite keys as primary keys in both tables. What i know is that primary key is unique always from design. And if i am using composite primary key they have already index created.

i do not undetsrand what you mean Minty

 

[Minty]

So you can create the constraint correctly yes?

ALTER TABLE YourTableName
ADD CONSTRAINT
ConstraintName UNIQUE NONCLUSTERED
(
    Column01,
    Column02,
    Column03
)

This page gives you a complete one to one example https://www.tech-recipes.com/rx/56738/one-to-one-one-to-many-table-relationships-in-sql-server/

 

[jaryszek]

Thanks:

the error i am getting is like you see.
Not working still ech.

sqlS = "ALTER TABLE tblCustomers " & _
"ADD CONSTRAINT " & _
"CustomerCon UNIQUE NONCLUSTERED" & _
"(" & _
    "LastName," & _
    "FirstName" & _
")"

but it worked with out NONCLUSTERED - maybe this is SQL Server only?

And what next ?

why primary keys can not be used as joined fields?

Jacek