SQL statement for linking table

I have 2 tables

collection
==========
collection_id (primary key)
collection_name
collection_type

collectable
===========
collectable_id(primary key)
collectable_name
collectable_type
collectable_collection

What is the SQL statement so that collectable_collection can only take as input collection_id? Sorry i开发者_如何学Cf this is too simple. Is this a change specific to the column: collectable_collection or to the table: collectable?

I am using a MySQL database .

It'd be better if you implement this in the application level than with the SQL. Just check if the id to be inserted in the collectable_collection is a collection_id and then perform the insert operation, if the collectable_collection has more than one collection_id's.

EDIT

If the collectable_collection has only one id in it, then you can use Foreign Key. Check the manual, for more info on how to use it

use coolection-id as a foreig key in the collectable table this will do insertion only when collection_id would be persent in the collection table.

And if u want to insert multi values then make a log table for collection and that will be use to get collection values in collectable table.

You could use a database management tool, such as the official MySQL Workbench. There you can use the user interface to make any change to the database.

If you wish to use SQL instead, you could use the following:

ALTER TABLE collectable
ADD CONSTRAINT FK_collectable_collection
FOREIGN KEY (collectable_collection)
REFERENCES collection (collection_id)

Optionally, you could add ON DELETE CASCADE or ON DELETE SET NULL to automaticly remove the affected rows in the collectable table or set the references to NULL when a row in the collection table are removed.