DB Design :: Remove Foreign Keys For Audit Columns?
Aug 25, 2015
We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table. This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.
For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables? While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.
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?
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tbl_position_tbl_departments". The conflict occurred in database "MyProjectDB", table "dbo.tbl_departments", column 'ID'. All of my tables have an ID field that is a primary key with Allow Null unchecked.
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
Sample Data would be 1 | CRYS | CRYSTAL STAIRS 2 | MAOF | MEXICAN AMERICAN FOUNDATION 3 | PATH | PATHWAYS 4 | CCRC | CHILD CARE RESOURCE CENTER 5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA ==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused: (1) Is the use of FK as a Constraint any different than using an INDEX and how? (2) Should I Alter the Tables to include CASCADING Up/Down? (3) Are the use of CHARs Ok for the Keys? (4) Have I over/under-normalized any of the relationships?
I have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?
I have a table called Appointment, for storing (you guessed it) appointments at a medical practice. If an appointment is cancelled, I want to collect a cancellation reason, so let's say that I create a second table called Cancellation which has a foreign key relating to the Appointment table's primary key, AppointmentID, and another column, Reason. Now, in order to indicate that an appointment was cancelled, I could include a Cancelled column in the Appointment table with a bit datatype, or instead I could infer that an appointment must be cancelled if it has a corresponding record in the Cancellation table.
It may be that it'd be better to store the cancellation reason in the Appointment table - But regardless, let's say I stick with the two-table solution described above, and I subsequently want to write a query to list all appointments which have been cancelled. If I had the Cancelled column in the Appointment table, I could simply query for all records in that table where that column's value was FALSE. If I went the other way and DIDN'T have a Cancelled column, I could instead write a query joining the Appointment and Cancellation tables to return all records in Appointment with a corresponding record in Cancellation.
That latter method, whilst slightly more complicated because it involves joining two tables, seems to me to be the most normalised. Instead of storing the fact that an appointment is cancelled in two different tables, that fact is only stored in the Cancellation table. Would there be a performance hit in using the two-table, 'inferred cancellation' method rather than just having a bit column in the Appointment table? Would that performance hit be enough to persuade you to use a Cancellation column in the Appointment table instead? And what about if I were to apply that method to other things associated with each appointment, e.g. Is it completed? Is it chargeable to the client or an insurance company? Is the client and in-patient or out-patient?
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,
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,
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?
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
I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -
LoginName varchar(100) - user name Action varchar(5) - this will store 'INSERT','UPDATE','DELETE' TableName varchar(30) - name of the table updated PrimaryKey int - primary key of the record updated ColumnName varchar(30) - name of the column updated OldValue varchar(1000) - old value converted to varchar NewValue varchar(1000) - new value converted to varchar RecUpdDate datetime - record update date.
This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.
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 ??
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)?
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?
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.
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?
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?
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.
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?
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)
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?
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 ?
I'm working with a SQL 2000 DB, which has a table structure as follows, implemented with foreign key constraints: ________________________________| ||--> TABLE1 --> TABLE2 --> TABLE3 --| (ie, it forms a 'loop' so to speak) I am trying to create a clean copy of this database, basically by deleting all the data in most of the tables, and these 3 tables are included. Of course, when I run a DELETE on any of these tables, it complains because of the FK. How can I script a delete on the 3 tables without manually deleting row after row, until there aren't anymore relations? Cheers for any help :)
I've been using VS.NET to design and implement my SQL databases. This has been simple and effective.
I've deployed the database onto the production database server and it's been performing without a hitch for some time now.
My problem is that I now need to remove a foreign key relationship on the production database as I have had a relationship between two tables but now not wanting the relationship since the data that had the relationship is now not wanting to be compulsory. I've had a look in the system.foreignkey (I think) table but I can't make head or tail of it.
Long story short, how can I find and remove the relationship from Query Analyser / Enterprise Manager ? I know how to do this in VS.NET diagram (just delete the relationship) but when you open the production database in VS.NET the diagram is no longer there.
Hey all - been knocking my head on this on for awhile - perhaps someone could shed a little light. I have 2 tables that I want to link: Table 1: UserID - Primary key First Name Last name AuditionID - FK
Table 2: AuditionID - PK Description Sing instrument
So - I have a web form that collects the information for both tables and then insets the collected data. But, the AuditionID value in Table1 does not update. Now, I assume I am incorrect in thinking the AuditionID in Table1 will be automagically updated when Table2 creates it's key...? If that is indeed the case, how do you update 2 tables and maintain a foreign key relationship?
I hope I am being clear enough - any help would be greatly appreciated.
Hi! Does anybody know why after applying both triggers and foreign keys to a table (standard relational database procedure!!!), when I try to perform, say a simple delete, I am not allowed to do so and get this error message:
Msg 547, Level 16, State 2 DELETE statement conflicted with COLUMN REFERENCE constraint `FK_contract_1__14`. The conflict occurred in database `tmp`, table `contract`, column `employee_id` Command has been aborted.
I have a database with a set of items that have different ways of describing their locations. One type of item uses states, the other uses some arbitrary region that could cross state boundries. Every item in each state/region is numbered starting at 1, so that each item within a state/region has a unique area number.
So I have a table of states (that doesn't need maintaining) and a table of regions (that does. New regions could be added, existing regions could be modified.)
CREATE TABLE states ( stateId int primary key, stateCode char(2), stateName varchar(16), UNIQUE(stateCode) )
CREATE TABLE regions ( regionId int primary key, regionCode char(6), regionName varchar(32), UNIQUE(regionCode) )
My table of items points to these other tables. I want to use foreign keys for referential integrity. So my table looks something like this:
CREATE TABLE items ( itemId int primary key, itemName varchar(32), itemType ENUM('state', 'region'), areaNum int, stateId int NULL, regionId int NULL, UNIQUE(itemName), UNIQUE(areaNum, stateId, regionId), FOREIGN KEY stateId REFERENCES states (stateId), FOREIGN KEY regionId REFERENCE regions (regionId) )
My problem seems to be that when I insert records into the items table, MS SQL doesn't like a foreign key entry to be null (even though I say it can be null.) I think the problem is that there is no value in the states or regions table with the stateId/regionId = 0. I don't want to entry a dummy entry for a non-state or non-region, but I can't figure out how to do this otherwise.
Is there a way to have MS SQL permit a foreign key value to be NULL? Or is there a better way to organize this data? I would appreciate any guidance. Thanks.
I've posted this in the general database forum as well but think it is more sql server specific. I have three tables as below. My problem is that when I insert anything into has_quote, cat_ref and cust_id are both null. Any ideas on how to get round this? Thanks
What are the possible issues I could run in to having multiple foreign keys in a table. Here is why I ask. I have a db (sql server) that has a participant table, a forum table, and a forum reply table. Every record in the forum reply table is associated with the forum table via a PK-FK relationship w/cascading updates/deletes. The participants who post in these tables are not tied back to the participant table via a PK-FK relationship w/cascading updates/deletes. Should they be?
The problem I ran in to is that one particpant was deleted from the participant table but a post with their partid still existed in the forum or forum reply tables.
My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right? If I am right, what do I have to be careful of if I do that?
I am constructing a db in sql server 2000 that will score cross-country running meets. I have an individual results table that needs to only contain participants that are entered as participants but are specific to a certain race as well. Can I have this table be linked back to TWO other tables via the PK-FK relationship and what issues might I have doing that?
I am having trouble creating multiple foreign keys on a table so that I can set up cascading update and cascading delete from two different primary tables. I am using the diagram to do this but when I try to save the diagram I get the following error.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.
What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?
What is the general consensus regarding indexing of foreign keys in tables?
I have a lot of queries that perform inner joins from the 'primary key' table to a 'foreign key' table. I was wondering if there are any special considerations to take into account.
For example, I think in some RDBMS, foreign keys are sort of automatically assigned indexes? Is there anything that SQL Server automagically does in the background when a foreign key constraint is created that may affect whether something should be indexed?
