Automatically inserting foreign keys

  • mattysut57

    Grasshopper

    Points: 10

    Hi, i have to build a database for my university coursework, i have 5 tables and 1 of them has 2 foreign keys connecting to 2 other tables. I have it set out sort of like this,

    Table 1(
    Primary Key#1
    );
    Table 2(
    Primary Key#2
    );
    Table 3(
    Foreign Key#1
    Foreign Key#2
    );
    Is there any way to make it so inputting a key into both primary keys in table 1 and 2 will automatically input the same key into the table 3 foreign keys?

    My actual code if needed is below;

    CREATING THE TABLES
    SET FOREIGN_KEY_CHECKS=0;
    CREATE TABLE Suspect(
    SuspectID int auto_increment PRIMARY KEY,
    FirstName varchar(255),
    SecondName varchar(255),
    Address varchar(255),
    Gender varchar(255),
    Age_Range varchar(255),
    Self_Defined_Ethnicity varchar(255),
    Officer_Defined_Ethnicity varchar(255)
    );

    CREATE TABLE Conviction(
    CrimeID int auto_increment PRIMARY KEY,
    Crime_Type varchar(255),
    Last_Outcome_Catagory varchar(255),
    Date date,
    Outcome_Linked_To_Object_Of_Search varchar(255),
    Outcome varchar(255),
    Removel_Of_More_Than_Just_Outer_Clothing varchar(255),
    SuspectID int NOT NULL,
    OfficerID int NOT NULL,
    FOREIGN KEY (SuspectID) REFERENCES Suspect(SuspectID),
    FOREIGN KEY (OfficerID) REFERENCES Officer(OfficerID)
    );

    CREATE TABLE LocationOfConviction(
    Longitude int,
    Latitude int,
    Location varchar(255),
    LSOACode int,
    LSOAName varchar(255),
    SuspectID int PRIMARY KEY REFERENCES Suspect(SuspectID)
    );

    CREATE TABLE Officer(
    OfficerID int auto_increment PRIMARY KEY,
    FristName varchar(255),
    SecondName varchar(255),
    Reported_By varchar(255),
    Part_Of_Policing_Operation varchar(255),
    Legislation varchar(255),
    Object_Of_Search varchar(255)
    );

    CREATE TABLE Prosecution(
    OfficerID int,
    SuspectID int,
    FOREIGN KEY (OfficerID) REFERENCES Officer(OfficerID),
    FOREIGN KEY (SuspectID) REFERENCES Suspect(SuspectID),
    Prosecuted varchar(255)
    );
    CONVICTION TABLE (THE ONE WITH THE FOREIGN KEYS)
    INSERT INTO Conviction VALUES(
    '',
    'Violence',
    'Suspect Arrested',
    '2017-09-00',
    'FALSE',
    'Suspect Arrested',
    'FALSE',
    '',
    ''
    );
    OFFICER TABLE (TABLE 1 WITH FIRST PRIMARY KEY)
    INSERT INTO Officer VALUES(
    '',
    'Joe',
    'Newton',
    'Lancashire Constabulary',
    '',
    'Misues of Drugs Act 1972 (Section 23)',
    'Controlled Drugs'
    );
    INSERT INTO Officer Values(
    '',
    'Sam',
    'Webster',
    'Lancashire Constabulary',
    '',
    'Police and Criminal Evidence Act 1984 (Section 1)',
    'Article for use in theft'
    );

    INSERT INTO Officer Values(
    '',
    'Cameron',
    'Singleton',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Stolen Goods'
    );

    INSERT INTO Officer Values(
    '',
    'Daniel',
    'Shaw',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Stolen Goods'
    );

    INSERT INTO Officer Values(
    '',
    'Ayden',
    'Collins',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Article for use in theft'
    );

    INSERT INTO Officer Values(
    '',
    'Charlotte',
    'Whatron',
    'Lancashire Constabulary',
    '',
    'Misues of Drugs Act 1971 (Section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'James',
    'Fisher',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Article for use in theft'
    );

    INSERT INTO Officer Values(
    '',
    'Michael',
    'Schofield',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Daniel',
    'Connelly',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Stolen goods'
    );

    INSERT INTO Officer Values(
    '',
    'Lawrence',
    'Andrew',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Neil',
    'Mullen',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Kiera',
    'Steadman',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Vlad',
    'Harper',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Roy',
    'Queen',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Oliver',
    'Harper',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Offensive weapons'
    );

    INSERT INTO Officer Values(
    '',
    'Stewart',
    'Allman',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Cillian',
    'Velez',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Ebony',
    'Beard',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Stolen goods'
    );

    INSERT INTO Officer Values(
    '',
    'Reagan',
    'Prosser',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Mohammod',
    'Palmer',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Darleene',
    'Povey',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Article for use in theft'
    );

    INSERT INTO Officer Values(
    '',
    'Roger',
    'Stein',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Article for use in theft'
    );

    INSERT INTO Officer Values(
    '',
    'Jimmy',
    'Hnedrix',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Stolen goods'
    );

    INSERT INTO Officer Values(
    '',
    'Lorraine',
    'Allison',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Stolen goods'
    );

    INSERT INTO Officer Values(
    '',
    'Billie-Joe',
    'Armstrong',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Stolen goods'
    );

    INSERT INTO Officer Values(
    '',
    'Jackie',
    'Coulson',
    'Lancashire Constabulary',
    '',
    'Polcie and Criminal Evidence Act 1982 (Section 1)',
    'Stolen goods'
    );

    INSERT INTO Officer Values(
    '',
    'George',
    'Hester',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'Lucy',
    'Regan',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer Values(
    '',
    'James',
    'Conway',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled drugs'
    );

    INSERT INTO Officer VALUES(
    '',
    'Lincoln',
    'Burrows',
    'Lancashire Constabulary',
    '',
    'Misuse of Drugs Act 1971 (section 23)',
    'Controlled Drugs'
    );

    SUSPECT TABLE (TABLE 2 WITH SECOND PRIMARY KEY)
    INSERT INTO Suspect VALUES(
    '',
    'Anthonie',
    'Watkins',
    '48 Hindlead Road',
    'Male',
    '25-34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'Asian'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Jo',
    'Taylor',
    '181 Maidstone Road',
    'Female',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Mark',
    'Tinsley',
    '82 South Western Terrace',
    'Male',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Adam',
    'Donald',
    '80 Stamford Road',
    'Male',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Matthew',
    'Cassell',
    '71 London Road',
    'Male',
    '18-24',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Chardonnay',
    'Whitworth',
    '69 Fulford Road',
    'Female',
    '18-24',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Keenan',
    'ONeil',
    '72 Whitchurch Road',
    'Male',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Reece',
    'Burke',
    '29 Oxford Road',
    'Male',
    '16-24',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Adeel',
    'Mcmanus',
    '91 Henley Road',
    'Male',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Oskar',
    'Chavez',
    '18 Stroude Road',
    'Male',
    '25-34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Nathaniel',
    'Milne',
    '114 St Omers Road',
    'Male',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Theresa',
    'Phillips',
    '60 North Promenade',
    'Female',
    '',
    '',
    ''
    );

    INSERT INTO Suspect VALUES(
    '',
    'Darrell',
    'Prosser',
    '87 Newgate Street',
    'Male',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Brad',
    'Bender',
    '131 St Omers Road',
    'Male',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Lenny',
    'Mcconnell',
    '70 Buckingham Road',
    'Male',
    '18-24',
    'Asian',
    'Asian/Asian British - Pakistani'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Keelan',
    'Herman',
    '112 Park Avenue',
    'Male',
    '',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Siya',
    'Adams',
    '67 Dunmow Road',
    'Female',
    '25-34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Carlton',
    'Manning',
    '123 Boroughbridge Road',
    'Male',
    '18-24',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Hubert',
    'Ford',
    '82 Thirsk Road',
    'Male',
    '25-34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Bill',
    'Gates',
    '49 Oxford Road',
    'Male',
    '18-24',
    'Asian/Asian British - Pakistani',
    'Asian'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Hania',
    'Rees',
    '104 Gloddaeth Street',
    'Female',
    '18-24',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Rose',
    'Tyler',
    '88 Overton Circle',
    'Female',
    '25-34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'David',
    'Tennant',
    '47 Nith Street',
    'Male',
    'Over 34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Matt',
    'Smith',
    '93 Main Road',
    'Male',
    '25-34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Karen',
    'Gillen',
    '110 Harrogate Road',
    'Female',
    '18-24',
    'Asian/Asian British - Pakistani',
    'Asian'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Jenny',
    'Lugo',
    '23 Graham Road',
    'Female',
    '18-24',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Robert',
    'Downey JR',
    '89 Manor Way',
    'Male',
    '25-34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Stefano',
    'Sharpless',
    '105 Thames Street',
    'Male',
    'Over 34',
    '',
    'White
    '
    );

    INSERT INTO Suspect VALUES(
    '',
    'Kamal',
    'Leal',
    '21 Grey Street',
    'Male',
    '25-34',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White'
    );

    INSERT INTO Suspect VALUES(
    '',
    'Jacob',
    'Cooke',
    '109 Ockham Road',
    'Male',
    '18-24',
    'White - English/Welsh/Scottish/Northern Irish/British',
    'White'
    );

  • Gail Shaw

    SSC Guru

    Points: 1004446

    You could probably do it with a trigger, though will have to be complex and on both tables, and I don't know how you plan to have it automatically figure out which key goes with which key.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • drew.allen

    SSC Guru

    Points: 76588

    I don't think that you want to do this in general.  I could see doing this as part of an application, but the application would probably feed the information into a stored procedure which would handle all of the inserts.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply