For ex.
Table Match_List ( MatchID, UserID_A, UserID_B)
constraints like
MatchID primary key
UserID_A <> UserID_B
unqiue index (UserID_A,UserID_B)
but I wish to exclude duplicated rows like 1,1,2 & 2,2,1,
cause UserA to UserB, and UserB to UserA are the same thing.
How can I get this?
I want to create a 1-many relationship. Parent table has a primarykey, child table has no primary key. The child table does have anindex with all four fields of the parent's PK. How can I do this?Thanks, Bob C.
View 9 Replies View RelatedHi 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
I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine what cols exist in the table?
AND, what is the syntax for such a query?
I will be receiving data back where I will receive the accountID and only the deltas will have values all other columns will be null.
My proc will update an existing record, updating the specific col when an accountID exists, it will create a new record if the accountID does not exist.
I'd like to search all the cols getting their names and values when not null.
I can construct a proc for each table searching each col by known name.
However, I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine the cols?
All my tables start with cols AccountID and end with RecID with any number of cols inbetween.
TIA
JeffP...
cross posted to ms.pub.mssqlserver.programming
Hi,
How can we use two identity column (with identity property) in table ?
GK
gk
I'm using MS SQL Server Studio Express 9.0. When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem. But how do I create a relationship with another table (foreign key)?
Bob K.
I want to create a table with primary key , and put relationship with second table.
View 5 Replies View RelatedHi, 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,
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
I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.
Operation (Commodity, OperationKey) ==========>
Commodity (Commodity)
/
/
|
|
Advice (OperationKey) ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)
I have the following measure:
Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
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 3 Replies View RelatedHi 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!
I have a XML column in my source DB. Is there any way possible using SSIS to convert the XML to ordinaty cols (varchar) in the destination db.
I am using Sql 2k5.
thanks in advance
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
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 4 Replies View RelatedWe 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 2 Replies View RelatedHi, 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...
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
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.
my table
id; part_name ; part_desc
01; xpto ; descr
02; qwerty ; azerty
03; azerty ; descr xpto
I want to search from two columns
something similar to :
SELECT * FROM tools WHERE (part_name LIKE @parameter_search or part_desc LIKE @parameter_search);
If i search for "xpto" only the first line would show.
How can i solve/go around this?
Hi
There is a table which has repeated values in more than one col.
I want to view all the distinct values for both the cols
for eg
col1 col2 col3
A B D
A B E
B C E
B C F
I want the output as
col1 col2
A B
B C
cheers
Vic
Vicky
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'.
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!
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.
Below is my sample data of my table named "Groups"
Code:
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
select * from groups;
Expected result:
Code:
with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all
select 'Microsoft' as GroupName,'Nokia' as SubGroup union all
select '' as GroupName,'EShop' as SubGroup union all
select 'IBM' as GroupName,'Meiosys' as SubGroup union all
select '' as GroupName,'UrbanCode' as SubGroup )
select * from ExpectedResult;
some sample query to how to achieve this parent-child has the same table.
Why do we need a junction table in a many to many relationship? Why can't everything be in just 2 tables?
View 9 Replies View RelatedI 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 3 Replies View RelatedI am having challenge to update the redemption table from the multiple card activation table. I want to update the redemption table with the activation date closest to the redeem date.
For example: Redeem date 20071223, I need to update the top row Date, Year, Period fields from the Card activation table.
Redeem date 20071228, I want to refer to the second row in the Card activation table date 20071228.
Redeem date 20080316 or later, I want to use the last row in the card activation table date 20080316.
How to modify the update query to select the right activation row accordingly?
Below is my partial code I used but it always pick the 20071223 date to update my redemption table.
CREATE TABLE #Card
(
[CardNumber] varchar(20)
,[ Date] int
,[ Year] int
,[ Month] int
,[ Period] int
)
[Code] ....
I have about 30 tabs with same struture.
I want to simple put column 2 in all 30 table in a new table without joining on anything. Is there a way to do that?
Thanks
JP
I have data in rows that I need to aggregate and display in a columnar fashion and I haven't been able to figure out how to do it. A simplified version of the data as it is stored in the table:
Station Month Day Reading
1 1 1 100
1 1 2 200
1 1 3 300
1 2 1 400
1 2 2 500
And I would like to create a query that returns:
Station Month Day 1 Day 2 Day 3
1 1 100 200 300
1 2 400 500
Any help you can provide or tips to steer me in the right direction are much appreciated.
Dianne Siebold
I'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
Below is my sample data of my table named "Groups"
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
[Code] ....
Expected result:
with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all
[Code] ....
How to achieve this parent-child has the same table.