Integrity Between Table And View
Jul 23, 2005It is possible to drop the table without dropping the view referencing
it. How do I force integrity?
Madhivanan
ADVERTISEMENT
Trying To Enforce Ref Integrity On Hierarchy Table Relationship
Dec 28, 2007I've got a table that includes:CREATE TABLE [dbo].[Content] ( [Id] int IDENTITY(1, 1) NOT NULL, [ParentId] int NULL,
I'm wanting to make sure that a ParentId must be in the table as Id someplace else. When I try to do it by making it a foreign key get the error:
--------------- SQL ---------------
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [dbo].[Content] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO
---------- ERROR MESSAGE ----------
There are no primary or candidate keys in the referenced table 'dbo.Content' that match the referencing column list in the foreign key 'Content_fk3'.Could not create constraint. See previous errors.
Any ideas?
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [Content].[dbo] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO
Advice On Table Design Which Will Allow Me To Enforce Integrity
Jul 23, 2005Hi,I have two tables Table A and B, below with some dummy data...Table A (contains specific unique settings that can be requested)Id, SettingName1, weight2, lengthTable B (contains the setting values, here 3 values relate to weightand 1 to length)Id, Brand, SettingValue1, A, 1001, B, 2001, null, 3002, null, 5.3(There is also a list of Brands available in another table). No primarykeys / referential integrity has been setup yet.Basically depending upon the Brand requested a different setting valuewill be present. If a particular brand is not present (signified by anull in the Brand column in table B), then a default value will beused.Therefore if I request the weight and pass through a Brand of A, I willget 100If I request the weight but do not pass through a brand (i.e. null) Iwill get 300.My question is, what kind of integrity can I apply to avoid the userspecifying duplicate Ids and Brands in table B. I cannot apply acomposite key on these two fields as a null is present. Table B willprobably contain about 50 rows and probably 10 of them will be brandspecific. The reason its done like this is in the calling client code Iwant to call some function e.g.getsetting(weight) .... result = 300Or if it is brand specificgetsetting(weight,A) ..... result = 100Any advice on integrity or table restructuring would be greatlyappreciated. Its sql 2000 sp3.Thanksbrad
View 9 Replies View RelatedReferential Integrity - Linking Multiple Tables To Transaction Table
Mar 3, 2006I have transaction table where the rows entered into the transactioncan come a result of changes that take place if four different tables.So the situation is as follows:Transaction Table-TranId-Calc AmountTable 1 (the amount is inserted into the transaction table)- Tb1Id- Tb1AmtTable 2 (an amount is calculated based on the percentage and insertedinto the transaction table)-Tbl2Id-Tb2PercentageTable 3 (the amount is inserted into the transaction table)-Tbl3Id-Tbl3AmutTable 4 (an amount is calculated based on the percentage and insertedinto the transaction table. )-Tbl2Id-Tb2PercentageHow do I create referential integrity between the Transaction table andthe rest of the tables. When I make changes to the values in Table 1 -4, I need to be able to reflect this in the Transaction table.Thanks.
View 6 Replies View RelatedReferential Integrity Constraints W/o Referential Integrity In The Db?
Mar 16, 2007Using the new referential integrity constraints that will be made available, will it allow us to manually define the relationships between entities even if there is no true foreign key constraints setup in the database?
Lets say we deleted the FK_Orders_Customers in Northwind between orders and customers.
Or is this ability available now?
Thank for your time.
Large Table-Table Partition, View Or Other Method?
Aug 27, 2007Hi everyone,
I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?
Can you please give some suggestions? It will be very helpful if you can post some references or examples.
Thank you!
T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B
May 6, 2014I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
Table/view
Nov 16, 2006there is a table - TableA in my DB
which iam using in a stored procedure now the table is normalised and the same date is spread in 6 diff tables.
my admin is creating a View which is just like TableA.
my question is do i have to change anything in my stored procedure as TableA is a view and not a table anymore.
View From A Table
Aug 14, 2005HiComplete newbie to sql server. I have a Companies table from which I wouldlike to create a Clients view as follows;SELECT ID, Company, CompanyTypeFROM dbo.CompaniesWHERE (CompanyType = N'Client')Such that when I insert a record into this view it automatically setsCompany Type to Client. Just so I can treat the view as just another tablewithout having to worry about setting the correct company type. Is there away to do this either via views or some other way?ThanksRegards
View 1 Replies View RelatedUsing Table UDF In A View
May 4, 2007If anyone can help... I have a function that takes one parameter andbring back quite a lot of records. but with a conventional function thefield size has a limit of 8000 and i can not use field type 'text'. thelimit has been reached and the view does not bring back results. anoption i found was to use a table function. but with my limitedexperience with table udf's, I am failing to use it within a view. I ampassing a value from the view as a parameter to use in the function andit doesnt work.Please help.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedTurning A View Into A Table
Apr 15, 2007I am in a scenario where my tables are refreshed every morning by a batch update. I have built a few views off of one table. To increase speed I would like to take all the rows from one of the view s and insert them into their own table. I know this can be done with some T-SQL but I'm a noob to it and don't know how to specifically do it.Any detailed help would be greatly appreciated. -Nate
View 1 Replies View RelatedUsing A View As A Lookup Table
Jan 29, 2004Hi
I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.
In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:
1) Is it possible to perform a JOIN on a view in the way that I would need to here?
2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?
Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.
Cheers
Jon
How To Connect To A SQL View NOT Table
Jul 14, 2005Normally i can use Web Matrix to connect to a SQL table. e.g. Dim queryString As String = "SELECT [MyTable].* FROM [MyTable]"Is is possible to connect to a SQL View. The reason is because i have generated a view using UNIONS and JOINS and some columns are generated by concatenating columns together (ie.. has alias columns).I also need to use VB.NET to refer to these alias columns.
Table And View Will Not Drop
Jun 13, 2001We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Anyone know what could be the problem here?
Design View Of Table
Jul 25, 2001How do you get the design view of a table in sql server query analyser?
I know in oracle its desc table_name.
Thanks
What's Different From SELECT FROM A TABLE To A VIEW?
Oct 12, 2000Hi all,
What's different from SELECT FROM A TABLE to SELECT FROM A VIEW? Which is faster? Can anyone explain my result following?
Example for:
-- TABLE:
set statistics io on
select * from TABLE_NAME
-->Table 'TABLE_NAME'. Scan count 2, logical reads 106, physical reads 0, read-ahead reads 0
-- VIEW:
Create view VIEW_NAME as SELECT * FROM TABLE_NAME
set statistics io on
select * from VIEW_NAME
-->Table 'TABLE_NAME'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0.
Hide Table View
Oct 14, 1999Hi,
How can I hide all the system tables so that only user tables show in table view.
Thank you very much!
John
Best Way To View Table Properties
Apr 28, 2003Can anyone help me with displaying several table properites at once. I know I can use sp_help 'tablename' to get one at time. What is the best method to get several databases at a time?
View 5 Replies View RelatedView Table Problem
Mar 27, 2007Hello
I am trying to insert records to view table.
Msg 4405, Level 16, State 1, Line 6
View or function 'table1' is not updatable because the modification affects multiple base tables.
table1 is view table
My Synatx is
insert into table1
select table1.col1, table2.col2
from table2, table3
where table2_id = table3_id
what is wrong with these.
Thanks
SQL 2k Replication - View To Table
Sep 20, 2005We would like to copy sensitive prod data into a devleopment environment and use a view to alter some of the sensitive rows. (incl. ssn's)
We have views that will "scrub" the data and substitute ssn's with an alternate key for R.I.
Some would like us to use replication to copy from our scrubbed views to our dev tables. Anyone have suggestions on how to accomplish this?
Only ops has access to distributors on-site, including my laptop :( so I can't do much experimenting and have to back-seat drive.
Trying To Sync A View Instead Table (ppc)
Jan 5, 2004I am working on a program for the Pocket PC platform, which will piggy-back on a helpdesk program which stores it data in an SQL server. I have no problems with getting information from the server, or any connectivity issues.
I have found that it is easy to synchronize an access .mdb file, or an ODBC Source to a .cdb file on the PPC. I have a data provider for the SQL database in question, and the "Import Database" function works fine; it pulls up a listing of all the tables and fields in the helpdesk database.
My problem is as follows. The table used to store the memo information is at least 20-30 megs. I only need a few key entries from that table. I also need to make the sync process as simple as possible (that's why i was looking into activesync), but there does not appear to be ANY way to import an SQL View, as opposed to a table.
SQL CE appears to be one possible solution, but we are running SQL 7, and the CE server tools require SQL 2000.
So, is there a way to trick activesync into sync'ing a VIEW instead of a table?
Do you have ANY OTHER ideas, which would be simple come time to sync?
Index A View Or A Table
Apr 8, 2004I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)
The table is also involved in frequent non-simple select statements. It currently has about a million rows.
Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.
Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.
Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?
Thanks
View On The Fly Or Temp Table
May 6, 2008problem:
Have a table with Data and Dependencies(Foreign Keys) and Stored Procedures, views etc.
Need the Data in that table to put in different order.
For exampl, put older years in the begining or end so when sorted by year, you will get right data.
Question is: If i use view it will be based on the table with Wrong entry order and if i use Temporary Table
each time stored procedure is run, it will be created and overhead.
Example of the table is below with Wrong order.
I should have entered the old years first.
Note this is example and not the actual table!
ID Yr Title/Model Serial#
---------------------------------
1 2005 Toyota Camery IXp12365555
2 2006 Honda Accord XJi9770009
3 2007 Honda Accord XJi9000009
4 2004 Honda Accord XJi9880009
5 2005 Honda Accord XJi9009009
6 2007 Honda Accord XJi9078009
If this example is not right, my bottom line is this:
I have entered the Data in a table and i'm getting wrong resutls when i order by Desc or Asc and now either have to delete all rows and re-enter it or have someother clever way!
Modifying A Table From A View
Jun 18, 2008I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table.
I also have an inner join view with the initials from both tables as the joining field.
I want the users to be able to modify the data table (MainTable) but also display the users name field.
here is the sql of the view:
SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName,
dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify,
dbo.MainTable.AdvocateInitials
FROM dbo.InitialsListing INNER JOIN
dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials
Using Temporary Table In A View
Jul 23, 2013I am trying to create a view using to three queries below and I get the error message Views or functions are not allowed on temporary tables. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?
--Query 1
SELECT * INTO #MOVEMENTS
FROM [GW_DW].[dbo].[DimStatusHistory] d
WHERE TransferFromToProgram<>''
AND d.Status=12
;
--Query 2
SELECT DISTINCT
[Code] ....
Create View From Table
Oct 6, 2014I want to create a view from table j25_teamlist2014 with the 7 columns shown below. But I only want the rows which don't have a Status of 'Not Available'.This sql works fine apart from the Team Name and Team Captain columns display the team which is an interger ie Row 1 shows 1 in the Team Name and Team Captain columns instead of 'My Team' and 'Jo Bloggs'.Also when viewing the original table and the new view the structures are slightly different.
CREATE VIEW j25_availableteamlist2014 AS
SELECT Team, Day, Time, Lane, Team Name, Team Captain, Status
FROM j25_teamlist2014
WHERE Status NOT LIKE 'N%'
SQL View/Table Question
Jul 23, 2005MVP's and the likeI am looking for suggestions , confirmationLet me start by saying bar none, performance is paramount with thequeries to be retured off this view/table query.To that end I am completley donormailzing the data into a view (or atable) with all possible legal combinations ( I have seen this refferedto as Croation Method ?) Its ends up at 3 total rows per account onaverage.THis query is to be executed only from the web, so once again theperformance is paramount.Ok here is what I have so far I have a set of related tables 5 or sothat are for the most part 1 to 1 but in the case of one particulartable its an average raqtion of 20 to 1.Broken all out with 150k customer rows it ends up at 500k total rows inthe viewAbout 7 cols are dynamic aggregates (Sum(tran_AMT)) , min(tran_amt) ,etcI had it in a view and with 1/5 million rows (In the view) it performedadequatley, I will most likeley get to somewhere around 10 milliontotal rows (In the view)I looked into indexing it but thats not going to happen because I amdoing several subquery's , outer joins etc.The View with a Select * returns in 40 SecondsNow i ran into an issue with recursing this query into several seperatesubqueries and hit the 256 table limit, after only about 15 recursions.Now the data only gets updated once , perhaps twice a day, in a batchtransaction.So I Put all the data from the view into a table, and indexed that, Ican return all rows in 12 seconds and any refining conditions I throwat it, like Date > 12/1/2003 (then it executes in under 6 second_ onlyslices the query time down big time (A very good thing)Since its only updates 2x a day I drop and recreate the table in my DTS, and I have triggers on the other tables for Online updates (usuallyno more that 100 a day) but to ensure the table is ALWAYS current itgets recreated on import of additional rows.Is there a name to this maddness ? My co-workers aree leary of it butthey come from a dbase background, denomailzing it into a table andthen indexing the table I couldnt be happeier with ther performance.Are there any lurking gremlins in this design ?I cannot see any pitfalls with doing this , or are there some ?,basically I am denomailizing for read-only query performance.They are concerned about the denorilization for performance, eventhough its only on a read only table (was and could be a view I justcant index this particualr view)So I guess Im looking for a , "Sounds OK" or "Sounds Great" from theSQL Gods ....Chris
View 2 Replies View RelatedTable And View Permissions
Aug 2, 2007I have a list of users that I want to restrict access to tables in a database. The goal is to allow the users to use select statements on the views instead of the tables. How can this be accomplished?
View 14 Replies View RelatedHow To Create Table From A View
Nov 7, 2006Dear all,
Can I create a table from a view like Oracle? The syntax is something like as the below.
create table table1 as select * from view1;
Thanks
Restoring Table Or View Or Sp
Sep 22, 2006Actually I have very big size of databse. Also its back up is obvious that also biger size.
In case of only data loss of one table or view or sp or change then restore whole database then get back previous backup database & restore table i.e. it consuming too much time.
I want to restore only one table or view in faster way. how i can do it. plz give any suggestions
Permission On View Not On Table
Dec 14, 2007I am trying to create a set of "Reporting" views and grant Select permission on those views instead of the tables. I created the view in the same database but under a different schema from the tables. When the user tries to select from the views they get an error saying they do not have select privilege on the tables. Do I need to grant them select on the underlying tables?
View 4 Replies View RelatedMirror A View To A Table
Aug 8, 2007I would like to replicate a single view to a table that is stored on another db server (connected as linked server object).
Is there a way to imitate the behavior of a trigger (insert, delete, update) for a view?
I could assign the triggers to the table that provides the primary key.
So I could handle insert, delete events.
But what about updates that affect row in other tables that are used in this view?
Code Snippet
CREATE TRIGGER mirror_tableA_insert
ON [TESTDB].[dbo].[tableA]
FOR INSERT
AS
BEGIN
set nocount on
SET XACT_ABORT ON
set REMOTE_PROC_TRANSACTIONS off
INSERT INTO OPENQUERY(TESTLINKED, 'SELECT * FROM tableA')
SELECT *
FROM [TESTDB].[dbo].[myView] orig
INNER JOIN inserted i
ON i.prim = orig.prim
END
Thanks in advance for any hints!
Marcus
Creating Table From A View!!!
Jan 15, 2008
Hi folks!!
I have been working on a view and now I want to create a table which has not only similar columns and datatypes but also same data as I have fetched in the view.
How can I do that? Do I have to use a cursor or there is a simpler way to do it?
Please advice me here..
Can I use
Create table <tablename> as ( Select * from <viewname>) ?????
Thanks in advance for reading this.
Tanya