This book 'MS SQL Server 2000 Bible' says the following:
'The foreign key can reference primary keys, unique constraints, or unique indexes of any table'
I've never heard of this before, I thought that the FK always established referential integrity by referencing the PK in the parent table. Does anyone have a further explanation of this?
Hi, i want to make a reference from a table on itself. The table has a composite Primary Key. But I just want to refernce the TEstCaseID. So whats wrong? Can anyone help me?
I have a UniqueIdentifier as a self referencing foreign key. The pk gets set by default using the newid(). I need the foreign key to default to that same value. how can i do that? @@identity doesnt work, $rowguid doesnt work, column name doesnt work. Any ideas?
It says : The number of columns in the referencing column list for foreign key 'staff_fk' does not match those of the primary key in the referenced table 'Staff'.
I ran into an interesting situation. I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem. I found FK's that are referencing columns that do not have a unique constraint or a unique index.
I don't know the history of the database but was there a time in SQL Server history where this would have been possible? I scripted out the tables and created it in a test database. When I run the script to create the FK I get the following message.
I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2. Currently the database is running on SQL Server 2008 Ent.
ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1]) REFERENCES [dbo].[table2] ([col2])
There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.
Hello everyone, this is my first time to these boards. I've been running all around for the last few days trying to solve a problem. So far, on 3 forums, we haven't been able to find a solution.
I am using MS SQL 2005 Workgroup. I have catalog of events on my site. Each event can have a little as 0 up to an unlimited amount of Photos attached to them(in a seperate table.) Here's the basic breakdown.
catalog ----------- id - PK act_name (price and other such info)
Photos ----------- id - PK path_to_photo event_id - FK(catalog.id)
What I'm trying to do is select all the events in the catalog, and order them by the number of photos they have in the photos table. The idea is to get the events that have photos at the top of the list.
here is the query that I'm using right now.
Code:
SELECTCOUNT(photos.id) AS PhotoCount, catalog.id AS item_id, catalog.company, catalog.act_name, catalog.location, catalog.price_adult, catalog.price_child, catalog.short_description, catalog.photo, catalog.children_allowed, catalog.long_description, catalog.online, catalog.act_type, event_types.act_type AS evt_type, event_types.id AS event_id, catalog.rank, catalog.length, catalog.bullets, photos.id FROM catalog, event_types, photos WHERE catalog.act_type = event_types.id AND photos.event_id = catalog.id GROUP BY COUNT(photos.id), catalog.id, catalog.company, catalog.act_name ORDER BY photos.id, catalog.id, catalog.company, catalog.act_name
Which returns the following error. Column 'catalog.location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
No matter what I do, I always seem to get an error. I've removed the Count in the select list, and that tells me that I cannot call Aggregate functions in the Group By list.
I'm trying to create a "Self-Referential, Many-to-Many" relationship under the Database Diagrams section in Sql Server 2005 express and I'm having a heck of a time figuring out where to click and edit to create what I want.
The url below links to the pictorial represenation of what i'm trying to create. Any help is greatly appreciated. Thanks! http://www.communitymx.com/content/source/A1A63/diag.gif
Hi, I have a self-referencing table, something like Emlplyee-Manager. I want to implement the cascading delete in this table. So when I delete a manager than all the employees should be deleted at ANY level below the manager. I do not have DRI for the Foreignkey (manager)!!! My problem is that the trigger fires only one time (for the "sons") and not for the "grandsons" and below. I've used the following flags: - nested triggers (in "SQL Server Properies", "Server settings" tabsheet) - recursive triggers (database "Properties", "Options" tabsheet) I've combined this 2 flags: 1. nested flag reset and recursive flag reset 2. nested flag reset and recursive flag set or 3. nested flag set and recursive flag reset 4. nested flag set and recursive flag set The results are the following: -for case 1, 2 and 3 the same results: the trigger fires just for the first level (the sons are deleted but the grandsons remains there orphan) -for case 4 I have the following error message: "Maximum stored procedure nesting level exceeded (limit 32)" so I can't delete anything at all.
Any idea why the trigger doesn't fire for "grandsons" and below? Could be that this above mentioned flags doesn't works for self-referencing tables ?
I am totally confused, please help me. I am new to this
I am trying to split one DB (A) into two databases original (A) and new-Blank(B) by moving some tables from DB (A) to the new DB (B) however some of the tables has FK and Stored procedures referencing other tables that need to stay in DB (B), The questions are
1. after scritping these tables while they are in DB (A), and runing the script in DB (B) to re-create them, do I delete these table from DB (A), and the FK that references them.
2. What shall I do with the stored procedures. Turn them into trigers or else if turn them into trigers, in which DB should the triggers run (DB A of DB B),if these are becoming triggers do I delete the stored procedures from DB (A)
Searching the KB i only found links to SP1 and SP3, the server is already at SP3.
BOL says the following:
SQL Server 6.x :
Updatable views were restricted to modifications that affected only one table
SQL Server 2000:
Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.
So why can this view not be updated in SS2000 SP3 ?
FROM PS_PER_ORG_ASGN A ,PS_PER_ORG_INST C , PS_JOB D
WHERE A.EMPLID = C.EMPLID AND A.ORG_INSTANCE_ERN = C.ORG_INSTANCE_ERN AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= { FN CURDATE() }) OR (JOB2.EFFDT > { FN CURDATE() } AND { FN CURDATE() } < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = D.EMPLID AND J2.EMPL_RCD = D.EMPL_RCD) ) )) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT )
Hi everybody, I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?
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?
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005
SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2
This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.
The only way how to avoid the issue in the SQLExpress seems probably to be
- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)
- Utilize computed columns if possible (no idea on performance impact here)
Am I right or is there any other way how to reference a calculated field?
Any suggestion is greatly welcomed! Thanks in advance.
I need a statement that can return a hierarchy of folders. There are a number of good articles out there describing how to do this in SQL 2005 with a Common Table Expression (CTE) that references itself under a UNION ALL. Yet every example I come across involves a self-referencing table. Unfortunately the architect of my project did not use that simple structure. Instead, we have a Folder table and Folder Properties table. Creating a child Folder involves adding a row in the Properties table that links to a parent. Here's the statement I came up with:
WITH Folders (ID, ParentID, FolderName, FolderDepth)AS ( SELECT FOLDER_ID, NULL, FOLDER_NAME, 0 FROM ZZ_FOLDER WHERE FOLDER_ID = 1 UNION ALL SELECT F.FOLDER_ID, Folders.ID, F.FOLDER_NAME, Folders.FolderDepth + 1 FROM ZZ_FOLDER F INNER JOIN ZZ_FOLDER_PROP FP ON FP.FPROP_FOLDER_ID = F.FOLDER_ID WHERE FP.FPROP_LINK_ID = Folders.ID )SELECT * FROM Folders
The initial statement brings back the top of the folder structure. The recursive statement brings back Folder rows that have an entry in their Property rows for the parent. Yet when I try to execute this, I get:
Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.ID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.ID" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "Folders.FolderDepth" could not be bound.
It sounds like all the recursive references to the CTE are unavailable. What am I doing wrong? Would welcome any suggestions, too. I realize this isn't a T-SQL specific question, more SQL in general, but I didn't see a more relevant forum. What follows are some tables/data to run the above statement against.
-- Create Folder TablesCREATE TABLE ZZ_FOLDER ( FOLDER_ID INTEGER NOT NULL PRIMARY KEY, FOLDER_NAME VARCHAR(50))CREATE TABLE ZZ_FOLDER_PROP ( FPROP_ID INTEGER NOT NULL PRIMARY KEY, FPROP_FOLDER_ID INTEGER NOT NULL, FPROP_LINK_TYPE VARCHAR(50), FPROP_LINK_ID INTEGER NOT NULL)GO-- Populate Folder TablesINSERT INTO ZZ_FOLDER VALUES (1, 'Top Level')INSERT INTO ZZ_FOLDER VALUES (2, 'Second Level A')INSERT INTO ZZ_FOLDER VALUES (3, 'Second Level B')INSERT INTO ZZ_FOLDER VALUES (4, 'Second Level C')INSERT INTO ZZ_FOLDER VALUES (5, 'Third Level A1')INSERT INTO ZZ_FOLDER VALUES (6, 'Third Level A2')INSERT INTO ZZ_FOLDER VALUES (7, 'Third Level A3')INSERT INTO ZZ_FOLDER VALUES (8, 'Third Level B1')INSERT INTO ZZ_FOLDER VALUES (9, 'Third Level C1')INSERT INTO ZZ_FOLDER VALUES (10, 'Third Level C2')INSERT INTO ZZ_FOLDER VALUES (11, 'Fourth Level A3A')INSERT INTO ZZ_FOLDER_PROP VALUES (1, 2, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (2, 3, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (3, 4, 'Folder', 1)INSERT INTO ZZ_FOLDER_PROP VALUES (4, 5, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (5, 6, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (6, 7, 'Folder', 2)INSERT INTO ZZ_FOLDER_PROP VALUES (7, 8, 'Folder', 3)INSERT INTO ZZ_FOLDER_PROP VALUES (8, 9, 'Folder', 4)INSERT INTO ZZ_FOLDER_PROP VALUES (9, 10, 'Folder', 4)INSERT INTO ZZ_FOLDER_PROP VALUES (10, 11, 'Folder', 7)GO/*Top Level Second Level A Third Level A1 Third Level A2 Third Level A3 Fourth Level A3A Second Level B Third Level B1 Second Level C Third Level C1 Third Level C2*/
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.
declare @THeader TABLE ( TransactionHeaderID [int] NOT NULL , PatientID [int] NOT NULL , TransactionAllocationAmount [money] NOT NULL , ApplyAmt [money] NULL ) - create table variable
insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @THeader set TransactionAllocationAmount = (SELECT isnull(Sum(mtblTransactionAllocation.Amount),0) FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable
Update @THeader set ApplyAmt = (SELECT mtblTransactionAllocation.Amount FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt
UPDATE mtblTransactionHeader SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount, mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt FROM @THeader, mtblTransactionHeader WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
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 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 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)
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
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.
I have a SqlDataSource - The select statement selects the DB row with an ID that equals a querystring value.... So I know I am only selecting one row..Now I want to create a little Sub that grabs any field(s) of my choice and then I can assign the value of it to a textbox on my page or a variable if I need to... Any idea how I actually reference the fields via the SQLDataSource in a sub? I see you can reference parameters using..SqlDataSource1.SelectParameters()I was hoping I could use something similar to get the actual DB fields like the below...SqlDataSource1.Select("MyDataBaseField").Value = TextBox.TextCan anyone help please??Thanks
The Background: From page 1 a search is created and this value is converted to a querystring and given the name "id=" and passed to page 2. Page 2 then does a lookup in the sql 2000 db useing this querystring. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ListingDBConnectionString %>" SelectCommand="SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE ([ID] = @ID)"> <SelectParameters> <asp:QueryStringParameter Name="ID" QueryStringField="id" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
The Problem: What I then need to do is an IF THEN ELSE statement. IF [Package in SQL1] = 4 Then Package 4 IF [Package in SQL1] = 3 Then Package 3 Only how do I reference the sql value in the if statement. I have tried <%IF Eval("Package") = 4 Then %> and with Bind - No good
Hi all, How do I return pictures along with my search results? For example, I have a shoe database and for the results I would like one column to hold a picture of the shoe (the other columns hold Brand, Name, Price, etc) The way I am looking at implementing this is by referencing the image and retrieving it from the server. I'm a bit lost on how to do this though. Any help? Thanks in advance,Nick
CREATE TABLE Folder ( iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY, folderName varchar(50) NOT NULL, parentFolderID int NULL FOREIGN KEY REFERENCES Folder (iD) ) GO
if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.
The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'
I'm setting up a trigger that will fire off an email explaining that this entry has been updated, however I need a reference to the updated row so I only send that bit of information off.
Heres my code thus far:
ALTER TRIGGER [dbo].[completeCreation2]
ON [Database_Test].[dbo].[Projects]
AFTER UPDATE
AS
-- SEND EMAIL
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administration',
@recipients = 'email@email.com',
@body = 'A new project has been created view details of the project below:',
@subject = 'Project Created',
@body_format = 'TEXT',
@importance = 'High',
@query = 'Select * FROM updated';
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
END
I thought the select From updated would work but it doesn't I need a reference for that line, select * FROM _____.
I am trying to run a mobile device with SQL's mobile 3.5. When I run my application I am given an error that sqlceme35.dll plus several other references cannot be found
When I try referencing them It does not allow me too. Is there any steps I should take to make referencing allowed?
