CODEWITHSUNDEEP
sqlsql-serverforeign-keysprimary-key
Richard
Richard

Reputation: 7443

Using a single attribute as both a primary key and a foreign key in a subtype table

I have a supertype table (from ERD) called Card and this table has two subtypes: Credit Card and Debit Card. I have a Primary Key in my table Card named as CardID.

Will it be correct if I assign the Primary Key of Card as both the foreign key and primary key of Credit Card and Debit Card?

How do import the primary key of a supertype table to its subtypes (the code)? Do I simply do this?

CREATE TABLE [Credit Card] (
    CreditCardNumber varchar(50) NOT NULL Primary Key,
    CreditCardNumber varchar(50) NOT NULL Foreign Key REFERENCES Card(CardID)
)

Upvotes: 0

Views: 3627

Answers (3)

George Menoutis
George Menoutis

Reputation: 7260

If the credit card table is considerably different in structure than the debit card one, I suppose it makes sense to do the keys the way you are describing. You have to balance for yourself whether this solution is better than to just have a card table with all fields of either credit or debit card.

The way you have written your query is like saying you have two different column with the same name, as GuidoG stated. You want something like this:

CREATE TABLE [Credit Card] (
    CreditCardNumber varchar(50) NOT NULL Primary Key,
    ....(other cols)....,
    CONSTRAINT FK_CreditCard_Card Foreign Key (CreditCardNumber) REFERENCES Card(CardID)
)

I'd like to add that the nature of your wondering about the best solution may be closely related to "polymorphic associations", check a bit around for it.

In response to your comment: The only thing not correct from the things you are saying is the "but" word. It seems to have gotten into your mind that it is "strange" if a column is both PK and FK; it is not. They are two totally irrelevant things, and SQL server couldn't care less. The PK makes sure each row has a different value in this column. The FK makes sure the value must exist in the possible values of a column from another table. No conflicts at all; except a possible philosophical/design conflict, which of course only happens in YOUR mind (not the sql server's structure), and which my response (and many others') covered already.

Upvotes: 2

GuidoG
GuidoG

Reputation: 12059

I guess you want something like this

create table dbo.[Credit Card] (
  CreditCardNumber varchar(50) not null,
  other columns ...

  constraint PK_CreditCardNumber primary key (CreditCardNumber),
  constraint FK_CreditCard_Card foreign key (CreditCardNumber) references Card(CardID)
)

This will only work if the column CardID in table Card has the same definition as the column CreditCardNumber in table Credit Card
Just from looking at the names this might not be the case ?

Most of the times it is better to use a discrimator field and just one table.
For example

create table dbo.Card (
   CardNumber varchar(50) not null,
   CardType char(1) not null check (CardType in ('C', 'D')),

   CreditCardColumn1 varchar(50) null,
   CreditCardColumn2 varchar(50) null,

   DebitCardColumn1 varchar(50) null,

   constraint PK_CardNumber primary key (CardNumber),
)

The check constraint will make sure only C or D can be stored in this colum.
Both columns for Credit Card and Debet Card have to be put into this table. The query that you write should be different for each type, the table will always be the same

Now when you need all creditcards just do

select c.CardNumber,
       c.CardType,
       c.CreditCardColumn1,
       c.CreditCardColumn2
from   dbo.Card c
where  CardType = 'C'

and for all debitcards just do

select c.CardNumber,
       c.CardType,
       c.DebitCardColumn1
from   dbo.Card c
where  CardType = 'D'

Upvotes: 1

Alexey Gasperovich
Alexey Gasperovich

Reputation: 21

See Doc\SQL\T-SQL

CREATE TABLE [Credit Card] (
    CreditCardNumber varchar(50) NOT NULL Primary Key,
    Foreign Key (CreditCardNumber) REFERENCES Card(CardID)
)

Upvotes: 2

Related Questions