I have quite simple question. I am creating database administration tool, where user will be able to create relations between tables, but I am not sure if table order is important when creating relations.
For example: I have two tables 'user'(user_id(INT, PRIMARY KEY), name(VARCHAR), email(VARCHAR)) and 'post'(post_id(INT, PRIMARY KEY), content(TEXT), author(INT)).
When user has selected one column from both tables ('user'(user_id) and 'post'(author)) tool creates a query:
ALTER TABLE 'user' ADD CONSTRAINT 'user_id_to_author' FOREIGN KEY ('user_id') REFERENCES post('author')
but if user would select tables in different order ('post'(author) and 'user'(user_id)) then query would look like this:
ALTER TABLE 'post' ADD CONSTRAINT 'author_to_user_id' FOREIGN KEY ('author') REFERENCES user('user_id')
Is there any difference between both query’s and if so whats is it?
Advertisement
Answer
You are defining a one-to-many relationship, so the direction of the relationship does matter.
The table that is altered is the child table; many rows may refer to the parent table, through the primary key of the parent table.
For the example given here, a post belongs to a user, and many posts may have the same author, so you want:
ALTER TABLE post
ADD CONSTRAINT author_to_user_id
FOREIGN KEY (author) REFERENCES user(user_id)
Note that object names should not be surrounded with single quotes.
Trying to create the relationship the other way around does not make sense. It would imply that a user needs to have a post before it can be created.