January 16, 2012 at 6:27 am
Hi I am new to sql. I have got two different tables having diff employee ids as per their domains. But i have got a single designation table, having employeeid,name, designation column. Now can the employeeid of designation table be the foreign key for both the employee tables. If yes then how? Waiting in anticipation.
January 16, 2012 at 6:36 am
a foreign key can only be created against a column (or group of columns) which are either the Primary Key of the table, or has a Unique Constraint against it.
Based on your table description, i'd expect the employeeId column to be the Primary Key of the table, so you would typically join two tables together on that column to get the [designation ] column.
Lowell
January 17, 2012 at 2:23 am
create table emptb1
(
empid bigint not null primary key,
name varchar(30) not null,
address varchar(30) not null
)
create table emptb2
(
empid bigint pk,
name varchar(30) not null,
address varchar(30) not null
)
create table designation
(
designationid bigint not null primary key,
empid bigint not null,
designation varchar(50) not null
)
Now the question is that there is a third table which contains designations of all employees both from emptb1 and emptb2. I want that empid in designation table to be the foreign key of empid of emptb1 as well as emptb2. Waiting in anticipation guys pls help..
January 17, 2012 at 2:38 am
I don't think there is a way to create a FOREIGN KEY linking a column in a table to 2 columns from 2 different tables
You will have to use TRIGGERS to implement this
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 17, 2012 at 6:05 am
I know you are leaving a lot out to simplify the examples, but that ends up adding more questiosn than answers.
why do you think you need two tables that represent the same data?
why can you not simply use a "AllEmployees" table instead of splitting it into two?
then the foreign key stuff is easy and would match typical expectations?
your second table should simply refer to the master list (emptbl1?) and have the additional attributes they are going to represent.
Lowell
January 18, 2012 at 2:26 am
[p]Thnx, Kingston and Lowell. Actually emptbl1 contains information about army personnel , their organisational structure is different where as emptbl2 contains information about navy personnel and their organisational structure is different too, so it is not possible to merge two tables. Secondly in joint services scenario the designations are common so I had to maintain a single table for designation. As designation is directly related to empid, there is a requirement of foreign key constraint from two different tables.[/p]
[p]Guys I really need to work around this and it is urgent.. If trigger is the only solution then Kingston can u pls explain it.[/p]
January 18, 2012 at 6:47 am
i would just add two separate foreign key columns, one to each table, and a constraint to prevent both foreign keys from existing for a given record;
then if a source references table one or table two, you can easily track it down.
create function ThereCanBeOnlyOne(@FirstFK as int,@SecondFK as int)
RETURNS int
AS
BEGIN
DECLARE @results int
IF @FirstFK IS NULL AND @SecondFK IS NULL
SET @results = 0
IF @FirstFK IS NULL AND @SecondFK IS NOT NULL
SET @results = 0
IF @FirstFK IS NOT NULL AND @SecondFK IS NULL
SET @results = 0
IF @FirstFK IS NOT NULL AND @SecondFK IS NOT NULL
SET @results = 1
return @results
END
GO
CREATE TABLE emptb1 (
empid BIGINT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
address VARCHAR(30) NOT NULL
)
CREATE TABLE emptb2 (
empid BIGINT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
address VARCHAR(30) NOT NULL
)
CREATE TABLE designation (
designationid BIGINT NOT NULL PRIMARY KEY,
armyempid BIGINT NOT NULL references emptb1(empid),
navyempid BIGINT NOT NULL references emptb1(empid),
designation VARCHAR(50) NOT NULL,
CONSTRAINT [CK__ONLYONEFKALLOWED] CHECK ([dbo].[ThereCanBeOnlyOne]([armyempid],[navyempid])=(0))
)
Lowell
January 18, 2012 at 7:39 am
another option is to have each table hav a foreign key to the designation instead;
much easier and makes sense to me:
the "designation" table would just be a lookup table.
CREATE TABLE designation (
designationid BIGINT NOT NULL PRIMARY KEY,
designation VARCHAR(50) NOT NULL
)
CREATE TABLE emptb1 (
empid BIGINT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
address VARCHAR(30) NOT NULL,
designationid bigint references designation(designationid)
)
CREATE TABLE emptb2 (
empid BIGINT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
address VARCHAR(30) NOT NULL,
designationid bigint references designation(designationid)
)
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply