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 table
I need:
The name of the column in the target table
The name of the column in the referenced table
The name of the referenced table
Any help would be great, thanks
how to find all primary key columns & foreign key columns in all database tables?
View 1 Replies View Relatedwhat 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?
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID . . . . . . . . . . int
Name . . . . . . . . . . . nvarchar(50)
ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?
Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!
View 1 Replies View RelatedI'm looking for a query that will return all index names, the table the index is on and the columns in the index...
View 1 Replies View RelatedI 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 RelatedHello.
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.
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
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
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?
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.
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 ?
Hi guys, i have a little question, i hope you can back me up please.
I have this tables:
Code:
TV
IdTV - autoincremental int, Primary key
IdCamera - int, foreign key
DeviceType int
Code:
DayLightCamera
IdDayLightCamera - autoincremental int, Primary key
DayLightCameraName - varchar(30)
Code:
InfraredCamera
IdInfraredCamera - autoincremental int, Primary key
InfraredCameraName - varchar(30)
now, when in insert a row in "TV" the foreign key "IdCamera" will relate to a row in either "InfraredCamera" or in "DaylightCamera" depending on the "DeviceType" value.
in other words, if i insert a row with DeviceType=0, then IdCamera will have to point to a row in the "InfraredCamera" table. And if i insert a row with DeviceType=1, then IdCamera will have to point to a row in the "DaylightCamera" table.
so, my question is, how can i make the constraints relationship so that the idCamera relates to a row in DaylightCamera or in InfraredCamera depending on the value of DeviceType? should i make 2 foreign keys with allow null? or should i place both relationships to the same foreign key? im not sure what to do
Thanks guys for your help. it is really appreciated!
Starting with an example will make explaining this much easier.
I have two (or more) tables defined as follows.
Code SnippetTable1 Table2
Id [uniqueidentifier] Id [uniqueidentifier]
[...] [...]
Now, I would like to create a table Table3 that has its own primary key and references Table1 OR Table2.
Code Snippet
Table2
Id [uniqueidentifier]
ForeignId [uniqueidentifier, references Table1 or Table2]
This would enable me to insert any value into ForeignId that is present as the Id field in Table1 or Table2. Is this possible?
Best regards,
Till
Hi,
I don't know if this is possible, i believe not, so I'm here to ask the experts if is possible to have a foreign key constraint that references the key of one of two tables.
Like this:
I have 3 tables: TABLE X, TABLE A and TABLE B
Is it possible to the FK on TABLE X refernce the PK of TABLE A OR TABLE B?
If yes, how can I do this?
If not, I need to have a fourth table, so TABLE X references TABLE A and TABLE Y references TABLE B.
Thanks!
I have a setup with a bridge table. There are about 5 different tables
on one side of the bridge (all with compatable PK columns) one of which
is called 'mobilesub', and one on the other side called
'allcostcenters'. The bridge table is called 'subaccountcostcenter'.
I can enter data for mobilesub in the bridge table. But then when I try
to enter the info into the bridge table for any of the other tables,
such as localsub, there is a conflict like this:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_subaccountcostcenter_mobilesub'. The conflict occurred in database
'test1', table 'mobilesub'.
The statement has been terminated.
Is there some rule against using a bridge table that references several
different tables, and I'm just not aware of it. Because I've done
everything I can to make sure the info from the different tables don't
conflict . . .
The same error comes up if I do the localsub table first--in that case
the foriegn key messing me up is FK_subaccountcostcenter_localsub. So
it's not something with the individual tables.
I need experienced advice lol
Thanks
I know altering the schema of system tables is a big no-no, but I was wondering if setting up a table that has foreign keys pointing to a system table is bad.
Basically what I'm refering to is in some cases I have CreationDate and CreatedBy fields in my tables that correspond to GETDATE() and USER_NAME() functions in insert statements....I want the CreatedBy field to be a valid SQL server DB username ... and not some unchecked string value (SYSNAME actually)
Greetings all!
How can I enforce a foreign key constraint when I have two tables each one residing on a different database?
Thanks for your help in advance.
I was trying a joining example provided in my book in which customer is a table and person is another table. The query provided in the book is this... USE AdventureWorks2012;
GO
SELECT c.CustomerID, c.PersonID, p.BusinessEntityID, p.LastName
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID;
This is the query that I did....
SELECT
     c.CustomerID,p.FirstName,p.MiddleName,p.LastName
FROM
     Sales.Customer AS c INNER JOIN Person.Person AS p
ON
     c.CustomerID=p.BusinessEntityID
ORDER BY
        p.BusinessEntityID;
Keys :-
Person Table
[PK_Person_BusinessEntityID]
[FK_Person_BusinessEntity_BusinessEntityID]
Customer Table
[PK_Customer_CustomerID]
[FK_Customer_Person_PersonID]
However,both of them gives a very different result set.But my question is why do we need to use the Customer.PersonID instead of Customer.CustomerID. Is it really important to use one primary key and one foreign,is there any specific reason why the book showed c.personId=p.BusinessEntityId.??
Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
i have two tables Table one have 2 columns id and value
    Â
id value
1 Dell
2 Hp
B2 Hp-mini
B3 Hp-lapTop
3 Acer
the second table have 3 clomuns id,name,idTable
1 TeaBou B
2 Mark B
3 Jack 1
4 Piere 2
5 Jean 2
6 Mark 3
i tried this query
select*from table1 t1 ,table2 t2
where t1.id=t2.idTable
i had some data but also i need to include the person who have the B product.
I have a situation that I must resolve. I have a program being used by many but I had to create a new table to provide a new feature. The problem I have is this table must use the primary key from the parent table as its primary key, meaning when a user adds a new record to parent table, I need to instantly add the primary key to the child table. Now this was done in the program using sql statements, but I need to implement a trigger or such as to keep me from having to reinstall application on many computers.
basically person inserts new record, then I need to get the new primary ket and add insert it into the child tables. how can I do this with a trigger. I have tried to use an insert into statment with my trigger, but I can't seem to pass the parameters correctly.
CREATE Trigger dbo.Table_Borrower_Insert_Keys
ON Table_Borrower
AFTER INSERT
AS
begin
declare @bid as int
@bid = select MAX(BorrowerID)
FROM Table_SoldProgression
INSERT Table_SoldProgression(BorrowerID)
values (@bid)
end
GO
another attempt
CREATE Trigger dbo.Table_Borrower_Insert_Keys
ON Table_Borrower
AFTER INSERT
AS
INSERT Table_SoldProgression(BorrowerID)
values (select MAX(BorrowerID)FROM Table_Borrower)
GO
Guys,
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table
My truncate script should be
TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE
IS there any automated way to figure out parent and child tables and generate truncate script for the same.
Thanks
Hi all,
In my project i will have the data in a collection of objects, I need to update series of tables with foreign key relations
Right now my code looks like this
foreach(object obj in Objects){
int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table
int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId
///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table
}
The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
Thanks
Regards
Prasad.
I was wondering how I do to insert values in two tables that are related each other by a FK?
That is the procedure that illustrate what I meant to be.
ALTER Procedure [dbo].[new_user]
@master nchar(10),
@nick nchar(10),
@fish nchar(10),
@e_mail nchar(30)
As
Begin
INSERT INTO users
(nick, fish, e_mail)
VALUES (@nick,@fish,@e_mail)
INSERT INTO friends
(user_id, e_mail)
VALUES ( Select user_id from users where nick=@master,@e_mail)
End
Thank you very much.
Is there any possibility to create a foreign key references more than one tables.
say for example,
Create table sample1(id int primary key)
Create table sample2(id int primary key)
Create table sample3(
id int primary key,
CONSTRAINT fk1 FOREIGN KEY REFERENCES sample1 (ID),CONSTRAINT fk1 FOREIGN KEY REFERENCES sample2 (ID))
this shows no error while creating, but in the data insertion it shows error..
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies View RelatedGuys,
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table
My truncate script should be
TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE
Is there any automated way to figure out parent and child tables and generate truncate script for the same.
Thanks
I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture. The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. without taking the xml down to the tables?The problem with this approach is I have xml stored in most tables and the tables are becoming very large.
<Route Type="OneWay" >
<Seat Type="FirstClass">
<Prices>
<Price Price="10" />
<Price Price="11" />
</Prices>
[code]....
Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?
For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.
I want to create a table with primary key , and put relationship with second table.
View 5 Replies View RelatedHi,
I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :
table MOTHER (MOTHER_ID, MOTHER_NAME)
table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)
After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.
My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".
The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...
Is there a solution to chain two insertions with a foreign key constraint in a data flow?
Thanks
Regards,
Arnaud Gervais.