rows that exclude each other in create table statement

Question

I have to create a table, with either first name and last name of a person, or a name of an organization. There has to be exactly one of them. For example one row of the table is -

first_name  last_name  organization
----------  ---------  ------------
John        Smith      null

or another row can be -

first_name  last_name  organization
----------  ---------  --------------------
null        null       HappyStrawberry inc.

Is there a way to define this in SQL language? Or should I just define all three columns being able to get null values?

Answer 1

Your situation is a classical example of what some ER dialects call "entity subtyping".

You have an entity called "Person" (or "Party" or something of that ilk), and you have two ditinct sub-entities called "NaturalPerson" and "LegalPerson", respectively.

The canonical way to model ER entity subtypes in a relational database is using three tables : one for the "Person" entity with all columns that are "common" for both NaturalPerson and LegalPerson (i.e. that exist for Persons, regardless of their type), and one per identified sub-entity holding all the columns that pertain to that sub-entity in particular.

You can read more on this in Fabian Pascal, "Practical Issues in Database Management".

Answer 2

You could use a check constraint, like:

create table YourTable (
    col1 varchar(50)
,   col2 varchar(50)
,   col3 varchar(50)
,   constraint TheConstraint check ( 1 =
        case when col1 is null then 1 else 0 end +
        case when col2 is null then 1 else 0 end +
        case when col3 is null then 1 else 0 end )
)

Another way is to add a type column (EAV method):

create table YourTable (
    type varchar(10) check (type in ('FirstName', 'LastName', 'Organisztion')
,   value varchar(50))
insert YourTable ('LastName', 'Obama')
insert YourTable ('FirstName', 'Barrack')
insert YourTable ('Orginazation', 'White House')

Answer 3

You can do this using a constraint:

CREATE TABLE [dbo].[Contact](
    [first_name] [varchar](50) NULL,
    [last_name] [varchar](50) NULL,
    [organization] [varchar](50) NULL,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [CK_Contact] CHECK  (([first_name] IS NOT NULL OR [last_name] IS NOT NULL OR [organization] IS NOT NULL))
GO

ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [CK_Contact]
GO

The CK_Contact constraint ensures that at least one value was entered.