SQL - Transfer Table(?) unique data

Question

I'm very green in the area of SQL, so I don't know what the idea or type is really called but I will call em "Transfer tables" and I will try to explain what I mean and my problem. Please correct me!

Description

3 tables.

Contacts: ID(primary key), Firstname, Lastname, SSN etc

Address: ID(primary key), City, Street etc

ContactsTransferAddress (short: CTA): ID(primary key), ContactID(foreign key), AddressID(foreign key)

The problem

Now this is just an exercise... For now I've made a stored procedure that:

ALTER PROCEDURE [dbo].[AddAddress] 
    @Street varchar(MAX),
    @City varchar(MAX),
    @CID int, --ContactID
    @AID int OUTPUT --AddressID
AS
BEGIN
    INSERT INTO Addressess(Street,City) VALUES (@Street, @City)
    SET @AID = SCOPE_IDENTITY()
    INSERT INTO CTA(CID,AID) VALUES (@CID, @AID)
END

Is there a way to make 2 people share the same AddressID(AID) when putting the connection data in the CTA? Right now, I get that my SP above will give different IDs for each address. But I don't know how to have unique addresses.

Thanks!

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-07-01T11:51:52

First, define the Addresses table so duplicates are not allowed:

alter table Addresses add constraint unq_Addresses_City_Street
    unique (City, Street);

Then you have to do something about duplicates, because your code will fail.

Because you are learning, I'm going to suggest just a check and insert approach:

SELECT @AID = AddressId
FROM Addresses
WHERE Street = @Street and City = @City;

IF @Aid IS NULL
BEGIN
    INSERT INTO Addresses(Street, City)
        VALUES (@Street, @City);

    SET @AID = SCOPE_IDENTITY();
END;

INSERT INTO CTA (CID, AID)
    VALUES (@CID, @AID);

In more serious code, I would take other precautions. IN particular:

I would use transactions and locking to prevent issues in a multi-threaded environment.
I would use the OUTPUT clause to return inserted ids.
I might use TRY/CATCH on the INSERT rather than IF to check if the constraint is violated.

SQL - Transfer Table(?) unique data

Description

The problem

1 Answers