3

My problem:

Although my database is rather complex I'm just including the car and customer tables.

So I have a customer table with the relevant details and the customer_id as the primary key.

Now, within the car table i want to have a foreign key linked to the customer_id, so within the car table i can see who owns what. The problem is what if the car hasn't been brought yet?

As far as i can see the foreign key must have a customer_id to be added to the table.

Am i missing something here or am i way off track, i read something about linked tables? Can a foreign key be null?

One other thing, how do i set up rules like:

'A car can only have 1 manufacturer (which is a separate table linked with a fk)'? I'm guessing that's done with php correct?

If anyone wants a pic of the databases current ERD, i have one.

Fernando Carvalhosa
  • 1,098
  • 1
  • 15
  • 23
Joel
  • 278
  • 2
  • 5
  • 10
  • Yes, FK can be have NULL. For your second question `A car can only have 1 manufacturer` ... you can use `CHECK CONSTRAINT` – Rahul May 28 '14 at 01:29

1 Answers1

0

For the first question, check this reference: Can a foreign key be NULL and/or duplicate?

Your question will be flagged as a duplicate because of it.

About the second one:

The relationship can and should be handled by both php and database for safety purposes (especially the last one). That guarantees that your data won't get corrupted in case someone "forget" about the relationship.

They are just table's columns that hold an ID of another table. You could use a "String", "Date" or whatever you think of to relate 1 entity to another, but the safest and faster option would be just using another entity's ID.

Why?

Primary_keys must be unique, that guarantees you have no duplicates (think of them as a universal IDentification inside your context), and foreign_keys must belong to another entity in another table. You can't use a value that doesn't belong to any entity in the other table nor delete one entity who has it's ID in some other foreign_key in some other entity (you will have to delete all the "childrens" before deleting the "parent", otherwise they would be "orphans")

More about those keys is in that link I have posted above and any other rich references you can find here and googling it

Of course you could just ignore it and don't handle it at all, but it's an awful practice and it won't be recommended. and I will hunt you down if I find out you did this... hahah

Community
  • 1
  • 1
Fernando Carvalhosa
  • 1,098
  • 1
  • 15
  • 23