Finding Columns That Are Intended For Foreign Keys

Apr 24, 2006

Hi Friends,

me again. I am trying to find out if all of my intended foreign keys are actually set as foreign keys programmatically.

Ive stuffed something up, please find my error

USE RIQDB1
SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE ((column_name Like '%fk%') AND (FROM Information_schema.type = F))
go

thanks
Cm

View 9 Replies


ADVERTISEMENT

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

Finding Duplicate Foreign Keys

May 29, 2007

Hi

i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc

but i am not able to get the fks created more than once on same column refering to same pk

Thanks in Advance

View 4 Replies View Related

SQL 2012 :: Explicit Columns Or NULL Foreign Keys?

Mar 19, 2014

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?

View 6 Replies View Related

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.

View 4 Replies View Related

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

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

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

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

Help With Deleting And Foreign Keys.

Feb 3, 2008

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 :)

View 11 Replies View Related

Removing Foreign Keys

Feb 1, 2004

Hi everyone,

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.

Any help will be appreciated!!

Thanks!
Andrew

View 1 Replies View Related

Novice Needs Some Help With Foreign Keys

Sep 4, 2005

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.

Thanks all

View 2 Replies View Related

Triggers And Foreign Keys

Aug 25, 1998

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.

HELP!!!!
Cheers, Marc

View 3 Replies View Related

Foreign Keys For Some Rows, Not Others

Jan 14, 2005

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.

View 4 Replies View Related

Foreign Keys IDENTITY

Aug 2, 2004

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


Code:

CREATE TABLE Item (
cat_ref INT IDENTITY(1,1) PRIMARY KEY,
descrip VARCHAR(50),
date_added SMALLDATETIME,
cat_type VARCHAR(20),
contract VARCHAR(10),
cost_price SMALLMONEY,
supplier_name VARCHAR(20),
supplier_phone VARCHAR(20))


CREATE TABLE has_quote (
quote_id INT IDENTITY (1,1) PRIMARY KEY,
installation_charge SMALLMONEY,
date_of_quote SMALLDATETIME,
commercial_markup SMALLMONEY,
service_desk_contact VARCHAR(20),
category INTEGER,
date_last_pricecheck SMALLDATETIME,
cat_ref INT FOREIGN KEY
REFERENCES Item(cat_ref),
cust_id INT FOREIGN KEY
REFERENCES Customer(cust_id))

CREATE TABLE Customer (
first_name VARCHAR(10),
surname VARCHAR(10),
customer_phone VARCHAR(20),
contract VARCHAR(10),
location VARCHAR(20),
email VARCHAR(50),
cust_id INT IDENTITY (1,1) PRIMARY KEY)

View 3 Replies View Related

Multiple Foreign Keys

Jun 25, 2004

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?

Let me know! Thanks!!

View 1 Replies View Related

Multiple Foreign Keys

Nov 20, 2004

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?

Thanks!

View 5 Replies View Related

Multiple Foreign Keys

Dec 30, 2004

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?

Thanks!

View 2 Replies View Related

Should I Index Foreign Keys?

Mar 14, 2004

Hi all

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?

Thanks
Matt

View 4 Replies View Related

Updating Foreign Keys

Dec 13, 2005

I am brand new to SQL Server 2000 and have encountered an issue. I created a DB with 5 or so tables all of which contain different client information. All tables are related via the Client_ID primary key I set in my main table, and foreign key in all the subsequent tables. Im using a Microsoft Access Project form as the front end.

Im having trouble figuring out how to populate the subsequent tables with the Client_ID after a enter the data in the Main table where the primary key resides.

Do I create a trigger or a stored procedure...am i even in the ballpark?

thanks in advance.

View 2 Replies View Related

Foreign And Primary Keys

Oct 19, 2005

I have an application in which i need to get the foreign key fieldsfrom a table and then get all the foreign keys primary key field fromthe linking table. Could some one tell me how i do this usingINFORMATION_SCHEMA. I have tried and can get the foreign keys but notsure how to get the associated primary keys.

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







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