locked
how to accomplish composite foreign key not null RRS feed

  • Question

  • hi experts,

    tableA

    (id + name) primary key

    tableB

    CONSTRAINT [FK_tableA_tableB] FOREIGN KEY
    (
           id,name
    )
    REFERENCES tableA(id,name) NOT NULL

    I want all the composite foreign key column not null. how to accomplished this task. the above statement gives gives error.

    plz help.

    Sunday, January 23, 2011 6:23 AM

Answers

  • for example like that

    create table a(id int not null, name varchar(10) not null)
    	
    alter table a add constraint a_pk primary key(id,name)
    
    create table b(id int not null, name varchar(10) not null)
    	
    alter table b add constraint a_fk foreign key (id, name) references a(id, name)
    

    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    • Marked as answer by KJian_ Friday, January 28, 2011 7:05 AM
    Sunday, January 23, 2011 6:34 AM
  • NOT NULL is not part of the Foreign Key constraint (at least not in SQL Server). In SQL Server, NOT NULL is a "property" of the individual column. So in the declaration of the columns you should add NOT NULL. Then you can simply add a Foreign Key constraint.


    Gert-Jan
    • Proposed as answer by Naomi N Sunday, January 23, 2011 4:14 PM
    • Marked as answer by KJian_ Friday, January 28, 2011 7:05 AM
    Sunday, January 23, 2011 10:22 AM
  • Like Gert-Jan said, NOT NULL is specified on the column definition rather than the constraint definition.  The foreign key constraint will be enforced for all rows when both columns don't allow NULLs. 

    CREATE TABLE dbo.tableA(
    	id int NOT NULL
    	,name varchar(50) NOT NULL
    	,CONSTRAINT PK_tableA PRIMARY KEY CLUSTERED(id, name)
    	);
    	
    CREATE TABLE dbo.tableB(
    	id int NOT NULL
    	,name varchar(50) NOT NULL
    	,CONSTRAINT PK_tableB_tableA FOREIGN KEY (id, name)
    		REFERENCES dbo.tableA(id, name)
    	);
    

    If you allow NULLs, the foreign key will not be enforced for rows that contain NULL in either column.  Rows that comtain a non-NULL value in both columns must also exist in the referenced table.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Friday, January 28, 2011 7:05 AM
    Sunday, January 23, 2011 11:58 AM

All replies

  • for example like that

    create table a(id int not null, name varchar(10) not null)
    	
    alter table a add constraint a_pk primary key(id,name)
    
    create table b(id int not null, name varchar(10) not null)
    	
    alter table b add constraint a_fk foreign key (id, name) references a(id, name)
    

    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    • Marked as answer by KJian_ Friday, January 28, 2011 7:05 AM
    Sunday, January 23, 2011 6:34 AM
  • NOT NULL is not part of the Foreign Key constraint (at least not in SQL Server). In SQL Server, NOT NULL is a "property" of the individual column. So in the declaration of the columns you should add NOT NULL. Then you can simply add a Foreign Key constraint.


    Gert-Jan
    • Proposed as answer by Naomi N Sunday, January 23, 2011 4:14 PM
    • Marked as answer by KJian_ Friday, January 28, 2011 7:05 AM
    Sunday, January 23, 2011 10:22 AM
  • Like Gert-Jan said, NOT NULL is specified on the column definition rather than the constraint definition.  The foreign key constraint will be enforced for all rows when both columns don't allow NULLs. 

    CREATE TABLE dbo.tableA(
    	id int NOT NULL
    	,name varchar(50) NOT NULL
    	,CONSTRAINT PK_tableA PRIMARY KEY CLUSTERED(id, name)
    	);
    	
    CREATE TABLE dbo.tableB(
    	id int NOT NULL
    	,name varchar(50) NOT NULL
    	,CONSTRAINT PK_tableB_tableA FOREIGN KEY (id, name)
    		REFERENCES dbo.tableA(id, name)
    	);
    

    If you allow NULLs, the foreign key will not be enforced for rows that contain NULL in either column.  Rows that comtain a non-NULL value in both columns must also exist in the referenced table.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Friday, January 28, 2011 7:05 AM
    Sunday, January 23, 2011 11:58 AM