Search code examples
sqlsql-serveruniqueauto-populate

SQL auto-validating data when adding it to row

My SQL code is as follows:

CREATE TABLE personsdb
(personID int IDENTITY(1,1) NOT NULL,
personName varchar(50) NOT NULL,
associatedWith varchar(50) NOT NULL,
CONSTRAINT pk_persondb PRIMARY KEY (personID),
CONSTRAINT uq_persondb UNIQUE (associatedWith))

INSERT INTO personsdb
(personID, personName, associatedWith)
VALUES
('John', 'Mary'),
('Jack', 'Maggie'),
('Jeff', 'Marie')

I have a UNIQUE Constraint on the 'associatedWith' column, as I want to make sure that a person in personName can only be associated with one person in associatedWith, i.e. Mary could not be associated with Jeff because Mary is already associated with John.

My query relates to inserting the next row of the table. I want to insert 'Mary' into the personName column, but need a rule that autopopulates or only allows 'John' to be populated in the corresponding 'associatedWith' field, as a person can only be associated with one other person, and as John is already associated with Mary, when Mary is entered into the table, she should automatically be associated with John.

I'm relatively new to SQL but I'd like to figure it out as much as possible myself - if you could hint at a way to do this (in layman's terms) I'd be grateful so that I can go and research and learn how to do this.

Thanks very much in advance for your help.

David

Solution

  • This is a unary relationship which you cannot implement using foreign keys. You'll have to use a trigger. See below for full implementation. 

    CREATE TABLE dbo.Person(PersonId int not null primary key identity(1, 1), PersonName varchar(20) not null, AssociatedWith varchar(20));

GO


create trigger dbo.AssociationConstraint
ON dbo.Person
FOR INSERT, UPDATE
AS
IF (EXISTS(SELECT TOP(1) 1 FROM inserted i INNER JOIN dbo.Person p on i.PersonName = p.AssociatedWith)
OR EXISTS(SELECT TOP(1) 1 FROM inserted i INNER JOIN dbo.Person p on i.AssociatedWith = p.PersonName))
BEGIN
    RAISERROR('Person is already part of a relationship', 16, 1);
    ROLLBACK TRANSACTION;
END
GO


insert into dbo.Person(PersonName, AssociatedWith) values('John', 'Mary'), ('Jack', 'Maggie'), ('Jeff', 'Marie');
--this will error
insert into dbo.Person(PersonName, AssociatedWith) values('Mary', 'Jack');