Is it possible to have more than one composite primary key

I have a table with about 30 columns of data. I did not design the table, as it is a legacy system.
This table already has a primary key which is a composite key of four columns of data.

So here are some questions. Is it possible to have another Composite key in the table? You see, I have two other columns of data, and
it would be nice if these two other columns of data were designated as a composite key. Basically the columns are related to property rentals,
and for each property their is associated a unique email address. So each property id might represent an apartement complex.
And I just need to ensure that for each property id, the same email address is not used more than once.In other words no one living in an apartement
complex can share the same email address

These are the columns I would like as a composite primary key.
Column Name : PropertyId
Column Name : EmailAddress

Something to keep in mind is that I have a situation where many of the EmailAddress columns currently contain null data. Which I am sure
would not be acceptable for a composite primary key.

Can an expert or two give me some feedback on my question. As I don't know how to approach this.
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Is it possible to have another Composite key in the table?
A table can have only one key, whether single or multi-column.  

>And I just need to ensure that for each property id, the same email address is not used more than once.
That would be a unique constraint:

ALTER TABLE YourTable
ADD CONSTRAINT ucCodes UNIQUE (PropertyId, EmailAddress)

>Something to keep in mind is that I have a situation where many of the EmailAddress columns currently contain null data.
I believe that would be fine, as long as there are no PropertyID - EmailAddresses duplicates before you run the above.
0
brgdotnetcontractorAuthor Commented:
I don't think that would work though if some of the email addresses are null. Because that would violoate the unique key constraint. For example if you have a property id called mulberry and there are 50 apartements with that same property id. And half of those email addressess are null, then that would violate the unique key constraint.

Because the Property Id would be repeated many times.
0
Scott PletcherSenior DBACommented:
You can add a computed, persisted int column to the index to allow only NULLs to be repeated, not other values.  If you want more info on how to do that, just let me know.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Here's my technique below.  You just need an identity, or other unique, non-zero value, in the row.


IF OBJECT_ID('tempdb.dbo.#test_unique_index_but_multiple_nulls') IS NOT NULL
    DROP TABLE #test_unique_index_but_multiple_nulls
GO
CREATE TABLE #test_unique_index_but_multiple_nulls (
    ident int IDENTITY(1, 1) NOT NULL,
    varchar_col varchar(100),
    -- CAST is just to be absolutely sure we get an int data type
    varchar_col_index_trick AS CAST(CASE WHEN varchar_col IS NULL THEN ident ELSE 0 END AS int) PERSISTED
    )
   
CREATE UNIQUE NONCLUSTERED INDEX varchar_col_index ON #test_unique_index_but_multiple_nulls ( varchar_col, varchar_col_index_trick )

GO

INSERT INTO #test_unique_index_but_multiple_nulls VALUES(NULL)
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('a')
INSERT INTO #test_unique_index_but_multiple_nulls VALUES(NULL) --should work!
INSERT INTO #test_unique_index_but_multiple_nulls VALUES(NULL) --should work!
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('b')
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('c')
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('a') --should fail!

GO

SELECT * FROM #test_unique_index_but_multiple_nulls ORDER BY ident
--verify both one more time
INSERT INTO #test_unique_index_but_multiple_nulls VALUES(NULL) --should work!
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('b') --should fail!
GO
SELECT * FROM #test_unique_index_but_multiple_nulls ORDER BY ident
GO
DROP TABLE #test_unique_index_but_multiple_nulls
0
brgdotnetcontractorAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.