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 :(