A Basic History Table - Foreign Keys And Deletes

Nov 19, 2007

Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when.

An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes.

If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes.

I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table?

Thanks,
-Dan

View 1 Replies


ADVERTISEMENT

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

Apr 11, 2006

Hello again,

I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:

Code:

CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

class_title
VARCHAR(50)
NOT NULL,

class_grade
SMALLINT
NOT NULL
DEFAULT 6,

class_tardies
SMALLINT
NOT NULL
DEFAULT 0,

class_absences
SMALLINT
NOT NULL
DEFAULT 0,

CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)

This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:

Code:

CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

grade_id
INT
NOT NULL,

CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),

CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)

Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?

In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?

Thank you for your assistance.

View 1 Replies View Related

2 Different Foreign Keys In One Table

Jun 13, 2006

There is itemlookup table, which stores item number and itemdescription. Also there is a child table, pricehistory, of theitemlookup table, which stores different prices and different dateranges. So it is one-to-many relationship. (Price can be stored morethan one with a different date range)And there is another table RequestItem that stores the foreign key ofthe itemlookup table to show the information of the itemlookup table.Then how do I know later which date range of the price was stored inthe RequestItem table? Since I only keep the foreign key of theitemlookup table, it will be impossible to keep track of the row of thepricehistory table if there are more than one data existed in thepricehistory table.Will it be a valid table structure to create a column for the foreignkey of the pricehistory in RequestItem table or any other ways tohandle this issue?

View 1 Replies View Related

Update Table, Foreign Keys Etc

Aug 16, 2004

I have two tables as below. I want to update a quote and change the item for which it is for. So I want to do an update statement to change the cat_ref that the quote is for. However, when I do this I get a foreign key conflict with cat_ref in the item table. How do I get around this? Thanks


Code:

CREATE TABLE Item (
cat_ref VARCHAR(5)PRIMARY KEY,
descrip VARCHAR(50),
date_added SMALLDATETIME,
cat_type VARCHAR(20))


CREATE TABLE has_quote (
quote_id INT IDENTITY (1,1) PRIMARY KEY,
date_last_pricecheck SMALLDATETIME,
cat_ref VARCHAR(5)FOREIGN KEY
REFERENCES Item(cat_ref)
ON DELETE CASCADE,
first_name VARCHAR(10),
surname VARCHAR(10),
FOREIGN KEY (first_name, surname)
REFERENCES Customer(first_name, surname)
ON DELETE CASCADE
ON UPDATE CASCADE)

View 5 Replies View Related

Create Table With Foreign Keys

May 30, 2007

How do I create a table with a foreign key

View 6 Replies View Related

Problem In Table With Two Foreign Keys

Feb 5, 2008

Hello,


I have a very elementary question on table design. This screenshot (which is part of a larger diagram) sets the context of the question.




There are two distinct groups of products with different attributes, hence two separate tables: Products_A and Products_B.
There's a third table that keeps track of daily production (DailyProduction), which must accept items from either product table. Now, my question is: How do I relate these two tables to "DailyProduction" ? If I place a double-constraint with two foreign keys, then I get an error whenever I attempt to insert a record in DailyProduction. For example, if the new item is of "Products_A" type, then the error message from SSMS is:


No row was updated.

The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DailyProduction_Products_B". The conflict occurred in database "MyDatabase", table "dbo.Products_B", column 'ProductB_ID'.
The statement has been terminated.


Correct the errors and retry or press ESC to cancel the change(s).


It's clear that the error message makes sense as SQL is trying to apply BOTH constraints. It's one AND the other, not one OR the other. Now, how do I remedy this ? What would be the most appropriate way to relate the three tables ?


Thanks.

Fernando

View 3 Replies View Related

Insert Records Into A Table With Foreign Keys

May 21, 2008

i'm using sql express, management studio express and a visual web developer starter kit.
i have 4 tables: items; categories; categorization; old_items
the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys.
i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it.  as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self.
i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them.
can anyone help?  i've been trying all the permutations of queries i can think of and not getting it.
thanks.

View 5 Replies View Related

Disabling Or Getting Rid Of Foreign Keys So I Can Drop My Table

Apr 18, 2007

Kinda new to SQL, using SQL Server 2005.I have some foreign keys in a couple of tables. I need to drop thesetables, but can't since I'll get the error:Msg 3726,Level 16, State 1, Line 1Could not drop object 'Client' because it is referenced by a FOREIGNKEY constraint.I'm not sure how to disable or get rid of these foreign keys so that Ican drop my tables. I tried:ALTER TABLE Client NOCHECK CONSTRAINT ALLAlter Table Client Drop Column Foreign Key Boss_ID;I went into the Object Explorer and deleted the FK lines from eachtable, but still the same error.What am I doing wrong?Thanks for your help.

View 1 Replies View Related

How To Detect That A Sqlserver Table Row Has Foreign Keys And Cannot Be Deleted?

Mar 27, 2006

What is the best way to detect that a sqlserver table row has foreign keys and cannot be deleted?Thanks,Keith

View 1 Replies View Related

SQL 2012 :: Create Table With Both Keys As Foreign (error)

Dec 15, 2014

I'm trying to create a table in Microsoft Server Management Studio 2012. The table has two fields which are both foreign keys.

I created the following:

create table tblRoomEquipment(
RoomID nvarchar(8),
EquipmentType nvarchar(1),
foreign key (RoomID) references tblRoom(ID),
foreign key (EquipmentType) references tblEquipment(Type)
)

Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!

Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.

View 6 Replies View Related

Transact SQL :: Identify Primary And Foreign Keys In Two Table

Apr 23, 2015

I've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?

CREATE TABLE StockNames
(
-- Added Primary key to [stock_symbol]
[stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY,
[stock_name] VARCHAR(150) NOT NULL,
[stock_exchange] VARCHAR(50) NOT NULL,

[code].....

View 19 Replies View Related

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

Jul 16, 2014

what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:

ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])

What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

View 4 Replies View Related

2 Foreign Keys To The Same Table - Multitable Selection Query Problem

Jun 23, 2006

Hello,I have 2 tables:- Customers with columns:customerID(prim_key),customerName(with customer's names)- Deliveries with columns:deliveryID(primKey),sender(ref_key to CustomerID from Customers),receiver(also ref_key to CustomerID from Customers);I need to select all data about deliveries, but instead of havingsender's ID and receiver's ID, I need to have their Names.I tried to do:SELECTdeliveries.deliveryID,Customers.customerName AS sender,Customers.customerName AS receiverFROMcustomers, deliveriesWHERECustomers.customerID=Deliveries.sender ANDCustomers.customerID=Deliveries.receiver;But this only works if sender=receiver, which is obvious ;)I'd like to know if there is any other way for obtaining those datawithin one queryThank you very much for your helpChris

View 2 Replies View Related

Transact SQL :: Left Join To Get Info From A Table With Foreign Keys

Sep 13, 2015

I am still new to SQL and I am having trouble obtaining the results I need from a query. I have worked on this command for some time and searched the internet but cannot seem to still get it correct.

I have a table called Patient. It's primary key is pat_id.

I have a second table called Coverage. It has no primary key. The foreign keys are pat_id, coverage_plan_id, and hosp_status.

I have a third table called Coverage_History.   It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from. 

I want to get the pat_id and all the coverage information that is current.   The coverage table contains specific insurance policy information.   The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this:

Patient (pat_id and lname)
P123  Monto
P124  Minto
P125  Dento
P126  Donto

Coverage (pat_id, coverage_plan_id, hosp_status, policy_num)
P123     MED1   OP   A1499
P123     ACT4   OP   H39B 
P124     MED1   OP   C90009
P124     RAC    OP   99KKKK
P124     RAC    OP   99KKKK
P124     MED1   OP   C90009
P125     ARP    OP   G190
P126     BCB    OP   H88

Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to)
P123   OP   1   20150102  MED1    NULL
P123   OP   2   20150102  ACT4    NULL
P124   OP   1   20150203  RAC     20150430
P124   OP   2   20150203  MED1    20150430
P124   OP   1   20150501  MED1    NULL
P124   OP   2   20150501  RAC     NULL
P125   OP   1   20150801  ARP     NULL 
P126   OP   1   20150801  BCB     20160101

select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority,
       from patient p   
     left join
  ( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and
                                   (ch.effective_to is NULL or ch.effective_to >= getdate()
)
         ) on ch.patient_id = p.patient_id
        
    where ( ch.effective_to is NULL or ch.effective_to >= getdate() )
   
So I want to see:

P123  Monto  MED1  OP   20150102    NULL       1
P123  Monto  ACT4  OP   20150102    NULL       2
P124  Minto  MED1  OP   20150501    NULL       1
P124  Minto  RAC   OP   20150501    NULL       2
P125  Dento  ARP   OP   20150801    NULL       1
P126  Donto  BCB   OP   20150801    20160101    1

View 6 Replies View Related

Transact SQL :: How To Apply Foreign Keys On Top Of A Common Lookup Table

Apr 29, 2015

I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views.

I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.

CREATE TABLE dbo.CodesTable (
PartyGrpId INT  NOT NULL
  , CdTyp  VARCHAR ( 8 ) NOT NULL
  , CompId INT  NOT NULL
  , CdValue VARCHAR ( 8 ) NOT NULL
  , CdValueDesc VARCHAR ( 255 ) NULL
  , AltValueDesc VARCHAR ( 100 ) NULL

[Code] ....

I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this.

ALTER TABLE dbo.BusinessStatus WITH NOCHECK
ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'Status'

View 5 Replies View Related

My Table That Uses Aspnet Foreign Keys Won't Display Anything - CAUTION - Noob At Work

Apr 12, 2007

I've created a new table in ASPNETDB.mdf called 'customerInfo'. Most of the fields are, for the moment, data that exists in one of the existing ASPNET tables eg Membership_UserId (primary key), Membership_Email, Users_UserName etc.; the list goes on. I have put a new column 'amountspent' - just to test that I can do what I think I can do, however the table doesn't display any data. Am I OK in thinking that I can use the aspnet data in my own tables and reference through foreign keys to them. I've made sure that the procedure that I used to put the table into a gridview on the webpage is OK as I did the same thing to the membership table and that displays all of its data. Can someone either explain the steps I need to make this happen correctly or point me in the direction of a noob type walkthrough.
Thanks 

View 1 Replies View Related

Foreign Keys - On Which Kind Of Keys Do The Base On?

Nov 22, 2007

Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz

View 6 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE &&amp; BUKL INSERT:How To Designate The Primary-Foreign Keys &&amp; Set Up Relationship?

Jan 28, 2008

Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View 6 Replies View Related

Foreign Keys

Apr 17, 2007

Can any body tell me how to know to what columns of other table it refers to.
shiva kumar

View 2 Replies View Related

Foreign Keys

Aug 24, 1999

Could someone enlighten me as to the advantage of using the foreign key tab when in table design mode in the Enterprise Manager. Does it have any advantages ?? Is it necessary ??

thanks in advance

Paul

View 1 Replies View Related

Foreign Keys

Mar 29, 2001

I haven't tried, but does anyone know if its possible to a have a foreign key for two tables when the tables reside in different databases (on the same server)?

Thanks,
Doug Smith

View 1 Replies View Related

Foreign Keys

Oct 25, 2001

l'm trying to do inserts on tables with foreign keys and they keep crashing. Can somebody please help.Whats the best way of populating data that has foreign keys?

View 1 Replies View Related

Foreign Keys

Oct 4, 2004

How would I drop a foreign Key?

Thanks

Lystra

View 1 Replies View Related

Keys, Foreign Key?

Mar 10, 2004

I need to know if this is required? I have products, with the help of their business account numbers, are naturally categorized numerically. I want to create a product category table and a product account table.


Example :
tbProductCategories
TypeCode | Description
1000 | Cups
2000 | Plates

tbProductAccounts
Account | Description
1001 | Mug
1002 | Glass
2001 | Plate
2002 | Saucer


With the above tables (which are made up :) ), would you include
a foreign key in tbProductAccounts indicating the type code?

What would the stored proc look like without it?


Create Procedure usp_GetProductAccounts

@iTypeCode int

AS

SELECT tbProductAccounts.Account,
tbProductAccounts.Description
FROM tbProductAccounts
WHERE tbProductAccounts.Account - iTypeCode > 0 AND < 999

Would this work? Or should the foriegn key always be included?

Mike B

View 4 Replies View Related

Foreign Keys - Allow Zero?

Jun 8, 2008

I am creating a series of foreign keys in my new database, and so far everything is going fine. The company I work for never uses them, and I am working on my own stuff on my own time now.

I come across one table that I do want one field to allow zeroes, but when its set to a value, to exist in the other table.

To be more specific. I have a PO type table, which has an EmployeeID of the person who made the PO, and another field to store who received the PO when the order arrives. I want that 2nd Employee field to be zero until it's received.

I created an employee of zero, to allow the foreign key to be created. But all the other tables that have foreign keys to my Employee table I would prefer to not allow zeroes. So I changed them to use a check of (employeeid > 0).

Is it possible to have a foreign key say that I want the value from the Field in Table A to exist in table B, or to be zero? Or would it just be easier to leave off the foreign key in this one case?


Tks

View 4 Replies View Related

Foreign Keys

Dec 15, 2006

Shiry writes "Hi, I'm a beginner and I'm a bit stuck here..

I'm creating this database for my homework, I'm using Marks & Spencer as an
example. It has a table, products, for the clothes

id
name
cat_id ....... connected to typ_cat
colour_id ...... connected to typ_colour
size_id ....... connected to typ_size
price
sup_id ....... connected to typ_sup ...... but here i'm stuck a bit, are you
allowed to connect a typ_table to another typ_table?

this is the typ_sup:
id
name
address
city_id ......... connected to typ_city

or rather have a separate sup table with the same id, name, address, city_id
id will be connected to typ_sup
and the products table.. sup_id will then be connected to typ_sup..?

What way is better? and is it allowed to connect a typ_table to another
typ_table?

Thanks in advance!!!!

Shiry"

View 1 Replies View Related

Foreign Keys

Oct 11, 2006

I have created two tables:

UserInfo:

UserID (PK)

Hairid

eyeColorid

faceShapeid



and

HairInfo:

HairInfoID (PK)

Hairid

HairDesc

Now I want to have a one to many relationship between UserInfo and HairInfo. I want to specify Hairid as the foreign key in the HairInfo table. Here UserInfo is the parent and HairInfo is the child. I am using SQL server 2000. Is there a way to do it using the Enterprise manager interface. Can someone run me through the steps to do it.

Thanks...



View 1 Replies View Related

Set-Off Foreign Keys

Feb 28, 2008

Is there a way to temporaly set off foreign keys dependencies (like a sql command or something...) and then set them on again? I've to migrate 90 tables on my app, but i get the "dependencies error", any ideas?

View 4 Replies View Related

Use Of Foreign Keys

Mar 24, 2008

having been used to creating databases for the past nine years in MS Access, i have been recently getting used to the basics of SQL Server 2005 express edition. i know pretty much all of the simple basics, but i have hit some trouble.

in MS Access, when i have used the "Design View" to develop all of my tables, fields and validation rules, i have used their "Lookup Wizard" to develop ways of assigning a record to a certain value - example;

CLUB:
Club_Id (Primary Key)
Club

PLAYER:
Player_Id
Player_Name
Club (Foreign Key)

the flaw, i suppose, is the ease with which i can create a relationship between TWO tables without the child tables foreign key having to be an ID (integer) and users can select a "Club" of their choice to assign a "Player" to in the PLAYER table. i.e.

CLUB:
Club_Id: 1
Club: Man Utd

PLAYER:
Player_Id: 1
Player_Name: Ryan Giggs
Club: Man Utd (FKEY)

instead of;

PLAYER:
Player_Id: 1
Player_Name: Ryan Giggs
Club: 1 (FKEY)

i hope i am making sense. now the awkward bit as far as i'm concerned:

in SQL Server 2005 Management Studio Express, i cannot do this once i do the necessary tables, content and relational diagrams.

so i have this;


PLAYER:
Player_Id: 1
Player_Name: Ryan Giggs
Club: 1 (FKEY)

instead of what i want, which is this;


PLAYER:
Player_Id: 1
Player_Name: Ryan Giggs
Club: Man Utd (FKEY)

how can this be achievable using Studio Management Express 2005 for someone like myself who is still something of an SQL novice (of a sort)

help would be much appreciated

View 3 Replies View Related

Basic Foreign Key Question (Asp.net)

Feb 12, 2008

I have a very basic question on SQL Server foreign keys and ASP.NET. I have a Projects table represented by a FormView, and I have ProjectTasks table that is represented by a GridView nested in the formview. The ProjectTasks table has a Project_ID column which has a foreign key constraint (the Project_ID must exist in the Projects table). I started by setting up the EditItemTemplate of the FormView, and as a result, when editing a record in the Projects table, the Project_ID is already known making it possible to edit or add ProjectTasks via the nested GridView. 
Here is my question. If I want to add a new Project, I will start with a blank form and will fill out the fields. The Project_ID (a Primary Key in the Projects table) will be automatically generated for me when I add the record. Prior to clicking the Add button, I would like to be able to fill out the ProjectTasks GridView, even though I don't have a Project_ID yet to use in my ProjectTasks records. Should I store the ProjectTasks in a temporary table without a foreign key constraint and then copy them to the proper table once I have a Project_ID?
 
Thanks for the help!

View 2 Replies View Related

Basic Foreign Key Question

Mar 29, 2008

Hi, I am new to SQL. I am trying to set a foreign key relationship within the same table, but when I drag the relationship to the same table I get the error, "The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint." What would be the cause of this?

Thanks.

View 2 Replies View Related

Remote Foreign Keys

Jun 21, 2007

Right now i'm building a language centre DB. Is going to hold translations for data in tables in another DB (english DB). The idea is going to be that there is going to be a table in the Language DB for every language and table it is going to translate in the english DB.
So lets consider the following in the English DB:
PROJ_TBL_HELPTOPICS  -> PK_HELP_ID -> TITLE -> DESCR
PROJ_TBL_CATEGORIES -> PK_CAT_ID -> TITLE -> DESCR
 
In the Language DB I want to hold translations for HELPPTOPICS and CATEGORIES, and I want translations for Spanish and Japanese.
PROJ_TBL_HELPTOPICS_ES -> PK_TRANS_ID -> FK_HELP_ID -> TRANS_TITLE -> TRANS_DESCR
 
The rest is going to be the layout as abovePROJ_TBL_HELPTOPICS_JA PROJ_TBL_CATEGORIES_ESPROJ_TBL_CATEGORIES_JA
The reasons I separated up the language DB from the english DB are:
1. English DB has, and is going to have a lot more tables, and is going to be heavily queried (plus I think dont think the translations are going to be used anywhere near as often and the english). I figured the less tables, where possible, the better.
2. Putting translations in different a different DB, I could take better advantage of colliations specific to a language for example when using Full-Text searching on Japanese text
 
Anyways, here's my question!?!
I want to link the foreign key column to the table it is translating primary key column (in the English DB). I want to be able to take advantage of Cascade on Delete. So when an item is deleted from EnglishDB.PROJ_HELP_TOPICS it is going to be deleted from LanguageDB.PROJ_HELP_TOPICS_[LANG ISO]. Is this done through Mirroring?

View 6 Replies View Related

Help On Foreign Keys And Tables

Jan 7, 2008

hi.
How to update FormA table from customer table. Let say i wish to keep small number of fields from each table so i use foreign keys as reference.
However i had a problem when i tried to save the relationships of both tables, i receive the error that FormA_id is not able to insert null into value.
Cust_id(PK) is identify column, as well FormA_id(FK) and FormA_id(PK) too. For example, when i insert a record from customer table, it will automatically create id for FormA.
Table structure. Customer
cust_id(PK),name,age,formA_id(FK)
Table structure, FormA
formA_id(PK), info, date,
How to solve ?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved