CODEWITHSUNDEEP
sqldatabaseprimary-key
nutella
nutella

Reputation: 99

Can I use one same primary key in two different tables?

CREATE TABLE Employee
(
employeeID INT (10) PRIMARY KEY,
Name CHAR (20)
);

CREATE TABLE SALARY
(
employeeID INT (10) PRIMARY KEY,
Salary INT (10)
);

Is it possible to use the same primary key in both tables?

Upvotes: 4

Views: 35425

Answers (6)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Yes, you can. You would make salary.employeeid both the table's primary key and a foreign key to the employee table:

CREATE TABLE salary (
  employeeid INT NOT NULL,
  salary INT NOT NULL,
  CONSTRAINT pk_salary PRIMARY KEY (employeeid),
  CONSTRAINT fk_salary_employeeid FOREIGN KEY (employeeid) REFERENCES employee (employeeid)
);

This creates a {1}:{0,1} relationship between the tables and ensures that you cannot store a salary without having stored the employee and that you cannot store more than one salary for one employee.

This is something we rarely do. (We would rather make the salary a column in the employee table.) The only advantage of a separate salary table I see is that you can grant rights on the employee table but revoke them on the salary table, so as to make the salary table invisible to some database users.

Upvotes: 8

Joao Ishiwatari
Joao Ishiwatari

Reputation: 85

Can do by differents ways

I have a "Main" table named "Peoples" with "peopleId" as PK Identity

"Child" tables

"PeopleDocs" with "peopleId" PK

"PeopleImages" with "peopleId" PK

"PeopleKeys" with "peopleId" PK.

So, when i create one record on "Peoples" i populate the "childs" with empty records to work later.

This logic is about a federal law on my country and some people informations has a life cicle and must be deleted if the life cicle ends or the owner of the data demands. So we split the people sensitive data on child tables to not lose all bussiness records, refferences and log about that people.

Col1 as Primary key on both tables

SELECT
  P.COL1,
  P.COL2,
  C.COL2,
  C.COL3
FROM
  TBL1 P, TBL2 C
WHERE
  C.COL1 = P.COL1

Or using joins

SELECT 
  P.COL1,
  P.COL2,
  C.COL2,
  C.COL3
FROM
  TBL1 AS P
INNER JOIN
  TBL2 AS C
ON
  C.COL1 = P.COL1

Upvotes: 0

ChrisCarroll
ChrisCarroll

Reputation: 463

You can do this, however, it is bad design.

I would suggest having the EmployeeId as the PK on the employee table and the EmployeeId as a Foreign Key on the Salary table, with the Salary table having it's own PK (most likely SalaryId).

Also the field [Name] I would personally steer clear of too, as "Name" is a reserved word in SQL.

    CREATE TABLE dbo.Employee
    (
     EmployeeId BIGINT IDENTITY(1,1)
    ,EmployeeName VARCHAR(20) NOT NULL
    ,CONSTRAINT PK_Emp PRIMARY KEY (EmployeeId)
    );
    GO

    CREATE TABLE dbo.Salary
    (
     SalaryId BIGINT IDENTITY(1,1)
    ,EmployeeId BIGINT NOT NULL
    ,Salary INT NOT NULL
    ,CONSTRAINT PK_Sal PRIMARY KEY (SalaryId)
    ,CONSTRAINT FK_EmpSal FOREIGN KEY (EmployeeId)
        REFERENCES Employee(EmployeeId)
    );
    GO

All of that said, I think a little more thought into the db structure you should most likely end up with 3 tables. It is likely that many staff will have the same salary, lets say 5 employees are on 40,000 and 3 are on 50,000, etc. You will end up storing the same Salary value multiple times.

A better way is to store that value once and have a third table that links an employee with a salary (in this case I have called it [Earnings]). With this structure the salary of say 40,000 is stored 1 time in the db and you can link an employeeId to it multiple times.

    CREATE TABLE dbo.Employee
    (
     Id BIGINT IDENTITY(1,1)
    ,EmployeeName VARCHAR(20) NOT NULL
    ,CONSTRAINT PK_Emp PRIMARY KEY (Id)
    );
    GO

    CREATE TABLE dbo.Salary
    (
     Id BIGINT IDENTITY(1,1)
    ,Salary INT NOT NULL
    ,CONSTRAINT PK_Sal PRIMARY KEY (Id)
    );
    GO

    CREATE TABLE dbo.Earnings
    (
     Id BIGINT IDENTITY(1,1)
    ,EmployeeId BIGINT NOT NULL
    ,SalaryId BIGINT NOT NULL
    ,CONSTRAINT PK_Ear PRIMARY KEY (Id)
    ,CONSTRAINT FK_EmpEar FOREIGN KEY (EmployeeId)
        REFERENCES Employee(Id)
    ,CONSTRAINT FK_SalEar FOREIGN KEY (SalaryId)
        REFERENCES Salary(Id)
    );
    GO

Upvotes: 1

Parth Badani
Parth Badani

Reputation: 17

Technically, it is possible. However, it is advisable to use Foreign key. This will:

-Avoid redundancy

-Help in maintaining db structure

-Improve readability

For this example, use:

CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, Name CHAR (20), Primary Key (EmployeeId) );

CREATE TABLE SALARY ( SalaryId INT , EmployeeID INT , Salary INT (10), Primary Key (SalaryId), Foreign Key (EmployeeId) REFERENCES Employee );

*An even better approach would be to add a constraint instead of just mentioning

[key names] Like:

CREATE TABLE Employee(
EmployeeId INT,
Name CHAR(20)
)

ALTER TABLE Employee ADD CONSTRAINT PK_EmployeeId PRIMARY KEY

Upvotes: -1

Venkataraman R
Venkataraman R

Reputation: 12959

Yes. You can have same column name as primary key in multiple tables.

Column names should be unique within a table. A table can have only one primary key, as it defines the Entity integrity.

If this question is about data modelling parent-child relationship, There are two types. You are read more on this.

  1. Identifying relationship : Child identifies itself by the help of parent. Here, Employee & Salary will share the same primary key. Primary key of parent table (EmployeeId) will be primary key of child table also (Salary).
  2. Non-Identifying relationship: Child is having its own identity. Here, Employee & Salary will have different primary key. Child table will have its own primary key(say SalaryId) and will have primary key of parent as a Foreign key(EmployeeId).

Upvotes: 8

BlackSwan
BlackSwan

Reputation: 384

If your question is if you can use the same EMPLOYEEID column as the primary ID on multiple tables, the answer is "YES, YOU CAN"

You can use the same column as primary index on multiple tables, but you cannot have more than one primary index on a table

Upvotes: 5

Related Questions