I have two tables that are reference to each other by foreign key, now I would like to alter the table so the it doesn't reference each other any more. What is the syntax to alter a table so that the fk field doesn't reference a table anymore. Thanks in advance for the help.
View 1 RepliesI have facing a design problem and unable to justify which design to choose for my data model.
Usually, what we have is like data tables and reference tables to store data in those data tables.
My database has tables with 20-30 columns in them. And most of them (though not all of them), stores data from some reference tables. Meaning each column has an associated reference table where it stores possible list of values for that particular column. Sort of data domain for that column. FYI, My database is related to medical field.
For example, A table that has a varchar(40) column called "Differentiation". It can only store values from following list:
- Undifferentiated
- Moderate
- Poor
- Poor - Moderate
- Moderate - well
Now, to implement this, simple solution would be to have a reference table where I can store all these possible values...And then have just a reference of each data item into my "Differentiation" column in the table.
This is simplest and probably the best solution for such thing and i can also have referential integrity implemented for this.
But now if we look at the bigger picture, my database is growing and I have about 80 tables which I need to create where most of the columns will have different reference tables like I mentioned above. Approximate number of reference tables is 300 tables. All the reference table will have same structure, with different values for different columns.
Now, what seems to me is, because the table structure is same for every column, rather than having 300 different tables, I can only have 2 tables, where I can put all these reference values into these 2 tables.
Like,
Table 1 :
This table can have name of the reference table like "differentiationlist" etc.
Table 2:
It has reference to the reference table list in Table 1 discussed above and all the values that are part of that reference table can go in this table with its reference.
But problem with this is, because all the reference tables are in these two tables, I don't know how to implement referential integrity in this design.
Does anyone have any idea or solution for situation like this?
Thanks,
Ujjaval
Hi ,
I have the following structure:
Item table
----------
ItemId
ItemName
Item Transaction Table
----------------------
TransactionId
GiverItemId
SenderItemId
the data is somewhat like this:
Item table
__________
1abc
2xyz
3pnr
4rew
5dds
6djs
7dsf
Item Transaction table
---------------------
112
224
343
437
Now i have a reauirement to build a stored proc in which all the transactions starting from one transaction like, if i want to know the chain for item no 2 it shall give the following result:
24
43
37
if i want to know the chain for item no 3 then it shall give following
37
if i want to know the chain for item no 1 then it shall give following
12
24
43
37
Please help.. Its urgent.....
Thanks
Ok so I am trying to update rows which have a rNumber in the range of a specific lookup table fields. Example.
Table A (source table)
----------
cn, rNumber
null, 223
null, 9999
null, 12345
Table B (Reference table)
-------------
cn, start_r, end_r
A, 1, 300
B, 9000, 10000
C, 11000, 13000
I want to find the 'cn' for tableA.rNumber where it exists in the range of tableB.start_r and tableB.end_r
how do I do it??
-Chris Varick
I'm trying to create two tables, Admins and SecurityGroups, I want to have the ability to assign a security group to an admin so that I don't have to specify all the rights individually to every admin. So, I have the securityGroupID in the Admins table and I reference the SecurityGroups table for data integrity. On the other hand, for audit purposes, I also want to record which admin created the security group so I have the AdminID in the SecurityGroups table and I reference the Admins table for data integrity. Of course when I try to create any of those tables I get an error message that the other table doesn't exist. I wonder if this can be done without having to remove the references constraint from one of the tables and then using alter table. Here's an example below to make it clearer:
CREATE TABLE Admins
(
AdminID INT NOT NULL PRIMARY KEY,
SecurityGroup NULL REFERENCES SecurityGroups (SecurityGroupID)
)
CREATE TABLE SecurityGroups
(
SecurityGroupID INT NOT NULL PRIMARY KEY,
CreatedBy INT NOT NULL REFERENCES Admins (AdminID)
)
Any suggestions are highly appreciated...
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!
Not sure if the title describes my situation or not.
Simplified example is:
I have an [Employee] table with EmpCode, EmpName
I have a second table [NewHires] that has: HireDate, EmpCode, Addedby
Both EmpCode and Addedby contain EmpCode referring to the Employee table.
I wish an output similar to:
New Employee (from EmpCode in NewHire), Hired on (From HireDate), Hired By (from Addedby)
My problem is with an Employee.EmpCode=NewHires.Empcode or Employee.EmpCode=NewHires.Addedby in the Where clause or Join part of the SQL I don't know how to get EmpName from the Employee table twice but using two different EmpCode as the reference.
Thanks in advance - Shawn
I have 2 tables, OBJECTS and LINKS both have common field(OBJECT_ID).
I need to update certain records in table OBJECTS only if they meet certain criteria in table LINKS.
How do I go about doing this..???
Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price
Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.
Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
Hi y'all
Does anybody know reference or pdf file or free e-learning on the web?
DECLARE Loan_cursor CURSOR FOR
SELECT Loan_No,store
FROM loan
WHERE maturity_date > '2001-04-30' and loan_no like 'ABL%'
OPEN Loan_cursor
-- Perform the first fetch.
FETCH NEXT FROM Loan_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @LoanNo varchar(12)
** Set @LoanNo = Loan_No
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Loan_cursor
END
CLOSE Loan_cursor
DEALLOCATE Loan_cursor
when l run the cursor l get the error
Server: Msg 207, Level 16, State 3, Line 15
Invalid column name 'Loan_No'.
.
If l reference it as Set @LoanNo = LoanTable.Loan_No l get the error
Server: Msg 107, Level 16, State 3, Line 15
The column prefix 'loan' does not match with a table name or alias name used in the query.
All l'm trying to do is to compare the loan number that l get from the cursor to the value in the loans table. Maybe reference is a better word
Hi friends,
Just i modified one sub stored procedures which is not getting affected in the main stored procedures
Create procedure main
as
begin
set nocount on
--First sp
exec Data_transfer_sp
--Second sp
Exec Clearance_sp
set nocount off
end
In data_transfer_sp,i have commented the select statement
but still iam use to the select result while execution the main sp.
Note:I have compiled the data_transfer_sp after making comment
.Txs in advance
Hi All ...
WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)
SELECT x,y,sum(z) from myCTE
SELECT y,z,sum(x) from myCTE
the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?
never mind the semantics of what I am SELECTing, I just want to be able to reference the CTE more than once in my SP
am I trying to use the CTE in a way that was not intended?
Hi All ...
WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)
SELECT x,y,sum(z) from myCTE
SELECT y,z,sum(x) from myCTE
the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?
never mind the semantics of what I am SELECTing, I just want to be able to reference the CTW more than once in my SP
am I trying to use the CTE in a way that was not intended?
hi ,
can any body help me that how can i use my .net dll into my rdl file.
thanks
Gorla
in this code ,whtat is the references of
RelationalDataSourceView
and also
what are the references of these?
OleDbConnection
OleDbCommand
OleDbDataAdapter
Can anyone see why I would get the 'Object Referenece not set to an instance of an object error in the following code?
It happens on this line:
MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection) 'Populate the text boxes from database for alumni fields.
Dim MyAdapter As SqlDataAdapter
Dim MyCommandBuilder As SqlCommandBuilder
Dim DetailsDS As DataSet
Dim Row As DataRow
Dim SelectStatement As String = "Select ClientID,ClassYear,HouseName,CampusName,EducationMajor,GraduationDate FROM tblclient Where [ClientID]=" & _
ClientIDSent
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection)
MyCommandBuilder = New SqlCommandBuilder(MyAdapter)
MyAdapter.Fill(DetailsDS, "tblClient")
Row = DetailsDS.Tables("tblClient").Rows(0)
ClassYearText.Text = Row.Item("Classyear").ToString()
HouseNameText.Text = Row.Item("HouseName").ToString()
CampusNameText.Text = Row.Item("CampusName").ToString()
EducationMajorText.Text = Row.Item("EducationMajor").ToString()
GraduationDateText.Text = Row.Item("GraduationDate").ToString()
Hi,
I'm looking for a good reference guide online as I am more used to mysql (and stil quite limited vocab at that)
I create table outside of a database by accident and I'm now looking for the sql syntaxt for moving tables, but I can't find it anywhere?
most simple guides don't seem to provide the syntax to do this. :(
does any one know any good pages to get some sql commands
and explainations thanks
Hi,
I am access SQL 7 via ADO in some ASP pages. My database objects were created under a user name aliased to the dbo. As a result, when I have my client logon to make a connection, I have to preface all my object references with the name of the owner, i.e., Select * from mydboname.people . Is there a way to avoid having to prefix all my transact sql statements with the owner?
thanks,
steve
What would you recommend for a good MDX book ?
specifically, for syntax, data structures and sample code.
thx
C
As I posted earlier, I am relatively new to TSQL. I have been given a few books:
O'Reilly SQL on SQL Server 2005
O'Reilly SQL in a Nutshell
Mastering SQL Server 2005 - Sybex
Beginning SQL Server 2005 Programming - Wrox
I have a copy of SQL2005 Developers Edition on a development server. So I think I have some decent tools at my disposal.
I was wondering if there are any other sites or forums that might be suggested for learning SQL programming? Right now, I am just making stuff up and following examples in books, etc. I would like to find a site where there are beginner level projects for no other purpose than educational. Something where I can get more hands on of the design and programming aspects. Just to practice.
Thanks,
grinch
i have this code,
CREATE TABLE s
(sno VARCHAR(5) NOT NULL PRIMARY KEY,
name VARCHAR(16),
city VARCHAR(16)
)
CREATE TABLE p
(pno VARCHAR(5) NOT NULL PRIMARY KEY,
descr VARCHAR(16),
color VARCHAR(8)
)
CREATE TABLE sp
(sno VARCHAR(5) NOT NULL REFERENCES s,
pno VARCHAR(5) NOT NULL REFERENCES p,
qty INT CHECK (qty>=0),
PRIMARY KEY (sno, pno)
)
when i run this query i get an error saying:
Column 's.sno' is not the same length as referencing column 'sp.sno' in foreign key 'FK__sp__sno__5EBF139D'. Columns participating in a foreign key relationship must be defined with the same length.
can anyone assist me what to be done?
I am teaching myself XML and was wondering if anyone knows
any good books to gently breaking me into XML.
Kind Regards
Rob
I need to parse SQL statements directly and extract each segmentindividually. Is there a way reference the Microsoft SQL Parserdirectly from VB.Net?Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 6 Replies View Related
Forgive me for my programming ignorance, but how to i find the microsoft.sqlserverce.samples.accesssync.editdata reference?
Thanks
Hello. I'm getting an error - "Object reference not set to an instance of an object" when running an SSRS report with graphs. It only happens when the returned dataset is empty (based on parameters set by the user, it is possible to return no rows). I've tried using the NoRows property (I entered "No Data" in the property box), but I still get the same error. Is there another way to account for an empty dataset in charts?
Thanks,
Marianne
I would like to use a custom build class library written in C# inside of the vb script. Does anyone know how to add the reference to the scriptComponent project once you open script through design script button?
By the way I am using visul studio 2005.
Thanks!
Hello everyone !
Given a UDT, is there any way to get a reference to the table where the specific instance is running ?
IE: Let's suppose we have defined a UDT named UDTPoint; now we define two tables: ATable & BTable, wich both have one column that is defined as UDTPoint.
When an insert/update/delete operation on ATable or BTable occurs, the UDTPoint class needs to verify in which context it is running (ATable or BTable) before doing operations on data.
Is there any way to achieve that ?
Thanks
Giovanni
Hi,
I wanted to add a reference to an assembly in my report (not a DLL, but an EXE). As I see there is no option to select an assembly which is an EXE. Firstly, is it possible to add a reference to an EXE (I guess not)? Secondly, If no, why is it not possible?
Shyam
Hi,
Our tables have a reference field which users can enter their own references into.
However, they can choose to have the system calculate the next reference number.
The reference can contain any characters from a-z, A-Z and 0-9.
In our system, reference AAAA is greater than reference ZZZ; the next reference after AAAA is AAAB.
How would I calculate the greatest current reference in the table?