[RESOLVED] Foreign Keys

[coco1985]

Hi,

I am making a database with multiple tables that will relate to each other by a clientid. So far I have 2 tables. I set the first table's clientid as a foriegn key to the clientid in the 2nd table. The clientid in the first table is a primary key with unsigned numbers and set to auto_increment. Each entry on that table will be a new client with a unique number. On the 2nd table, there will be multiple entries for the same client id. I'm not really sure how to set that one up so I just put it as the same data type (int) and put "unsigned."

Do I need to set the clientid in the 2nd table as a foreign key to the 1st table too?

[bradgrafelman]

If the "first" table is the table where the client information is stored, then you shouldn't define a foreign key in that table.

For example, if you had a "users" table and a "blog_posts" table, and you wanted to link blog posts to their owner, you'd create a foreign key in the blog_posts table to point to the user_id in the "users" table. You're doing it the other way around, which would suggest that a user belongs to a blog post (rather than the blog post belonging to a user).

[coco1985]

Oh I see. Ok, I dropped the foreign key on the first table and I tried to add it to the 2nd table instead, but an error message came up:

SQL query:

ALTER TABLE claims ADD FOREIGN KEY ( clientid ) REFERENCES tristem.checklist (
clientid
) ON DELETE CASCADE ON UPDATE CASCADE ;

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (tristem.#sql-8a8_1d0, CONSTRAINT #sql-8a8_1d0_ibfk_1 FOREIGN KEY (clientid) REFERENCES checklist (clientid) ON DELETE CASCADE ON UPDATE CASCADE)

[bradgrafelman]

Check the claims table for invalid data in the clientid column. MySQL won't allow you (by default) to add a foreign key on a table that contains invalid data for that foreign key. In other words, if you have items in the claims table with clientid's that don't already exist in the checklist table, then the constraint will fail and you'll get the above error message.

[coco1985]

Cool! It worked!!! Thank you so much!!! <3<3<3