|
jassie
Constraint Violating Yak Guru
332 Posts |
 Posted - 2012-03-29 : 10:25:46
|
| 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 better solution 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 thedata. 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]GOALTER TABLE [dbo].[Rej_History] WITH NOCHECK ADD CONSTRAINT [PK_MRH_RNumber] FOREIGN KEY([MRH_Plan_Id])REFERENCES [dbo].[Rej_Plans] ([MRP_Plan_Id])GOALTER 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]----------------- |
|
foreign key and load data