locked
foreign key setup RRS feed

  • Question

  • Since I am new to setting up a database on sql server 2008 r2 and creating tables that are referenced by foreign keys, I would like to ask the following about the 4 tables that I created below:
    1. The way I setup the foreign keys is this ok why or why not? Can you explain and/or point me a reference that will explain the bettersolution for me?
    2. In 3 tables the data will not change much after the data is initially loaded. However in the table called Rej_History,
     I will be loading data to that table daily. Thus for all the tables, can you tell me if I need to drop indexes every time I update the
    data. Basically can you tell me and/or point me to a reference that will tell me how to setup a script to load the data?
    CREATE TABLE [dbo].[Rej_Contacts](
     [MRC_Contact_ID] [int] IDENTITY(1,1) NOT NULL,
     [MRC_Prefix] [varchar](30) NULL,
     [MRC_Last_Name] [varchar](50) NULL,
     [MRC_First_Name] [varchar](50) NULL, 
     [MRC_Phone_Number] [varchar](25) NULL,  
     [MRC_Email] [varchar](150) NULL,
     [MRC_Address] [varchar](100) NULL, 
     [MRC_City] [varchar](50) NULL,
     [MRC_State] [varchar](2) NULL,
     [MRC_Zip] [varchar](10) NULL,
     [MRC_Update_Date] [datetime] NULL,
     [MRC_Updated_By] [varchar](50) NULL
     CONSTRAINT [PK_Rej_Contacts] PRIMARY KEY CLUSTERED
    (
     [MRC_Contact_ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    ------------
    CREATE TABLE [dbo].[Rej_History](
     [MRH_Id] [int] IDENTITY(1,1) NOT NULL,
     [MRH_Plan_Id] [int] NULL,
     [MRH_Create_Date] [datetime] NULL,  
     [MRH_Code_ID] [int] NULL,
     [MRH_Tran_Count] [numeric](18, 0) NULL,
     [MRH_Batch_Size] [numeric](18, 0) NULL,
     [MRH_Tran_Code_Description] [varchar](max) NULL
     CONSTRAINT [PK_Rej_History] PRIMARY KEY CLUSTERED
    (
     [MRH_Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Rej_History]  WITH NOCHECK ADD  CONSTRAINT [PK_MRH_RNumber] FOREIGN KEY([MRH_Plan_Id])
    REFERENCES [dbo].[Rej_Plans] ([MRP_Plan_Id])
    GO

    ALTER TABLE [dbo].[Rej_History]  WITH NOCHECK ADD  CONSTRAINT [PK_MRH_Tran_Codes] FOREIGN KEY([MRH_Code_ID])
    REFERENCES [dbo].[Tran_Codes] ([TRC_Code_Id])
    GO

    ----------------

    CREATE TABLE [dbo].[Rej_Plans](
     [MRP_Plan_Id] [int] IDENTITY(1,1) NOT NULL,
     [MRP_PLan_Number] [varchar](10) NULL,
     [MRP_Contact_Id] [int] NOT NULL,
     [MRP_Parent_Organization_Name] [varchar](100) NOT NULL,
     [MRP_Update_Date] [datetime] NULL,
     [MRP_Updated_By] [varchar](50) NULL
     CONSTRAINT [PK_MRP_RNumber] PRIMARY KEY CLUSTERED
    (
     [MRP_Plan_Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    ----------------
    CREATE TABLE [dbo].[Tran_Codes](
     [TRC_Code_Id] [int] IDENTITY(1,1) NOT NULL,
     [TRC_Tran_Code] [numeric](6, 0) NOT NULL,
     [TRC_Type] [char](1) NULL,
     [TRC_Title] [nvarchar](75) NULL,
     [TRC_Long_Definition] [varchar](max) NULL 
     CONSTRAINT [PK_Tran_Codes] PRIMARY KEY CLUSTERED
    (
     [TRC_Code_Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    -----------------

     

    • Moved by Brian TkatchEditor Thursday, March 29, 2012 3:54 PM design question, not query (From:Transact-SQL)
    Thursday, March 29, 2012 2:23 PM

Answers

  • The issue is the word problem:

    "Could you explain your statement better? How can you have a duplicate problem when you use the identity column? Should the [TRC_Tran_Code]  column in the example be required to be a unique key value  in the table also? Do I want like a"

    With just a key on the identity column, you will get physically unique rows, but not logically.  Example:

    CREATE TABLE Tran_Codes
    (
     [TRC_Code_Id] [int] IDENTITY(1,1) NOT NULL,
     [TRC_Tran_Code] [numeric](6, 0) NOT NULL
     PRIMARY KEY (TRC_Code_Id)
    )
    GO

    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    GO

    Now you have lots of rows that look different, but the important bits of data are all the same...

    SELECT *
    FROM Tran_Codes
    GO
    TRC_Code_Id TRC_Tran_Code
    ----------- ---------------------------------------
    1           1
    2           1
    3           1
    4           1
    5           1
    GO
    DROP table Tran_Codes
    GO

    But put a unique constraint on the code too:

    CREATE TABLE Tran_Codes
    (
     [TRC_Code_Id] [int] IDENTITY(1,1) NOT NULL,
     [TRC_Tran_Code] [numeric](6, 0) NOT NULL
     PRIMARY KEY (TRC_Code_Id),
     UNIQUE (TRC_Tran_Code)
    )
    GO
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    GO

    Now the second attempt to duplicate the value that I am most certain that you want to be unique causes an error:

    Msg 2627, Level 14, State 1, Line 1
    Violation of UNIQUE KEY constraint 'UQ__Tran_Cod__06826D32C4B74602'. Cannot insert duplicate key in object 'dbo.Tran_Codes'. The duplicate key value is (1).
    The statement has been terminated.
    GO

    SELECT *
    FROM Tran_Codes

    TRC_Code_Id TRC_Tran_Code
    ----------- ---------------------------------------
    1           1


    Louis

    Tuesday, April 3, 2012 2:25 PM

All replies

  • It hard to say without knowing the purpose. On the surface, it looks fine. Here's what i'm seeing:

    Rej_Contacts
    ------------
    MRC_Contact_ID (PK)
    MRC_Prefix    
    MRC_Last_Name
    MRC_First_Name
    MRC_Phone_Number
    MRC_Email
    MRC_Address
    MRC_City
    MRC_State
    MRC_Zip
    MRC_Update_Date
    MRC_Updated_By

    Rej_Plans
    ---------
    MRP_Plan_Id (PK)
    MRP_PLan_Number
    MRP_Contact_Id  (?? FK->Rej_Contacts)
    MRP_Parent_Organization_Name
    MRP_Update_Date
    MRP_Updated_By

    Tran_Codes
    ----------
    TRC_Code_Id (PK)
    TRC_Tran_Code
    TRC_Type
    TRC_Title
    TRC_Long_Definition

    Rej_History
    -----------
    MRH_Id (PK)
    MRH_Plan_Id (FK->Rej_Plans)
    MRH_Create_Date
    MRH_Code_ID (FK->Tran_Codes)
    MRH_Tran_Count
    MRH_Batch_Size
    MRH_Tran_Code_Description

    I'm assuming Rej_Plans.MRP_Contact_Id is supposed to be an FK.

    Thursday, March 29, 2012 4:02 PM
    Answerer
  • you are correct.

    What sql would  you recommend I loading the daily file?

    Thursday, March 29, 2012 5:39 PM
  • Hi,

    Try using SSIS or Use BCP to import/export in SQL server, refer the link below for BCP.

    http://msdn.microsoft.com/en-us/library/aa337544.aspx


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, March 29, 2012 5:46 PM
  • can you please describe your problem in more detail, i am not able to get it.
    Saturday, March 31, 2012 9:58 AM
  • Well, I would first suggest that you reconsider your names:

    CREATE TABLE [dbo].[Rej_Contacts]

    CREATE TABLE [dbo].[Rej_History]

    CREATE TABLE [dbo].[Rej_Plans]

    CREATE TABLE [dbo].[Tran_Codes]

    Consider using schemas instead of dbo to replace the prefix in the name.  So:

    CREATE SCHEMA Reg --not sure what reg is, but if that is a very standard term in your org, (short for organ donor team :)) then cool

    CREATE TABLE Reg.Contacts, Reg.History, etc.. Also not fond of the prefixes of TRC, MRH, etc. Just makes things harder to work with, and when you find a name clash (two things that should be MRH things will get even more confusing)

    The prefixes get really awful when dealing with queries that join on a relationship like:

    ALTER TABLE [dbo].[Rej_History]  WITH NOCHECK ADD  CONSTRAINT [PK_MRH_RNumber] FOREIGN KEY([MRH_Plan_Id])
    REFERENCES [dbo].[Rej_Plans] ([MRP_Plan_Id])

    How does the user know that MRH_Plan_Id is the same thing as MRP_Plan_Id? 

    ---------------------------------

    Second thing I notice is the use of WITH NOCHECK.  You want to create them WITH CHECK or the data that is in the table will not be checked, meaning the data might be incorrect. This also negates the optimizer using the constraint for performance purposes.

    Mechanically, the constraints seem fine (though they won't create in the order given, since the create of plans table is after the reference of it.

    -------------------------------------

    Lastly, consider adding a unique constraint on a column other than the identity based value.  Take the code table:

    [TRC_Code_Id] [int] IDENTITY(1,1) NOT NULL,
    [TRC_Tran_Code] [numeric](6, 0) NOT NULL,

    inserting two rows with the same tran code will work just fine, but is it? The only actual differences in the row would be data generated by SQL Server.  Trust me that a user will try to create the same row twice, or better yet, an automated process may try to create the same row 1000 times... Then you get to write a fun cleanup query :)

    ---------------------

    One last nitpick. A code column:

     [TRC_Tran_Code] [numeric](6, 0) NOT NULL,

    You may want to use a char(6) for a column like this, since you probaby want it to be all numbers filled in, and numbers less than 100000 would not be zero padded.  Not necessarily necessary, but it is a typical best practice/


    Louis

    Monday, April 2, 2012 2:33 AM
  • Could you explain your statement better? How can you have a duplicate problem when you use the identity column? Should the [TRC_Tran_Code]  column in the example be required to be a unique key value  in the table also? Do I want like a

    "Lastly, consider adding a unique constraint on a column other than the identity based value.  Take the code table:

    [TRC_Code_Id] [int] IDENTITY(1,1) NOT NULL,
    [TRC_Tran_Code] [numeric](6, 0) NOT NULL,

    inserting two rows with the same tran code will work just fine, but is it? The only actual differences in the row would be data generated by SQL Server.  Trust me that a user will try to create the same row twice, or better yet, an automated process may try to create the same row 1000 times... Then you get to write a fun cleanup query :)"

    Monday, April 2, 2012 8:05 PM
  • The issue is the word problem:

    "Could you explain your statement better? How can you have a duplicate problem when you use the identity column? Should the [TRC_Tran_Code]  column in the example be required to be a unique key value  in the table also? Do I want like a"

    With just a key on the identity column, you will get physically unique rows, but not logically.  Example:

    CREATE TABLE Tran_Codes
    (
     [TRC_Code_Id] [int] IDENTITY(1,1) NOT NULL,
     [TRC_Tran_Code] [numeric](6, 0) NOT NULL
     PRIMARY KEY (TRC_Code_Id)
    )
    GO

    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    GO

    Now you have lots of rows that look different, but the important bits of data are all the same...

    SELECT *
    FROM Tran_Codes
    GO
    TRC_Code_Id TRC_Tran_Code
    ----------- ---------------------------------------
    1           1
    2           1
    3           1
    4           1
    5           1
    GO
    DROP table Tran_Codes
    GO

    But put a unique constraint on the code too:

    CREATE TABLE Tran_Codes
    (
     [TRC_Code_Id] [int] IDENTITY(1,1) NOT NULL,
     [TRC_Tran_Code] [numeric](6, 0) NOT NULL
     PRIMARY KEY (TRC_Code_Id),
     UNIQUE (TRC_Tran_Code)
    )
    GO
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    INSERT INTO Tran_Codes (TRC_Tran_Code)
    VALUES (1);
    GO

    Now the second attempt to duplicate the value that I am most certain that you want to be unique causes an error:

    Msg 2627, Level 14, State 1, Line 1
    Violation of UNIQUE KEY constraint 'UQ__Tran_Cod__06826D32C4B74602'. Cannot insert duplicate key in object 'dbo.Tran_Codes'. The duplicate key value is (1).
    The statement has been terminated.
    GO

    SELECT *
    FROM Tran_Codes

    TRC_Code_Id TRC_Tran_Code
    ----------- ---------------------------------------
    1           1


    Louis

    Tuesday, April 3, 2012 2:25 PM