SQLTeam.com | Weblogs | Forums

How to insert in a table without a foreign key?


#1

Hi!
I'm a newbie in SQL and i have a question for you people. So, I have these two tables one "account" has an id(int primary key), email(nvarchar), password(nvarchar), u_id(int foreign key(user id)). The other one "user" has an id(int primary key), name(nvarchar), a_id(int foreign key(account id)). So my question is how do I insert a user if I don't have a foreign key(account id) created yet? I would use the following command:
INSERT INTO public.user(id, name, u_id) VALUES ('1', 'amanda', 'i did not created any account yet');

Could i insert a tuple with null value then insert an account tuple with null value too and then update both?


#2

Short answer, you can't (normally).

Longer answer: Insert a new row in the referenced table first (your userid table).

Still longer answer. Disable the foreign key constraint then insert your row. not recommended since it will stop referential integrity checks.


#3

You can insert a NULL value and the FK will be ignored.

But are you saying that user has to fk to account id and account has a FK to user id? That's not really a valid design. If they have only a single relationship, put the fk in one table or the other. If they have multiple, you need a separate table to store the linking ids.