How to implement referential integrity here?

I got the following structure – which I admit is not ideal, but so much is built on that, tat I want to minimize changes.
I am not sure about how to properly implement referential integrity between Documents and Delivery Adresses. Can it be done here without using triggers ? The problem is that the addressNum can sometimes be Null in the Documents.

CREATE TABLE [dbo].[Clients](
    [IdClient] [varchar](10) NOT NULL,
    [Nom] [varchar](40) NULL
    CONSTRAINT PK_Clients PRIMARY KEY (IdClient))
GO

CREATE TABLE [dbo].[ClientsDelivAdr](
    [IdClient] [varchar](10) NOT NULL,
    [AdrNum] [tinyint] NOT NULL,
    [Adresse] [varchar](200) NULL
 CONSTRAINT [PK_ClientsAdrLivr] PRIMARY KEY (IdClient, AdrNum))

CREATE TABLE [dbo].[Documents](
    [DocID] [int] IDENTITY(1,1) NOT NULL,
    [NoDoc] [char](9) NULL,
    [IdClient] [varchar](10) NULL,
    [AdrNum] [tinyint] NULL,
    [DateDoc] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_DocID] PRIMARY KEY (DocId)) 

Some Clients have several Delivery Adresses, some have none.
So data looks like this:

  • converting start date to datetime as beginning of the day and end date to end of the day
  • Display next event date
  • Denormalization of a table (Pivot?)
  • How do I name the results of a joined table query
  • Find out the calling stored procedure in SQL Server
  • How can I use CONTAINS() when the variable is sometimes null or empty string?
  • Clients
    Id   Name     Address 
    ---  ----     -------
    AA   ClientA  addressA
    BB   ClientB  qddressB
    CC   ClientC  addressC
    
    
    DeliveryAdresses
    Client  Adr   Address
    ------  ---   -------
    AA      1     shop1
    AA      2     shop2
    CC      1     shopx
    
    Documents
    DocId   Client  Addr  OrderDate
    ------- ------  ----  --------
    1001    CC      1     5/5/2013
    1002    AA      1     5/5/2013
    1003    BB     (Null) 5/5/2013
    

  • Using PIVOT TSQL command
  • Database Form Builder For MS
  • Recursive CTE SQL (Parent Child)
  • SQL Server: Must numbers all be specified with latin numeral digits?
  • Sending data from excel to Server using HTTP Post
  • How Can I Find All The Stored Procedures That A Certain User Has Execute Rights To
  • One Solution collect form web for “How to implement referential integrity here?”

    I think you can just use foreign keys as you would expect:

    CREATE TABLE [dbo].[Documents](
        [DocID] [int] IDENTITY(1,1) NOT NULL,
        [NoDoc] [char](9) NULL,
        [IdClient] [varchar](10) NULL,
        [AdrNum] [tinyint] NULL,
        [DateDoc] [smalldatetime] NOT NULL,
     CONSTRAINT [PK_DocID] PRIMARY KEY (DocId),
    CONSTRAINT FK_DOC_Clients FOREIGN KEY (IdClient)
         references Clients (IdClient),
    CONSTRAINT FK_Doc_Addresses FOREIGN KEY (IdClient,AdrNum)
         references DeliveryAddresses (IdClient,AdrNum) ) 
    

    If one or more column values in the referencing side of a foreign key is NULL, then the foreign key constraint is not checked. Conversely, there’s no way to have NULL be a foreign key reference.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.