auto creat table row when other table add row

Question

i have 2 table 1 persons(email,name) 2 location(email)

now when i add a person to persons table i want the db auto creat a row in location whit the person email (that i just add to persons).

i try to do it white PRIMARY KEY, and FOREIGN KEY but no succses.

thank you all.

this what i try :

       CREATE TABLE Persons
        (
       Email char(50) PRIMARY KEY,

           First_Name char(50))  



      CREATE TABLE location
         (
         email char(50),
         FOREIGN KEY (email) REFERENCES Persons(email)
           )

but when i add to persons person its not added to location too.

score 3 · Answer 1 · edited May 23 '17 at 11:55

3

You would need a trigger to do this not a foreign key.

The FK just enforces that a row cannot be inserted in location without a corresponding record in Persons

But email is a very wide choice for a primary key as well as unstable (see is email address a bad primary key) and the whole design seems odd.

What is the location table for? Does this have a 1:many relationship with Persons? Where are the other columns? What is the PK of location?

edited May 23 '17 at 11:55

Community

1
1

answered Sep 19 '11 at 21:22

Martin Smith

438,706
87
741
845

first of all thanks i will see about this trigger, and this not my real tables it's just for the example. :-) – dan Sep 19 '11 at 21:29
@dan - What RDBMS are you using? (e.g. MySQL, SQL Server, Oracle...) – Martin Smith Sep 19 '11 at 21:30
@dan Ah If it was one I was familiar with I would have put some example trigger code in my answer but it's not! – Martin Smith Sep 19 '11 at 21:32

auto creat table row when other table add row

1 Answers1