How To Create Two Tables That Reference Each Other?
Jan 14, 2008
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)
)
Hi All, Can anyone tell me how to create a reference for composite key. For ex, I have created tblEmp table successfully. create tblEmp( empId varchar(100), RegId varchar(100), empname varchar(100),constraint pk_addprimary key(empId, RegId) ) And now, I am going to create another table which references the composite key.create table tblAccount( acctId varchar(100) primary key, empId varchar(100) references tblEmp(empId), RegId varchar(100) references tblEmp(RegId) ) But it gives error like Server: Msg 1776, Level 16, State 1, Line 1There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors. Could anyone please let me know how to create reference for composite key. Thanks in advance,Arun.
Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?
I 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?
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.
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
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object. Here are some clipits of what you need. Imports SQLDMOPrivate myServer As New SQLServer Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString() Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString() Dim storedProcedure As New StoredProcedure Dim qresults As SQLDMO.QueryResults Example Stored Procedure Try myServer.Connect(serverName, userName, password) storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) Label1.Text = "Creation of stored procedure successful"Catch ex As Exception Label1.Text = "Creation of stored procedure failed" Finally myServer.DisConnect()End Try Run Stored Procedure and Obtain Results myServer.Connect(serverName, userName, password) qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows MemberRowNo = qresults.GetColumnString(num, 1) Next myServer.DisConnect()
CREATE ASSEMBLY asmRANDRTCalendaringGateway FROM 'E:sqlRANDRTCalendaringGateway.dll' WITH PERMISSION_SET = UNSAFE
I get this error ...
Msg 10301, Level 16, State 1, Line 1 Assembly 'RANDRTCalendaringGateway' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Even though I do not explicity refer to system.web in the DLL's code ...
using Microsoft.Win32; using System; using System.Data.Sql; using System.Data.SqlClient; using System.Collections.Generic; using System.Configuration; using System.Text; using log4net; using ADODB; using MAPI; using CDO; using System.Runtime.InteropServices;
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.
I was hoping to instantiate a channelfactory from a clr stored proc in order to send messages to a wcf endpoint but VS 2005 wont allow refs to anything other than what it calls "SQL Server" targets. Is calling a wcf endpoint from clr sp's possible? I've alreay read the article on serialization under ss2005 but dont believe it has the answer to this particular piece of the puzzle.
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?
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
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?
Just installed VS 2005 & SQLServer 2005 clients on my workstation. When trying to create a new Integration Services Project and start work in the designer receive the MICROSOFT VISUAL STUDIO 'Object reference not set to an instance of an object.' dialog box with message "Creating project 'Integration Services project1'...project creation failed."
Previously I had SQLServer 2000 client with the little VS tool that came with it installed. Uninstalled these prior to installing the 2005 tools (VS and SQLServer).
I'm not finding any information on corrective action for this error.
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
I am a serious NT sql NEWBIE. I have installed v7 on my NT/MIIS. I want to allow another party now to create tables on their SQL db they have runnning on my server (their own domain)
What is the best way -? Do they send me the scripts or is there a way to run them remotely and create them.
Hi! I am using VS 2003 (v 1.1) I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net. How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation. Give me the complete code with can example (if possible)
I was just looking at the AdventureWorks Database Sample and I see the Tables and the Procedures have different prefixes other than dbo. How can I create Tables that use different prefixes as I would like to organize my tables better. Thanks in advance.
Hello,In my web site I have 2 types of users: students and professors.All users need to login but the personal information for each user type has different fields.So I create 3 tables: professors, students and authentication,"professors" will have all the personal data from professors."students" will have all the personal data from students.authentication will have the username and password from all users.How can i link authentication table to both professors and students tables so when the user logins i get the data from that user, either a professor or student?And is the right way to do this?I am using SQL 2005 and Asp.Net 2.0Thanks,Miguel
I know this may sound stupid but I really can't figure out how to do it. I just keep going around in cricles.
I need to create six table under three different users. so I would have users1 owning tableA and tableB. Then I would have users2 owning another copy of tableA and tableB. same for users3.
But I just can't find a way of doing it, I am using enterprise manager. Please help, Ed
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
Hi, I have a users table in my SQL Server database. Now, I am looking to create a table (or multiple tables) to allow users to post their weekly events, meetings, activities, and accomplishments to the database. Each Monday morning, each user will enter their new schedule for that week and the previous week's entry will be archived in the database. My question is: what would make more sense? Should I create one big table that would have the following columns:
- Week Number (the current week number in the year) - User ID - Events - Meetings - Activities - Accomplishments
And each user would have one row in the database per week.
OR should I create 4 separate tables named Events, Meetings, Activities, Accomplishments. Each of these tables would have the following columns: (for instance, the Events table would contain:)
Each time a user adds a new event to their schedule, a new row in the Events table is created. Each time a user adds a new accomplishment, a new row in the Accomplishments table is created. etc., etc.
Which approach seems to make more sense and would be easier to maintain? Also, which one would conserve database space better and result in faster querying.
I have to create a history of tables. Therefore I must know the previous structure of the table. I have to document the changes of the structures of the tables. Is there a solution for the version 2000?
I'm attempting to use SQL statement to create tables within a new database, and I'm somewhat lacking in experience for this. Normally I'd simply create them through the GUI, but I want to get the hang of DDL statements.
I have written what I think are scripts to create one table with a primary key, create a unique index on it, and then create another table with a primary key and constrain two columns in the second table to the non-primary unique index in the first.
Code: Use SKUTracking CREATE TABLE SKUTracking.dbo.tblOraUnits
