Post reply

Automatically inserting foreign keys

  • January 9, 2019 at 4:51 pm

    #381171

    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'
    );

  • January 10, 2019 at 1:55 am

    #2018489

    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

  • January 10, 2019 at 9:48 am

    #2018571

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy