CODEWITHSUNDEEP
sql
Amit
Amit

Reputation: 129

Insert row into a table with foreign key

I have created two tables (SupplierName and SupplierAddress) in the address_book database.

Table SupplierName has one row inserted as shown in the insert statement below. I am struggling how to insert data into the table called SupplierAddress.

Please note that the SupplierID (primary key) from the SupplierName table is the foreign key in the SupplierAddress table. If my table design is incorrect please opine on that as well. I am just trying to create a simple database to learn SQL. 

CREATE TABLE SupplierName
(
    SupplierID int Primary Key identity(1,1) NOT NULL,
    CompanyName varchar(50) NOT NULL,
    EIN char(9) NOT NULL UNIQUE
)

CREATE TABLE SupplierAddress
(
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Address varchar(50),
    City varchar(50),
    State char(2),
    ZipCode char(5),
    Phone varchar(10),
    SupplierAddressID int FOREIGN KEY REFERENCES SupplierName(SupplierID)
)

INSERT INTO SupplierName (CompanyName, EIN)
VALUES ('Clarks Electronics, Inc.', '123456789');

INSERT INTO SupplierAddress (Address, City, State, ZipCode, Phone)
VALUES ('2020 Garnet Road', 'York', 'PA', '17403', '717-123-4567')

SELECT
    SupplierName.SupplierID, SupplierAddress.SupplierAddressID 
FROM
    SupplierName
INNER JOIN 
    SupplierAddress ON SupplierAddressID=SupplierID

Upvotes: 0

Views: 68

Answers (1)

donPablo
donPablo

Reputation: 1959

Remove the hyphens from the phone number, or create table with more characters in phone

Look at http://www.dpriver.com/pp/sqlformat.htm to pretty format the sql

CREATE TABLE supplieraddress
  (
     id                INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
     address           VARCHAR(50),
     city              VARCHAR(50),
     state             CHAR(2),
     zipcode           CHAR(5),
     phone             VARCHAR(10),  -- make this longer
     supplieraddressid INT FOREIGN KEY REFERENCES suppliername(supplierid)
  ) 


INSERT INTO supplieraddress
            (address,
             city,
             state,
             zipcode,
             phone)
-- add  supplieraddressid  and its value so that the FK is created
VALUES     ('2020 Garnet Road',
            'York',
            'PA',
            '17403',
            '717-123-4567')  -- or  remove hyphens

SELECT suppliername.supplierid,
       supplieraddress.supplieraddressid
FROM   suppliername
       INNER JOIN supplieraddress
               ON supplieraddressid = supplierid

Upvotes: 1

Related Questions