₦airaland Forum

Hi pals,
am currently working on a dbase and i need to declare a foreign key such dt an alteration in table A will take effect in table B. Please how do i write d code and putting the necessary constraints? Thanks in anticipation.

I don't think that can be achieved with foreign keys. Infact what you want to do beats the need for a relational database. A relational database is one where tables relate to each other (using primary/foreign keys) and no two tables should contain the same chunk of information. In clear terms, you should build your database in such a way that information is central to one place and not redundant.

You can do some Google'ing and also await other responses from people who may have something better to offer.

What is the alteration, how does the data in table A relate to table B. What is the tables all about?
we need more details

Depending on what you want to achieve,you can use a stored procedure/function,a trigger or use the last insert id value from a previous insert query to update or do some other processing.send a mail to labegan at gmail dot com,with full details of what you want to do and probably your code structure

Assuming I understand what you mean and assuming you are using mySQL:

When you create a primary key in one table and a foreign key in another, the 2 tables are related. Now, if you delete from A, without a constraint, you will leave B "hanging" or "orphaned" in that the relationship b/w a particular row in A and a particular row in B is no more. To enforce the situation where what you update in A applies to B or what you delete in A applies to B, you need to use "ON DELETE CASCADE" and/or "ON UPDATE CASCADE"

An example:

CREATE TABLE categories (
category_id int unsigned not null primary key,
name varchar(255) default null
);
CREATE TABLE products (
products_id int unsigned not null primary key,
name varchar(255) default null
);

CREATE TABLE categories_products (
category_id int unsigned not null,
products_id int unsigned not null,
FOREIGN KEY (category_id) REFERENCES categories (category_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES products (products_id)
ON DELETE CASCADE
ON UPDATE CASCADE,

);

Again, the above assumes I understand what you need. Good luck!

Thank u all 4 ur contributions, it has really helpd alot... Yewa-ti-de seems to get d true picture, thanks 4 ur contribution...
A bow to all d oga in d house...