Problem with keys

[mr_scooby]

Hi, I have 2 tables that I have made using sql create table which seem to work ok until I try to insert data into the 2nd table then it throws a: didn't add 1 record due to key violations.

When I use the design wizard and do exactly the same thing but without using sql it works fine.

table 1 sql- user table

CREATE TABLE User (
userEmailAddress varChar(255) not null PRIMARY KEY, 
userFirstName varChar(100) not null,
userLastName varChar(100),
userStatus varChar(1) not null,
userNickName varChar(40),
userMifrenzPassword varChar(10) not null,
UserEmailPassword varChar(10) not null,
userDOB date
)

table 2 sql - email options table

CREATE TABLE Email_Options (
emailNumber number not null PRIMARY KEY,
userEmailAddress varChar(255) not null, 
emailProvider varChar(255) not null,
popServerAddress  varChar(255) not null,
smtpServerAddress  varChar(255) not null,
popServerPort varChar(6) not null, 
smtpServerPort varChar(6) not null,
FOREIGN KEY(userEmailAddress) references user(userEmailAddress)
)

have spent ages trying to find a solution and just don't know enough

Like I said everything works ok when I do it using the design wizard and creating the relationships manually, however when I use sql I get exactly the same physical layout and view in the view and relationship view, except I get the cannot add record due to key violation.

I get the message when updating the email options table, updating the user table works fine.

Any ideas?? I'm lost :(

[sknake]

What database are you using?

CREATE TABLE User ( -- reserved word
userEmailAddress varChar(255) not null PRIMARY KEY, 
userFirstName varChar(100) not null,
userLastName varChar(100),
userStatus varChar(1) not null,
userNickName varChar(40),
userMifrenzPassword varChar(10) not null,
UserEmailPassword varChar(10) not null,
userDOB date -- There is no "date" datatype
)

CREATE TABLE Email_Options (
emailNumber number not null PRIMARY KEY, --There is no "number" data type
userEmailAddress varChar(255) not null, 
emailProvider varChar(255) not null,
popServerAddress  varChar(255) not null,
smtpServerAddress  varChar(255) not null,
popServerPort varChar(6) not null, 
smtpServerPort varChar(6) not null,
FOREIGN KEY(userEmailAddress) references user(userEmailAddress) -- Has a reserved word, should be [user]
)

Your create statement has datatypes that don't exist and "User" is a keyword that has to be used as [User] in MSSQL. Is this another type of database...?

[mr_scooby]

sql and access, solved the problem. cheers