SQL, Primary key and Foreign Key?

Question

Hi all !
I'm building a basic php based website, and of course i use SQL database to make a login system.
Now suppose i have two table (Users, News)
Users -> usersID, name....and so on
News ->newsID, usersID, text....and so on

Now usersID in Users table is the Primary key..While i want the usersID in News table to be the Foreign Key.
I wrote down the foreign key in SQL " FOREIGN KEY (usersID) REFERENCES Users(usersID) "...
Now i make an account, it doesn't copy the usersID value from the original users table...in News table there is nothing..I want the value in Users->usersID = News-
It can't work, any ideas?

Answer 1

I'm not sure if I understand your question correctly, but creating a foreign key doesn't create any copying of data at all. In your foreign key example, it simply means that when you add an entry to your News table, the usersID that you specify must exist in the Users table.



You can also specify rules on what should happen when a usersID in the Users table is updated or deleted. For example, if you specify 'ON DELETE CASCADE' as the rule, when a usersID in Users table is deleted, all rows with that usersID in table News will also be deleted automatically.

Answer 2

As Robin states, defining the foreign key constraint won't automatically insert the associated rows into the other table for you - all it does is define the rule that the value must exist in the other table that the foreign key references. If you want something done for you automatically, about the only way you can do so is define a trigger.

Answer 3

Try doing a join:



FROM Users a

LEFT OUTER JOIN News AS b

ON (a.usersID = b.usersID)

Answer 4

Have you tried joining tables?