Failing To Create Relationship (SQL Server Claims Table Lacks PK For Some Reason ?)
I tried to create a relationship in EM's diagram pane in
SQL Server 2000 (I'd list the version of EM, but About gives me
the MMC version, which is probably not relevant.)
(The database itself is SQL Server 2000 SP3.)
I got an error that I don't understand (because, at least
at first blush, it appears to be quite untrue). Note that
I have never clicked before on the diagrams child of the
database; this was entirely experimental.
"Primary key or UNIQUE constraint must be defined for
table 'xxx' before it can participate in a relationship."
http://msdn.microsoft.com/library/d...cantbepktbl.asp
1) My table already had a primary key; why is MS SQL Server apparently
claiming otherwise ? (I don't think I can define a second primary key
on the same table. I could perhaps define an additional unique index
on top of the primary key, but, I'm not sure.)
2) What does it mean: table '<0s>' ? That is, what does 0s mean ?
View Complete Forum Thread with Replies
Related Forum Messages:
How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?
How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram? For example: I have 2 tables, tbl1 and tbl2. tbl1 has the following columns: id {uniqueidentifier} as PK name {nvarchar(50)} tbl2 has the following columns: id {uniqueidentifier} as PK name {nvarchar(50)} tbl1_id {uniqueidentifier} as FK linked to tbl1.id If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead? mradlmaier
View Replies !
Failing To Make Relationship Between To Tables Of SQL Server DB
Hi, I'm trying to make relationship between two tables "reservation" and "charges". The column is "booking_ticket". Its giving me following error : 'reservations (akr)' table saved successfully 'charges (akr)' table - Unable to create relationship 'FK_charges_reservations'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_charges_reservations'. The conflict occurred in database 'limp', table 'reservations', column 'booking_ticket'. I used to make relationship before , but never found this problem. Kindly guide me to solve it. Regards,
View Replies !
Create Table With Many-to-many Relationship...
Hi, I come back again. Can anyone help me to create table with many-to-many relationship. Here is my three tables tbl_Networks ( NID int identity(1,1) primary key, NetworkName nvarchar(256) ) tbl_Categories ( CID int identity(1,1) primary key, CateName nvarchar(256), NID int ) tbl_Sim ( SID int identity(1,1) primary key, NID int, CID int, NameOfSim nvarchar(256) ) My problem is 1 value in tbl_Sim may have multiple values in table tbl_Categories and vice versal. And I don't know how to organise them So I need some help...
View Replies !
Why I Got The Message As You Need To Create The Many-to-one Relationship Between The Case Table And The Nested Table?
Hi, all experts here, Thank you very much for your kind attention. I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it? Hope it is clear for your help. Thanks a lot for your kind advices and I am looking forward to hearing from you shortly. With best regards, Yours sincerely,
View Replies !
Create Table With Recursive Relationship
I am fairly new to SQL and I am currently trying to createa SQL table (using Microsoft SQL) that has a recursiverelationship, let me try to explain:I have a piece of Data let's call it "Item" wich may again contain onemore "Items". Now how would I design a set of SQL Tables that arecapable of storing this information?I tried the following two approaches:1.) create a Table "Item" with Column "ItemID" as primary key, somecolums for the Data an Item can store and a Column "ParentItemID". Iset a foreign key for ParentItemID wich links to the primarykey"ItemID" of the same table.2.) create separate Table "Item_ParentItem" that storesItemID-ParentItemID-pairs. Each column has a foreign key linked toprimary key of the "Item" Column "ItemID".In both approaches when I try to delete an Item I get an Exceptionsaying that the DELETE command could not be executed because itviolates a COLUMN REFERENCE constraint. The goal behind these FK_PKrelations is is that when an Item gets deleted, all childItems shouldautomatically be deleted recursively.How is this "standard-problem" usually solved in sql? Or do I inned toimplement the recursive deletion myself using storedprocedures or something ?
View Replies !
Create A View For One-to-many Relationship Table
hi.. I would like to create a view for two tables which have a one-to-many relationship. Code: Table: Supplier Supp_ID 1 Table:Supplier_category Supp_ID,StockCategoryID 1,56 1,57 1,90 How can i create a view that has columns like below: Supp_ID, Stock 1,[56,57,90] Thanks in advance.
View Replies !
How To Use The Create Relationship Function At MS SQL Server
hi all, For those who will celebrate the chinese new year, Gong Xi Fa Cai!!! I now using microsoft SQL server to manage my database. To manage the database, I have go through the SQL Server Enterprise Manager To create my database. Now I am the stage create the relationship for my relations. So when I drag the foreign key from one relation to primary key at another relation, vice versa, it will pop up the 'Create Relationship' form. What I can saw is there are three check boxes. one check boxes have 2 sub check boxes. Quote: Checkbox 1 - [Check existing data on creation] Checkbox 2 - [Enforce relationship for replication] Checkbox 3 - [Enforce relationship for INSERTs and UPDATEs] Checkbox 3.1 - [Cascade Update Related Fields] Checkbox 3.2 - [Cascade Delete Related Records] Usually the Checkbox 1, 2, 3 had been checked. but the check box 3.1 and 3.2 is display as uncheck by default. Another question is when I linked up the relationship between two of the relations will appear a asterisk(*) beside the relation's name. Why? But I not very understand to the check boxes means and the asterisk. Can someone give me some guidelines!!Thanks Thanks in billions.... Best regards John Ang
View Replies !
Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?
Hi all, I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations: -- ImportCSVprojects.sql -- USE ChemDatabase GO CREATE TABLE Projects ( ProjectID int, ProjectName nvarchar(25), LabName nvarchar(25) ); BULK INSERT dbo.Projects FROM 'c:myfileProjects.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ======================================= -- ImportCSVsamples.sql -- USE ChemDatabase GO CREATE TABLE Samples ( SampleID int, SampleName nvarchar(25), Matrix nvarchar(25), SampleType nvarchar(25), ChemGroup nvarchar(25), ProjectID int ); BULK INSERT dbo.Samples FROM 'c:myfileSamples.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ========================================= -- ImportCSVtestResult.sql -- USE ChemDatabase GO CREATE TABLE TestResults ( AnalyteID int, AnalyteName nvarchar(25), Result decimal(9,3), UnitForConc nvarchar(25), SampleID int ); BULK INSERT dbo.TestResults FROM 'c:myfileLabTests.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE. 2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables? Please help and advise. Thanks in advance, Scott Chang
View Replies !
Create Relationship With Tables In A Linked Server
I need to create a relationship between a local table and tables on alinked server. I used the design table wizard and selected therelationship property wizard. In the reslationship property wizard,the tables that I need to get the keys from in the linked server do notshow up. Is there a way to do this, or I simply don't have enoughpermission to tables in the linked server. On the local server, theSecurity tab of linked server property has Local Loging "sa", RemoteUser "sa" and Remote Password "****". Thanks for your help.
View Replies !
SQL Server Claims 100% CPU Time
After running a process on a SQL Server for a while, the Server claims 100% CPU time and never stops again. In the log file we get: Error: 17883, Severity: 1, State: 0 The Scheduler 0 appears to be hung. SPID 53, ECID 0, UMS Context 0x01823980 We can't find any cause or solution for this problem. Does anyone know an answer?
View Replies !
One Or More Of The Server Network Addresses Lacks A Fully Qualified Domain Name (FQDN).
Hello Guys, I had been trying to solve this error with no success : One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again. The syntax for a fully-qualified TCP address is: TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port> I had installed three instances on my local machine to test Data base mirroring : Principal : running SQL Developer Instance Mirror : running SQL Developer Instance . Witness : Running SQL Express. Database mirroring already enabled using startup flag : -T1400 i even tried to configure it with out a witness but still have the same error . I used the follwoing server name in the mirroring wizard(not localhost) : Principal : TCP://Ali-laptop:5022 Mirrored : TCP://Ali-laptop:5023 Witness : TCP://Ali-laptop:5044 whats the problem guys?!
View Replies !
Append Query From Access Table To Linked SQL Server Table Failing
Strange one here - I am posting this in both SQL Server and Access forums Access is telling me it can't append any of the records due to a key violation. The query: INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue ) SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue FROM Colors_Access; Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000. There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query. I can manually append the records using cut and paste with no problems. I have tried re-linking the tables. Any ideas? Thanks, Brad
View Replies !
Trying To Create A Relationship
Here is the error I am getting'role' table saved successfully'users' table- Unable to create relationship 'FK_users_role'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_users_role'. The conflict occurred in database 'raintranet', table 'role', column 'role_id'.table rolerole_id intname varchar 50table usersusers_id introle_id inTrying to get table.role_id to be related to role.role_idAny help would be appreciated
View Replies !
How To Create A Relationship?
How do I create a relationship between two tables when the two columns that should connect each table have different names? i.e. Table Person Column Name < -- > Table Employee Column Person Name The two columns actually contain the same data type but different field name and size. I know this involves a series of steps. What are they? Could you please include some sample code.
View Replies !
Best Way To Create The Relationship
Hi - SQL beginer here.... SQL2005 I have a table dbo.server Compid - 1 Name PK - Server1 Make - HP etc... I have just created a new table dbo.ProcessorInfo with the following columns: Name BrandName ProcessorCoreCount etc.... This table will have more than 1 record for each name: Server1 Intel 2 Server1 Intel 2 Server2 Intel 1 Server2 Intel 1 and so on. What is the best way to relate the name in dbo.server and name in dbo.ProcessorInfo so this is a one to many? Obviously I can't set the name column as a PK as I have more than 1 record of the same value in it. Help much appreciated!
View Replies !
How To Create A One-to-one Relationship
hello all, I am new to SQL server and dont have a clue on how to create a one-to-one relationship in sql server2000. Say, I have a table 'A' with a PK <customer_id> and another table 'B' with a PK <order_id>. Now to define a one-to-one relation between them how to do that? thanks in advance. -tanveer
View Replies !
SQL Server 2005 Incorrectly Claims I Don't Meet Minimum Service Pack Level Requirements?
This doesn't make any sense. I am trying to install SQL Server 2005 on SBS 2003 with Service Pack 1. According to Windows Update, there is nothing left for me to install. However, I am getting this error (while installing SQL Server Express and the Developer Edition): "Your operating system does not meet Service Pack level requirements for this SQL Server release. Install the Service Pack from the Microsoft Download Center at http://go.microsoft.com/fwlink/?LinkId=50380, and then run SQL Server setup again." When I go to the link, there is nothing there to download (it takes me to the main Microsoft download page). I meet all the requirements that I have found. What gives? Thanks, Scott
View Replies !
Insert Statement For Junction Table On Many To Many Relationship Ms Sql Server 2005
Hello, This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here... http://msdn2.microsoft.com/en-us/library/ms178043.aspx At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools). http://msdn2.microsoft.com/en-us/library/ms189098.aspx and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW.... Here is how I set everything up... PetitionSet table consists of: PetitionSetID int auto-increment primary key PetitionSetName varchar(50) no nulls PetitionSetScope varchar(50) no nulls the Petition table consists of: PetitionID int auto-increment primary key PetitionSetID int no nulls PetitionName varchar(50) no nulls the SetToPetitionJunction table consists of: PetitionSetID int PetitionID int And, there is a composite key made up of both the PetitionSetID and PetitionID fields. I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition. The trigger is on the Petition table and it has the following code: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= ALTER TRIGGER .[SetToPetitionJunctionTrigger] ON .[dbo].[Petition] AFTER INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO SetToPetitionJunction (PetitionID, PetitionSetID) SELECT Petition.PetitionID, PetitionSet.PetitionSetID FROM Petition INNER JOIN PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID END I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity... The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table. Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error... Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'. The statement has been terminated. David All Rights Reserved in All Media
View Replies !
Create Trigger For Relationship
Hi, I need help in creating a trigger before delete. The trigger should be in such a way that it should display a message, if there is any relationship with other table. For example I have a table with employee details which have empid as primary key. I have another table with employee salary details where empid is foreign key. The trigger should check the relationship with these two tables. If I try to delete an emploeyee from employee details table and if there is a relationship of that employee with the salary table then the trigger should print a message. If there is no relationship then the trigger should perform the deletion. I want to create a trigger like this. Please help!!!!!!!!!!!!!!!!
View Replies !
How Do I Create A Relationship In DB Diagrammer ?
Hi Folks, When I try to create a Relationship between two Tables I find the Relationship is always created from the Table I start with to itself. I seem to have to select a Table to enable the Create Relationship option, and then when I 'Add' it creates a recursive Relationship to that Table. I guess it must be possible to do what I am trying to do ? I'd appreciate any advice. Thanks. Barry
View Replies !
How T Create Relationship B/w Two Tables
Hi I have two database as Malathi,Indoo In Malathi database i have Employee Table AS ---------------------- Eid Int (PK) Enam varchar(50) In Indoo Database I have Job table as Eid int (FK) Jid Int (PK) Now how can i create relation b/w two tables of different database Any One can help for this query Malathi Rao
View Replies !
Unable To Create Relationship FK
Hi. I get this error when i try to create a relationship in a db diagram (sql 2005) "'tblActivedir' table saved successfully 'tblClient' table - Unable to create relationship 'FK_tblClient_tblActivedir1'. Introducing FOREIGN KEY constraint 'FK_tblClient_tblActivedir1' on table 'tblClient' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors." What i have is 2 tables. 1 named client 1 named activedir In the client table the columns i want to bind with activedirtable are FR1 and DC1 I want to bind them in the ID of the activedir table (both, in different fk relationships) so that they get the id of activedir. Fr1 has an fk relationship with activedir (pk is activedir' id) and DC1 exactly the same in another fk. So i want both columns to comunicate with activedir. If p.e. activedir has 3 elements (a,b,c) when i delete element a then werever FR1 or DC1 have this element(binded to it's id) then the element will also be deleted (id of the element) from both FR1 and DC1 I don't want to set Delete and Update action to none because i want the element changed or deleted from activedir, to do the same on Fr1 or DC1 or both. Any help? Thanks.
View Replies !
How To Create A Datagrid For Two No Relationship Tables
Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as : ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong: Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID May I know is there solution for it? Thank you very much!
View Replies !
Stored Procedue 2 Create A Relationship.
i have a table where the feilds are 1.fromtable 2.fromfeild 3.fromcategory 4.totable 5.tofeild 6.tocategory. i have write a stored proceduer for creating 2 relationship between fromfeild & tofeild from the same value in from category & in tocategory. there r around 465 records in a table. so anyone can comeout for solution of this. hope soon i get a solution for this.
View Replies !
Create Or Show Relationship Digram
Dear sir or madam I have a problem related to create or show relationship digram in sql server 2005. Especially, I want to show relationship diagram that I established in sqlserver 2000 in sql server 2005 but I can't and I don't how to do it. I look forward to hearing. Thank you in advance! Best regard, seyha moth
View Replies !
CREATE FTC Failing For
Trying to create a catalog as seen below: CREATE FULLTEXT INDEX ON [dbo].[AttachFiles]( [BinFile]) KEY INDEX [PK_AttachFiles] ON [Dossiers_FTC] WITH CHANGE_TRACKING OFF GO Getting error of: Msg 7655, Level 16, State 1, Line 1 TYPE COLUMN option must be specified with column of image or varbinary(max) type. BinFile is an Image datatype. What do I need to update on my CREATE statement above to make this work?
View Replies !
Mssql Lacks
HelloI have recently decided to upgrade my programs to enable users to have mssqldatabases instead of access.I have since then run into many incompatibilities between their sql:access has IIF(x>y,a,b) whereas mssql hase case when (this already meanshundreds of changes in queries)it does not have format(number,'#,##0.00') or format(date,'dd.MM.yyyy') butmost surprising is following:access allows this while mssql reports error ("Cannot perform an aggregatefunction on an expression containing an aggregate or a subquery")SELECT ....SUM(a*b/(SELECT SUM(c) FROM d WHERE e=f))FROM ...(a-f are fields and tables )I am totaly dissapointed in MS since I will have to have two variants ofqueries in programs just to enable users to choose between databases.Does anyone know an MS e-mail where I could flame them
View Replies !
Variable Value Not What It Claims To Be
this is not making a whole lot of sense to me. I am trying to do this very simple query: SELECT ID FROM Equipment WHERE CMMS_ID=@EqNum This is inside a cursor where I select from an oracle datasource. @EqNum is declared as an nvarchar(15), after selecting into it from the cursor I print the variable and it reads: 000000000015552 perfect. But when it executes against the above query I get no results. However, if I execute the following query manually SELECT ID FROM Equipment WHERE CMMS_ID='000000000015552' it returns results. Aren't these supposed to be exactly the same??? Why am I not getting results when I use the variable? Also CMMS_ID column is an nvarchar(15) column. Is this weird or what? Is oracle really returning something different than is what is printing??
View Replies !
LogReader Agent Lacks Required Privilege
I'm using our dev ID to set up peer-to-peer replication among three SQLServer 2005 servers. The dev ID essentially has sa rights to the serverand databases. Per the BOL, I explicitly gave the login db_owner rightsto both the distributor and publication databases. Nevertheless, theLogReader agent fails with the following error message:Executed as user: mydomsqldev. A required privilege is not held by theclient. The step failed.The SQLAgentManger on each of the three boxes also runs with the sqldevID. Anybody have any idea what the "required privilege" is that theLogReader agent is missing?Any help greatly appreciated.Randy
View Replies !
Log Reader Agent Lacks A Required Privilege
I'm using our development ID to set up peer-to-peer replication. I am following the steps outlined in the BOL using SQL Studio 2005. When I created the initial publication, I read the section on permissions and assigned the development login ID db_owner. Indeed, to eliminate any possible issues (I thought), I gave it db_datareader, db_datawriter, and all the db admin rights and made very sure the db_denydatareader and db_denydatawriter roles were not checked. I did this through the Logins section of the Security folder and applied the same rights to both the distribution and the publication database. Nevertheless, when I run the LogReader agent job, it fails with the message for step 2 that: Executed as user: ourdomsqldev. A required privilege is not held by the client. The step failed. The dev ID essentially has sa rights to the server and all the databases. It is also a domain authenticated ID. Any ideas what's causing this problem? I really need to get the replication going reliably before the weekend is out. Thanks. Randy
View Replies !
Updatable Cursor Claims To Be READONLY
Can anyone tell me why this might return that the cursor is readonly? As you will notice, I am using the FOR UPDATE OF property. DECLARE @txtStudentID varchar(50) DECLARE @lngStudentID int DECLARE @SiteLast tinyint DECLARE @txtStudentID_Last varchar(50) SELECT @SiteLast = 0 DECLARE txtStud_cursor SCROLL CURSOR FOR SELECT txtStudentID, lngStudentID FROM tblStudentASECertification# FOR UPDATE OF lngStudentID OPEN txtStud_cursor FETCH LAST FROM txtStud_cursor INTO @txtStudentID_Last, @lngStudentID FETCH FIRST FROM txtStud_cursor INTO @txtStudentID while @SiteLast != 1 BEGIN SELECT @lngStudentID = (SELECT DISTINCT lngStudentID from tblStudent WHERE txtStudentID = @txtStudentID) UPDATE tblStudentASECertification# SET lngStudentID = @lngStudentID WHERE CURRENT OF txtStud_cursor if (@txtStudentID = @txtStudentID_Last) Select @SiteLast = 1 FETCH NEXT FROM txtStud_cursor INTO @txtStudentID END CLOSE txtStud_cursor DEALLOCATE txtStud_cursor
View Replies !
Cannot Run DBCC Repairs. Claims No Single User
Hi all, I have a test DB that when suspect on a box that had a disk I/O problem. Got that repaired, no I am looking at trying to bring up the DB. We need this db back, and yes, no backups. Win2k box with SQL2k SP3 Problem I am having is getting DBCC to do any repairs. I can do a DBCC CHECKDB and of course see the errors. When I try to run any type of DBCC repair I get the old Repair statement not processed. Database needs to be in single user mode. I have confirmed that the DB is in fact in single user mode, both in the Manager and QA, Even went so far as to start the entire server in single user mode yet still get the error. I have two dbs that I am trying to recover and they both bark out the same errors. Any pointers as to where I am going wrong? Thanks, Chris PS Commands I am using. use master go dbcc checkdb ('dbname', 'repair_rebuild') ******************* Use master go sp_dboption dbname, single, true ******************* UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='dbname'
View Replies !
What Relationship To Use Between These Two Table?
Hi I have two tables: 1.) Operator-OperatorID{PK, int, not null}-OperatorName{varchar(100), not null}-Enabled{bit, not null}-PasswordChange{bit, not null}-BirthDate{datetime, not null} 2.) Password-PasswordID{PK, int, not null}-Password{varchar(50), not null}-ExpirationDate{datetime, not null} I'm not sure how to design and layout these two tables. The layout of these two tables is completely flexible as the application has not been deployed. I'm open to any good suggestions. For each Operator I want to stored up to 3 previous passwords plus their current password. The password change field is so that if the operator's password expires or gets reset, they will be forced to enter a new password. This is a simple internal company application, so password encrypting is not necessary. The ExpirationDate indicates the date that the password will expire. Hope to hear from someone soon! Thank you!
View Replies !
Table Relationship
Hello, I created some SQL 2005 tables using Microsoft SQL Server Management Studio. I need to get the script code of those tables. I was able to do that by right clicking over each table. But how can I get the code for the relationships between the tables? Can't I create relationships between two tables by using T-SQL? Thanks, Miguel
View Replies !
Naming Of Relationship Table
We have two tables. Users and Projects and there is a many-to-many relationship.Ex. A user can be assigned into multiple projects.For the relationship table, should the table name be UserProjects or ProjectUsers?Also should it be singular or plural? (ex. UsersProjects or ProjectsUsers)?
View Replies !
Relationship Inside The Same Table
i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation
View Replies !
Define More Than One Relationship Per Table?
Why is it not possible to define more than one relationship per table? i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives? thank you
View Replies !
Combine Relationship Table
Hello everyone: I have two database desgin questions. I hope could get some helps here to resolve my puzzle. suppose I have a parent table which has many-to-many relationship to its three children tables. E.g: Parent table: webpage -pageID -pageTitle child table: (which are the content could be in the webpage) Picture ---pictureID ---pctureName StreamVideo --videoID --videoName StreamAudio --audioID --audioName Normally, in a many-to-many relationship, a third table will be introduce to break down the many-to-many into one-to-many relationship. So in this case, I will have three relationship tables 1. Webpage--Picture --pageID --pictureID 2. Webpage--Video --pageID --videoID 3. Webpage--Audio --pageID --audioID My question is should I combine this three relationship table into one big relationship table like: Webpage--content --pageID --pictureID --videoID --audioID I know the drawback of this combining will cause redundant, but this could avoid join tables when writting query (especially when there are more child table associate with one parent table). Also I was told that foreign key can be nullable, so if one webpage only have pictures, as long as I insert pictureIDs with the null value videoIDs and audioIDs into the Webpage--content table, then it seems I don't duplicate anything. So far I don't see anything wrong with this approach, I hope someone can give me some suggestions whether I should go this way. My another question is also about the relationship table, suppose in the above exmaple, we won't combine those three relationship tables. But we need to assign a value to each pictureID, videoID, audioID. And this value will be different if a pictureID, videoID, aduioID appears in different webpage. How I should solve this issue? The best I can think so far is add a value column in the relationship table. But I have not seen people did this before. Add columns into a relationship table. Is this a good desgin?
View Replies !
Table Relationship In VB Express
I have 3 tables I am trying to relate for a music player. I was following the example in the msdn however, my relationships do not seem right. Here are tables i want to relate: Table 1: ArtistID ArtistName: Table 2: RecordingID RecordingName ArtistID Table 3: TrackID TrackName TrackNumber TrackLocation RecordingID So the main idea here is that the foreign keys are recordingId and artistID. So what i did is created the 3 tables and then make a diagram to create the relationships. I then was reading this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=219696&SiteID=1 What i want to do is use 2 different text boxes lets say and as i move from the records in the Record_table (2) the corresponding artist will change with it. However in the dataset the relationship looks like this: Artist -> Recording -> Track.... inorder for me to get this relationship to work correctly i have to change all the relationships in the dataset diagram. This way the dataset would look like this: Track -> Recording ->Artist. This way i can use the 2 bindings to reference each other as stated in the link above. why doesnt the relationship of the database know this already? why do i have to change the relationship in the datasets.
View Replies !
Table Relationship. What Am I Doing Wrong?
Hello, I have 3 tables: Articles, ArticlesTags and Tags. ArticleTags relate Articles and Tags records. When I try to delete a record in Tags I get the following error: The DELETE statement conflicted with the REFERENCE constraint "FK_ArticlesTags_Tags". The conflict occurred in database "MyDB", table "dbo.ArticlesTags", column 'TagID'. The statement has been terminated. What am I doing wrong? Here is how I am creating my tables: create table dbo.Articles ( ArticleID uniqueidentifier not null constraint PK_Article primary key clustered, Body nvarchar(max) not null ) create table dbo.ArticlesTags ( ArticleID uniqueidentifier not null, TagID uniqueidentifier not null, constraint PK_ArticlesTags primary key clustered (ArticleID, TagID), constraint FK_ArticlesTags_Articles foreign key(ArticleID) references dbo.Articles(ArticleID) on delete cascade, constraint FK_ArticlesTags_Tags foreign key(TagID) references dbo.Tags(TagID) ) create table dbo.Tags ( TagID uniqueidentifier not null constraint PK_Tag primary key clustered, [Name] nvarchar(200) not null ) Basically, what I need is: 1. If an Article is deleted then: > Delete all records for that Article in ArticlesTags > Don't delete any Tag in Tags. 2. If an Tag is deleted then: > Delete all records associated with it in ArticlesTags. > Don't delete any Article in Articles. What am I missing? Thanks, Miguel
View Replies !
Problems Creating Second Relationship To The Same Table
I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error: - Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.
View Replies !
Search On Table With Recursive Relationship
I am using tables with recursive relationships extensively. For example the table tbComponent has a primary key called Co_ID and a foreign key called Co_Co_ID which references the field Co_ID. This allows a component to have unlimited child components, and each child component can have an unlimited number of tiers of children. I have a few question for which I have seen no documentation on. 1. How can I create a view or a SP that will return a component record and all of its children and children's children records down to the last/lowest child record? 2. I need to be able to do a search in this table. Example: Table: tbComponent Columns: Co_ID Integer Co_Co_ID Integer Co_Name Text Co_Attribute Text Co_Category Text Note: the data for Co_Category comes from a lookup table with also has a recursive relationship to itself where a category can have an unlimited number of tiers of children categories. A typical group of records could be something like this: Co_ID Co_Co_ID Co_Name Co_Attribute Co_Category 1 1 Car Blue Ford 2 1 Body Steel Parts 3 2 Door Front Parts 4 3 Invoice April 1 1999 Accounting Ok, say there is over a million records in this table. Say I want to query this table and return all of the invoices for cars between March 1 1999 and May 1 1999. Say for example that there are less records where Co_Name has a value of Car then there are records with a value of Invoice, so logically I would set some kind of criteria to limit only invoices with where Co_Name = "Car". That's easy, I can return a result set of all the records Where Co_Name = "Car" and I can hold these in a view or a temp table. Now I need to query this View or temp table and see if it has any children records records Where Co_Name = "Invoice" . The problem is that the Invoice child could be a child record directly under the "Car" record, or 10 levels of children records down. The logic for this would be: If Co_Name = "Car" Then Select * Where Co_Co_ID = 1, then take all of those record's values in Co_ID and run another statement Where Co_Co_ID = X and so on until there are no more children to search. If any of those records have a value of Invoice in Co_Name then return them. Please don't give any speeches on Normal Relationship Database design suggesting a typical design using something like a Car table, a parts table, and a invoice table. I used the example above to demonstrate the dynamics of doing a search on a recursive relationship. I understand relationship theory and a recursive relationship is what we need to use in our situation. Thanks for any and all input!
View Replies !
|
TRACKER
