-1

I have a table in which I have made two columns as composite PRIMARY KEY. Now I have a confusion.

Let's say I have a table as following:

ColA         ColB

X            NULL
X            NULL

Both the rows "X NULL" and "X NULL" are combined PRIMARY KEY.

So, is this insertion possible? Because the NULL value differs and accordingly both the rows should represent a new combination of data.

Please clarify.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Samarth
  • 36
  • 4
  • No. Null values aren't allowed in primary key columns. – jarlh Aug 24 '18 at 09:59
  • 3
    How exactly is `X, NULL` *different* from `X, NULL`? – David Aug 24 '18 at 10:02
  • 1
    First of all no null values are allowed in primary key. And if 2 values are same for 2 columns which will be part of primary key as X , Y and another X,Y , this is also not allowed.because it is not at all different , it is same. – YLG Aug 24 '18 at 10:03
  • 1
    Please **[edit]** your question and add the `create table` statement for the table in question including all indexes and constraints. Primary key columns can not contain null values, so I guess you only created a unique index, not a primary key constraint –  Aug 24 '18 at 10:05
  • 1
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Aug 24 '18 at 10:05
  • [What to do with null values when modeling and normalizing?](https://stackoverflow.com/a/40733625/3404097) – philipxy Aug 24 '18 at 17:53
  • Possible duplicate of [SQL Server, can't insert null into primary key field?](https://stackoverflow.com/questions/3876785/sql-server-cant-insert-null-into-primary-key-field) – philipxy Aug 24 '18 at 18:03
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. – philipxy Aug 24 '18 at 18:04

1 Answers1

1

Primary keys have three properties:

  • The key (combination) uniquely identifies each row.
  • No component of the keys can be NULL.
  • A table has only one primary key.

So, by this definition, you cannot declare the two columns to be a primary key.

In general, I am not an advocate of composite primary keys. I recommend using an auto-incremented primary key for the table (the syntax varies by database).

One nice feature of a numeric identifier is that it captures the insertion order of the values in the table. Also, many databases cluster the data by the primary key, causing fragmentation on inserts and updates.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786