CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-design
Rm558
Rm558

Reputation: 4992

How do you enforce unique across 2 tables in SQL Server

Requirements:

  1. Every employee has a unique ID. (EPID)
  2. A employee can only be either one of below,
    • FT - Full Time
    • PT - Part Time
  3. Any employee can never be both FT and PT.
  4. FT & PT have lots of different fields to capture.

Implementation:

Create Table EmpFT( EPID int primary key,  F1, F2, etc)
Create Table EmpPT( EPID int primary key,  P1, P2, etc)
--This does not prevent same EPID on both EmpFT and EmpPT.

How do you implement No. 3 in database?

I am using SQL Server 2012 standard edition.

Upvotes: 0

Views: 96

Answers (4)

Chris Stillwell
Chris Stillwell

Reputation: 10547

You can add check constraints. Something like this for both tables

ALTER TABLE EmpFT
ADD CONSTRAINT chk_EmpFT_EPID CHECK (dbo.CHECK_EmpPT(EPID)= 0)
ALTER TABLE EmpPT
ADD CONSTRAINT chk_EmpPT_EPID CHECK (dbo.CHECK_EmpFT(EPID)= 0)

And the functions like so:

CREATE FUNCTION CHECK_EmpFT(@EPID int)
RETURNS int
AS
BEGIN
DECLARE @ret int;   
SELECT @ret = count(*) FROM EmpFT WHERE @EPID = EmpFT.EPID
RETURN @ret;
END
GO


CREATE FUNCTION CHECK_EmpPT(@EPID int)
RETURNS int
AS
BEGIN
DECLARE @ret int;   
SELECT @ret = count(*) FROM EmpPT WHERE @EPID = EmpPT.EPID
RETURN @ret;
END
GO

Further reading here:

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You cannot have primary keys span tables. So, one method is to create a table employee with the appropriate constraints. This might look like this:

create table employee (
    EPID int not null identity(1, 1) primary key,
    FTID int references empft(empftid),
    PTID int references emppt(empptid),
    CHECK (FTID is not null and PTID is null or FTID is null and PTID is not null),
    UNIQUE (FTID),
    UNIQUE (PTID)
    . . .
);

create table empft (
    EmpFTId int not null identity(1, 1) primary key,
    . . .
);

create table emppt (
    EmpPTId int not null identity(1, 1) primary key,
    . . .
);

Of course, you could also use triggers if you wanted to.

Upvotes: 0

nvogel
nvogel

Reputation: 25526

Try this method:

CREATE TABLE Emp(EPID INT PRIMARY KEY,
    t CHAR(2) NOT NULL, UNIQUE (EPID,t));

CREATE TABLE EmpFT(EPID INT PRIMARY KEY, ... other columns
    t CHAR(2) NOT NULL CHECK (t = 'FT'),
    FOREIGN KEY (EPID,t) REFERENCES Emp (EPID,t));

CREATE TABLE EmpPT(EPID INT PRIMARY KEY, ... other columns
    t CHAR(2) NOT NULL CHECK (t = 'PT'),
    FOREIGN KEY (EPID,t) REFERENCES Emp (EPID,t));

Upvotes: 4

Politank-Z
Politank-Z

Reputation: 3719

You could create a combined table for all employees. The FT and PT tables could use foreign keys to the employee table.

Upvotes: 0

Related Questions