SQL Primary Key for 2 tables

I have two tables (Company and Persons) in my reservation system, (These are the TYPES OF CLIENT that I have).

COMPANY TABLE

-CompanyID
**-CompanyName**
-Fname
-Lname
-Street
-City
-ContactNo

PERSONS TABLE

-PersonID
-Fname
-Lname
-Street
-City
-ContactNo

Their only difference is the COMP开发者_如何学PythonANYNAME.

Then each transaction can either be a COMPANY or PERSON..

Reservation Table

-ReservationNo
-ClientID

How can I insert a value for ClientID?

---

You can add one more column in reservation table specifying client type (company/person). Then ClienID can be PersonID/CompanyID. Doing a join for finding whether a clientID corresponds to person or company is not a good design

---

If these two possibilities are likely to never change, then having a pair of nullable columns (with appropriate foreign keys) would probably suffice:

CREATE TABLE Reservations (
    ReservationNo int not null,
    PersonID int null,
    CompanyID int null,
    /* Other columns */
    constraint CK_Reservations_PersonOrCompany CHECK
      ((CompanyID is null or PersonID is null) and COALESCE(CompanyID,PersonID) is not null),
    /* Other constraints */
)

If there are likely to be more clients, then introduce a client table:

CREATE TABLE Clients (
    ClientID int not null,
    ClientType varchar(10) not null,
    /* Common columns for all client types */
    constraint PK_Clients PRIMARY KEY (ClientID),
    constraint UQ_Client_TypeCheck UNIQUE (ClientID,ClientType),
    constraint CK_Client_ClientTypes CHECK
        (ClientType in ('PERSON','COMPANY')) --Add more types later
     /* Other constraints */
)

Then, in each client type table, enforce the correct client type:

CREATE TABLE Persons (
    ClientID int not null,
    ClientType as CONVERT(varchar(10),'PERSON'),
     /* Columns unique to person client types */
    constraint PK_Persons PRIMARY KEY (ClientID),
    constraint FK_Persons_Clients FOREIGN KEY (ClientID) references Clients,
    constraint FK_Person_ClientTypeCheck FOREIGN KEY (ClientID,ClientType)
         references Clients (ClientID,ClientType)
    /* Other constraints */
)

And repeat for the other tables, as appropriate.

---

You should merge "COMPANY TABLE" and "PERSONS TABLE" to "CLIENTS TABLE" :)

-ClientID
-Fname
-Lname
-Street
-City
-ContactNo

and create "COMPANY TABLE" with

-CompanyID
**-CompanyName**
-ClientID

and create "PERSONS TABLE" with

-PersonID
-ClientID

OR

you merge "COMPANY TABLE" and "PERSONS TABLE" to "CLIENTS TABLE" with NULL values like this

-ClientID
-PersonID (can be NULL)
-CompanyID (can be NULL)
**-CompanyName** (can be NULL or empty)
-Fname
-Lname
-Street
-City
-ContactNo

If you don't need mapping from PersonID/CompanyID for backward compatibility with other tables then you should drop the PersonID/CompanyID columns (and the whole "PERSONS TABLE" for the first case i mentioned). You can afterwards identify your company rows with "CompanyName NOT IS NULL".

If you don't want to merge tables consider "Damien_The_Unbeliever"s answer.

But i think you should definitely merge these two "person tables".