Find Out The Columns Should Be Referred Through Foreign Key

Aug 2, 2007

For example if we clearly define a foreign key in the master and child table as following script -

CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int CONSTRAINT fk_master_child
FOREIGN KEY (fkey) REFERENCES master(pkey))


We can find out the two tables reference relationship by looking at INFORMATION_SCHEMA tables.

However, if the two tables are created in this way €“


CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int)


Does any one know how to programming verify the actual reference relationship exists between pkey and fkey in these two tables?


Thanks

View 1 Replies


ADVERTISEMENT

How To Find All Primary And Foreign Key Columns In All Database Tables

Apr 17, 2014

how to find all primary key columns & foreign key columns in all database tables?

View 1 Replies View Related

SQL 2012 :: Find All Table Names That Have Foreign Key

Oct 20, 2015

Is there a way to find all the table names that have a foreign key that references FILE_ID_IN([FILE_ID])

Note: The table def below has a foreign key

IF object_id('CODE_MAP_IN', 'U') IS NOT NULL DROP TABLE CODE_MAP_IN
Print 'Creating table CODE_MAP_IN...'
Create table CODE_MAP_IN
(
[CODE_MAP_FR] [varchar] (53),
[CODE_MAP_TO] [varchar] (53),
[FILE_ID] [float] DEFAULT 1000
PRIMARY KEY ([CODE_MAP_FR]),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]

GO

View 2 Replies View Related

Foreign Key Columns

Aug 8, 2006

I am deleting a column from a table in code.  Before I drop the colum I need to find if the column belongs to a foreign key.  I have the names of the foreign keys for the table from sysobjects.How can I determine what columns are part of the foreign key?

View 9 Replies View Related

T-SQL (SS2K8) :: Find Primary Key Table Name And Field Name Of Foreign Key

Apr 10, 2015

How can i find the primary field name and primary table name of a given foreign key.

For example

Table A:
IDa
Column1
Column2
Column3

Table B:
IDb
column1
column2
column3 <---- this is foreign key to table A's IDa

What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?

View 4 Replies View Related

Query Foreign Key Columns And Tables

Dec 20, 2006

I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table  Any help would be great, thanks 

View 5 Replies View Related

Could Not Create A Foreign Key On 2 Unique Columns

Feb 12, 2006

Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.

I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)

CREATE TABLE T_FK (ID1 INT, ID2 INT)

And I want to do:

ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)

I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.

Thank you.
Have a nice day.

View 4 Replies View Related

Linked Server Referred By Insert Trigger

Jul 23, 2005

Hei,We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separatedmachines).I am triing to connect them sĺ that when one row is added to the table inthe database in main server - then the same row is added to the same tablein the second server database.I made the insert trigger on the table in the first server ( the secondserver is added as a linked server):-----------------------------------------------------------------------------------------create trigger ti_myTabe1 on myTable1 for insert asbegindeclare ........BEGINinsert into server2.myDatabase2.owner.myTable2(column1, column2, column3)SELECT column1, column2, column3FROM inserted insEND......end-----------------------------------------------------------------------------------------When I run the statement in "SQL Query Analyzer"on the first server:insert into Table1 values(va1,val2,val3)then error is coming:Server: Msg 7391, Level 16, State 1, Procedure ti_myTabe1 , Line 19The operation could not be performed because the OLE DB provider 'SQLOLEDB'was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].The straing thing is: if I run the statement in "SQL Query Analyzer"on thefirst server:insert into server2.myDatabase2.owner.myTable2 values(va1,val2,val3)then it works!But not inside the trigger!!! - What I am doing wrong ?Any idea is greatly appeciated.

View 2 Replies View Related

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 View Related

Transact SQL :: Delete Data From Table Referred In Other Tables

Oct 14, 2015

I've a table named Master in which a column is referenced in other tables like Child1, Child2,.. ChildN. I've deleted a part of data( say Column Id values 1,2,3,4,5) from all the Child tables which pointed to Master table Id column.

Now, I want to delete the same Id values from Master table as there rows are not referred in any of the child tables. When I try to delete the Id values 1 thru 5 from Master table, it is scanning all child tables for the references and taking lot of time for the deletion.

Is there any way to specify to the system(in the query) to delete the Master table values without scanning the child tables..?

View 5 Replies View Related

Transact SQL :: Row Handle Referred To Deleted Row Or A Row Marked For Deletion

Sep 2, 2015

We are getting below error while stored proc is being run:

SqlException: Cannot get the data of the row from the OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds".

OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds" returned message "Row handle referred to a deleted row or a row marked for deletion.".

Here is the definition of the stored proc:

CREATE PROC [dbo].[usp_MobileAppOnClick_InsertNoCabsTranData_Log]
(
@CustomerMobileNo VARCHAR(40),
@City VARCHAR(50) = '',
@PickUpLat REAL,
@PickUpLng REAL,
@NextAvailableSlot_Meru VARCHAR(20),

[Code] ....

View 2 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

How To Find No. Of Columns ?????

May 5, 2008

Hi is there any way to find no. of columns in a database table
like we can do that in Oracle by typing DESC tablename

San

View 5 Replies View Related

Find Max Values From 3 Different Columns?

Mar 9, 2008

Hi I want to find 3 different columns maximum values in one shot. Like I tried to use a reader to go through results but it kept coming back with index out of bounds. Right now to get it to work I got to use a ExecuteScalar() get the first columns max value and then open the connection again(since it seems after ExecuteScalar() it closes the connection) and then do the ExecuteScalar() again. Open the connection again and do the ExecuteScalar() again. Theres got to be a better way of doing this. 

View 3 Replies View Related

Find Lowest Value In 3 Columns

Jun 2, 2008

Im having trouble finding a solution to comparing columns to find the lowest value.

Example

Row 1

COL 1 = 10
COL 2 = 20
COL 3 = 60
COL 4 = 5
COL 5 = 35

I would want to return 5.

I tried to write a case statement such as below but am unable to use Logical Operators.

WHEN C1 <= C2 and C1<= C3 THEN C1
WHEN C2 <= C3 THEN C2
ELSE C3



any help would be great.

Thanks,
Nick

View 1 Replies View Related

Find Out Similar Columns

Apr 19, 2007

i like to write a sql query to find out similar columns in different tables.

i have something like this. but my logic is wrong.

select *
from information_schema.columns a
join information_schema.columns b
on a.column_name = b.column_name
and a.table_name <> b.table_name

View 1 Replies View Related

How Can I Find All Columns, That The Key Is Made Of?

Nov 27, 2006

In every database we have 'sysobjects' table storing names of all objects(including all keys)  in our data base. How can I find all columns, that the key is made of? Sorry about my english :-/

View 1 Replies View Related

To Find Ratio Between Two Columns

Oct 16, 2007

Can anyone pls help me to solve my problem in SSRS. Also I wanted to plot the 'Ratio' in to a chart against the 'Year'.

The scenario is:

Year Amount_A Amount_B Ratio(Amount_A/Amount_B)
05 x y x/y
06 a b a/b
07 m n m/n

Its very needy.

View 9 Replies View Related

Cannot Find Columns From A Stored Procedure...

Jul 21, 2007

I have an application that I inherited, and I have a annoying problem.  We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table.  When I run the stored procedure against SQL Server I receive the expected output.  What would make this random act happen, any ideas?
Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed.  I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?

View 3 Replies View Related

Find Out Which Table Columns Data Comes From

May 9, 2008

TableName: EmployeeCloumns:     EmployeeID                  EmployeeName                  If I do "Select * From Employee", is there a way to do something like this...
Loop through each result   IterateThroughControls.ID &= dbTable & dbColumnName & "<br />"EndSo that the end result would assign ID values...EmployeeEmployeeIDEmployeeEmployeeName

View 5 Replies View Related

Find The Smallest Number In Two Columns Was: How Do I Do This ?

Jun 15, 2005

Hi,
I have a sql query which gives me the result set with lots of columns and rows.

a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395

i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?

Thanks

View 1 Replies View Related

Find Columns Which Execute Updatetriggers

Oct 9, 2006

Hello,

I want to find the columns which execute updatetriggers. Is there in sqlserver a way to find out?

In Oracle you can say:

SELECTCOLUMN_NAME
FROMDBA_TRIGGER_COLS
WHERETRIGGER_OWNER = 'MyOwner'
AND TRIGGER_NAME = 'JobUpdate'
AND TABLE_NAME = 'Job'
AND COLUMN_LIST = 'YES'

I tried: EXEC sp_depends "dba.JobUpdate".

This says nothing about the updatecolumns.

Can anybody help?

thanx and greetz

C Dunnink
The Netherlands

View 3 Replies View Related

Find Records Where There Is Difference Between 2 Columns

Jan 24, 2014

I am trying to produce a report in Application express. I want to find records where there is a difference between 2 columns eg

Debt_amount - billed_amount

where debt >billed amount

I only want to see records where the debt is more than the amount billed

View 1 Replies View Related

How To Find Ntext Datatype Columns?

May 30, 2007

Dear experts,
how can i find the ntext datatype columns in a database?

please guide me

View 4 Replies View Related

Query To Find The Indexed Columns

May 30, 2007

Dear Experts, i need one query to find all the indexed columns with table names ,column names and type of indexes....

i'm trying with sysindexes, but i was unable to finish it....i'm not clear about keys column in sysindexes...

please guide me.

thank you very much

View 12 Replies View Related

Trying To Find A Match In Computed Columns

May 26, 2006

I need to create an function similar to the "MATCH" function in Excelthat evaluates a number within a set of numbers and returns whetherthere is a match. I have put the example of what I see in excel in thecheck column. The "0" answer in the result column is in the fourthaccount in the list. Somehow I need to loop through the accountscomparing the result to the total and indicate a match in the checkcolumn. It wouldn't even need to tell me the row number; it could be a0 or 1.account total result check123770266.84124.2112377026131.050 412377026164.38-33.33123770260131.051237702678.7152.3412377167-31.34221.891237716731.34159.211237716738.55152 51237716731.34159.211237716715238.5512377167490.91-300.36123771670190.55123771670190.5512377167-31.3443.341237716731.34-19.341237716738.55-26.551237716731.34-19.3412377167152-14012377167490.91-478.9112377167012123771670121237736347.058412377363131.05012377363-45.38176.4312377363-47.05178.11237736347.0484.0112377363-47.04178.091237736347.058412377363541.11-410.06123773630131.0512377363672.15-541.11237750737.64152.91

View 3 Replies View Related

How Do I Find IDENTITY Columns On Table Using T-SQL

Mar 23, 2006

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?

View 8 Replies View Related

Find The Greatest Of Three Columns Per Each Row And Display

Nov 15, 2006

Hi,

i have a problem where in i have to display the greatest marks scored by each student.

How can i do this?? Is there any built in TSQL function.

Rgds..,

Aazad

View 4 Replies View Related

Find All Columns That Have Identity Property

Mar 19, 2008



How do i Find all Columns that have Identity Property in a database. That is i will like to know all columns in a database that are identity columns.

I am using SQL Server 2005. Thanks

View 4 Replies View Related

Transact SQL :: Find Max Of 2 Columns In One Table?

Jul 23, 2015

I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision

Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative

Expected

Customer 1 had a max of refnum of 2 and 1st one on number

IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative

View 21 Replies View Related

How To Find Out Missing Columns Between Tables

Apr 21, 2015

We are trying to find out the difference between tables in CUSTOMER database and CUSTOMER_coded database. The goal is to find out if there are any columns missing in each table of CUSTOMER_coded database.

We need the list of tables in CUSTOMER_coded database that misses some column compare to its peer in CUSTOMER database (list of columns being missing also).

I googled, but I get only all the columns in tables of database.

I need missing columns of all the tables when we compare these 2 databases( CUSTOMER and CUSTOMER_coded  databases).

View 8 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







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