CODEWITHSUNDEEP
sqlsql-server
Kruise
Kruise

Reputation: 3

Cross Referencing Table Value for Another Value to allow Insert

How do I look at one table and use it to check another table for data integrity?

I have two SQL Tables.

One that is a person table

CREATE TABLE PERSON
(
 ID              INT IDENTITY(10000,1) NOT NULL,
 Firstname       VARCHAR(15),
 Lastname        VARCHAR(25) NOT NULL,
 Birthdate       DATE,
 Gender          VARCHAR(1),
   CHECK ( GENDER IN ('M', 'F')),
 Street          VARCHAR(50),
 City            VARCHAR(15),
 State           VARCHAR(2),
   CHECK (State IN ('FL','GA','PA')),
 Zip             INT,
 Phone           VARCHAR(10),
 Employee        VARCHAR(1),
   CHECK ( Employee IN('Y','N')),
 Member          VARCHAR(1),
   CHECK ( Member IN('Y','N')),
   CHECK (Member IN ('Y') or Employee IN ('Y')),

 CONSTRAINT PERSON_PK PRIMARY KEY (ID));

And Employee Table

CREATE TABLE EMPLOYEE
(
 ID               INT NOT NULL,
 Datehired        DATE DEFAULT GETDATE(),
 Status        VARCHAR(1),
   CHECK ( Status IN ('F','C')),
 Position      VARCHAR(25),
 EmpType       VARCHAR(25),

 CONSTRAINT EMPLOYEE_PK PRIMARY KEY (ID),
 CONSTRAINT EMPLOYEE_PERSON_FK FOREIGN KEY (ID) REFERENCES PERSON);

Let's say someone isn't an employee. I can still insert them into the Employee Table.

INSERT INTO EMPLOYEE
 (ID, Status, Position, EmpType)
  VALUES
 ('10000','C','Teaching Classes','Instructor');

How Do I prevent this from happening.

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

One method is to have a redundant key:

alter table person
    contraint unq_person_id_employee
        unique (id, employee);

Then add a computed column to employee:

alter table employee add employee as ('Y') persisted;

Finally, add the constraint:

alter table employee
    add constraint fk_employee_person
        foreign key (id, employee) references person(id, employee);

Now, you are guaranteed that only employees are in the Employee table.

Upvotes: 1

Related Questions