Can a foreign key be NULL and or duplicate

Question

Please clarify two things for me    Can a Foreign key be NULL  Can a Foreign key be duplicate    As fair as I know  NULL shouldn t be used in foreign keys  but in some application of mine I m able to input NULL in both Oracle and SQL Server  and I don t know why

User · Answer

Simply put   Non-identifying  relationships between Entities is part of ER-Model and is available in Microsoft Visio when designing ER-Diagram  This is required to enforce cardinality between Entities of type   zero or more than zero   or  zero or one   Note this  zero  in cardinality instead of  one  in  one to many    Now  example of non-identifying relationship where cardinality may be  zero   non-identifying  is when we say a record   object in one entity-A  may  or  may not  have a value as a reference to the record s in another Entity-B    As  there is a possibility for one record of entity-A to identify itself to the records of other Entity-B  therefore there should be a column in Entity-B to have the identity-value of the record of Entity-B  This column may be  Null  if no record in Entity-A identifies the record s  or  object s  in Entity-B    In Object Oriented  real-world  Paradigm  there are situations when an object of Class-B does not necessarily depends  strongly coupled  on object of class-A for its existence  which means Class-B is loosely-coupled with Class-A such that Class-A may  Contain   Containment  an object of Class-A  as opposed to the concept of object of Class-B must have  Composition  an object of Class-A  for its  object of class-B  creation   From SQL Query point of view  you can query all records in entity-B which are  not null  for foreign-key reserved for Entity-B  This will bring all records having certain corresponding value for rows in Entity-A alternatively all records with Null value will be the records which do not have any record in Entity-A in Entity-B

User · Answer

I think it is better to consider the possible cardinality we have in the tables   We can have possible minimum cardinality zero  When it is optional  the minimum participation of tuples from the related table could be zero  Now you face the necessity of foreign key values to be allowed null   But the answer is it all depends on the Business

User · Answer

Short answer  Yes  it can be NULL or duplicate   I want to explain why a foreign key might need to be null or might need to be unique or not unique  First remember a Foreign key simply requires that the value in that field must exist first in a different table  the parent table   That is all an FK is by definition  Null by definition is not a value  Null means that we do not yet know what the value is   Let me give you a real life example  Suppose you have a database that stores sales proposals  Suppose further that each proposal only has one sales person assigned and one client   So your proposal table would have two foreign keys  one with the client ID and one with the sales rep ID   However  at the time the record is created  a sales rep is not always assigned  because no one is free to work on it yet   so the client ID is filled in but the sales rep ID might be null   In other words  usually you need the ability to have a null FK when you may not know its value at the time the data is entered  but you do know other values in the table that need to be entered  To allow nulls in an FK generally all you have to do is allow nulls on the field that has the FK  The null value is separate from the idea of it being an FK    Whether it is unique or not unique relates to whether the table has a one-one or a one-many relationship to the parent table   Now if you have a one-one relationship  it is possible that you could have the data all in one table  but if the table is getting too wide or if the data is on a different topic  the employee - insurance example  tbone gave for instance   then you want separate tables with a FK  You would then want to make this FK either also the PK  which guarantees uniqueness  or put a unique constraint on it    Most FKs are for a one to many relationship and that is what you get from a FK without adding a further constraint on the field  So you have an order table and the order details table for instance  If the customer orders ten items at one time  he has one order and ten order detail records that contain the same orderID as the FK

User · Answer

I think foreign key of one table also primary key to some other table So it won t allows nulls So there is no question of having null value in foreign key

User · Answer

By default there are no constraints on the foreign key  foreign key can be null and duplicate   while creating a table   altering the table   if you add any constrain of uniqueness or not null then only it will not allow the null  duplicate values

User · Answer

it depends on what role this foreign key plays in your relation    if this foreign key is also a key attribute in your relation  then it can t be NULL if this foreign key is a normal attribute in your relation  then it can be NULL

User · Answer

The idea of a foreign key is based on the concept of referencing a value that already exists in the main table  That is why it is called a foreign key in the other table  This concept is called referential integrity  If a foreign key is declared as a null field it will violate the the very logic of referential integrity  What will it refer to  It can only refer to something that is present in the main table  Hence  I think it would be wrong to declare a foreign key field as null

User · Answer

Here s an example using Oracle syntax  First let s create a table COUNTRY  CREATE TABLE TBL COUNTRY   COUNTRY ID VARCHAR2  50  NOT NULL     ALTER TABLE TBL COUNTRY ADD CONSTRAINT COUNTRY PK PRIMARY KEY   COUNTRY ID       Create the table PROVINCE  CREATE TABLE TBL PROVINCE  PROVINCE ID VARCHAR2  50  NOT NULL   COUNTRY ID  VARCHAR2  50     ALTER TABLE TBL PROVINCE ADD CONSTRAINT PROVINCE PK PRIMARY KEY   PROVINCE ID     ALTER TABLE TBL PROVINCE ADD CONSTRAINT PROVINCE COUNTRY FK FOREIGN KEY   COUNTRY ID   REFERENCES TBL COUNTRY   COUNTRY ID       This runs perfectly fine in Oracle  Notice the COUNTRY ID foreign key in the second table doesn t have  NOT NULL    Now to insert a row into the PROVINCE table  it s sufficient to only specify the PROVINCE ID  However  if you chose to specify a COUNTRY ID as well  it must exist already in the COUNTRY table

User · Answer

Yes foreign key can be null as told above by senior programmers    I would add another scenario where Foreign key will required to be null     suppose we have tables comments  Pictures and Videos in an application which allows comments on pictures and videos  In comments table we can have two Foreign Keys PicturesId  and VideosId along with the primary Key CommentId  So when you comment on a video only VideosId would be required and pictureId would be null    and if you comment on a picture only PictureId would be required and VideosId would be null

User · Answer

Can a Foreign key be NULL   Existing answers focused on single column scenario  If we consider multi column foreign key we have more options using MATCH  SIMPLE   PARTIAL   FULL  clause defined in SQL Standard   PostgreSQL-CREATE TABLE A value inserted into the referencing column s  is matched against the values of the referenced table and referenced columns using the given match type  There are three match types  MATCH FULL  MATCH PARTIAL  and MATCH SIMPLE  which is the default   MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null  if they are all null  the row is not required to have a match in the referenced table  MATCH SIMPLE allows any of the foreign key columns to be null  if any of them are null  the row is not required to have a match in the referenced table  MATCH PARTIAL is not yet implemented   Of course  NOT NULL constraints can be applied to the referencing column s  to prevent these cases from arising    Example  CREATE TABLE A a VARCHAR 10   b VARCHAR 10   d DATE   UNIQUE a b    INSERT INTO A a  b  d   VALUES  NULL  NULL  NOW      a   NULL  NOW     NULL   b   NOW      c    b   NOW      CREATE TABLE B id INT PRIMARY KEY  ref a VARCHAR 10   ref b VARCHAR 10     -- MATCH SIMPLE - default behaviour nulls are allowed ALTER TABLE B ADD CONSTRAINT B Fk FOREIGN KEY  ref a  ref b   REFERENCES A a b  MATCH SIMPLE   INSERT INTO B id  ref a  ref b  VALUES  1  NULL   b       --  NULL  x    x  value does not exists in A table  but insert is valid INSERT INTO B id  ref a  ref b  VALUES  2  NULL   x       ALTER TABLE B DROP CONSTRAINT IF EXISTS B Fk  -- cleanup  -- MATCH PARTIAL - not implemented ALTER TABLE B ADD CONSTRAINT B Fk FOREIGN KEY  ref a  ref b   REFERENCES A a b  MATCH PARTIAL  -- ERROR   MATCH PARTIAL not yet implemented  DELETE FROM B  ALTER TABLE B DROP CONSTRAINT IF EXISTS B Fk  -- cleanup  -- MATCH FULL nulls are not allowed ALTER TABLE B ADD CONSTRAINT B Fk FOREIGN KEY  ref a  ref b   REFERENCES A a b  MATCH FULL   -- FK is defined  inserting NULL as part of FK INSERT INTO B id  ref a  ref b  VALUES  1  NULL   b    -- ERROR   MATCH FULL does not allow mixing of null and nonnull key values   -- FK is defined  inserting all NULLs - valid INSERT INTO B id  ref a  ref b  VALUES  1  NULL  NULL    db lt  gt fiddle demo

User · Answer

1 - Yes  since at least SQL Server 2000   2 - Yes  as long as it s not a UNIQUE constraint or linked to a unique index

User · Answer

From the horse s mouth      Foreign keys allow key values that are all NULL  even if there are no   matching PRIMARY or UNIQUE keys       No Constraints on the Foreign Key      When no other constraints are defined on the foreign key  any number   of rows in the child table can reference the same parent key value    This model allows nulls in the foreign key           NOT NULL Constraint on the Foreign Key      When nulls are not allowed in   a foreign key  each row in the child table must explicitly reference a   value in the parent key because nulls are not allowed in the foreign   key       Any number of rows in the child table can reference the same parent   key value  so this model establishes a one-to-many relationship   between the parent and foreign keys  However  each row in the child   table must have a reference to a parent key value  the absence of a   value  a null  in the foreign key is not allowed  The same example in   the previous section can be used to illustrate such a relationship    However  in this case  employees must have a reference to a specific   department       UNIQUE Constraint on the Foreign Key      When a UNIQUE constraint is   defined on the foreign key  only one row in the child table can   reference a given parent key value  This model allows nulls in the   foreign key       This model establishes a one-to-one relationship between the parent   and foreign keys that allows undetermined values  nulls  in the   foreign key  For example  assume that the employee table had a column   named MEMBERNO  referring to an employee membership number in the   company insurance plan  Also  a table named INSURANCE has a primary   key named MEMBERNO  and other columns of the table keep respective   information relating to an employee insurance policy  The MEMBERNO in   the employee table must be both a foreign key and a unique key          To enforce referential integrity rules between the EMP TAB and   INSURANCE tables  the FOREIGN KEY constraint    To guarantee that each employee has a unique membership number  the   UNIQUE key constraint          UNIQUE and NOT NULL Constraints on the Foreign Key      When both UNIQUE   and NOT NULL constraints are defined on the foreign key  only one row   in the child table can reference a given parent key value  and because   NULL values are not allowed in the foreign key  each row in the child   table must explicitly reference a value in the parent key    See this   Oracle 11g link

[sql] Can a foreign key be NULL and/or duplicate?

Examples related to sql

Examples related to sql-server

Examples related to oracle

Examples related to foreign-keys