How to create many-to-many relationship between two different attributes within a cube?

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about how to create many-to-many relationship between two different attributes within a cube? (e.g. In insurance case, I want to create a many-to-many relationship between attribute broker and attribute assured company, as one broker may have many different assured companies insured under each of them, and each insurred company may be insured under different policies under different brokers). It seemed we could only create one way relationship between them, but cant make it another way around. But this is the common case in the data though. Could please any experts here shed me any light on it?

Thank you very much in advance and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

take a look at this great paper by Marco Russo:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

There are many examples in it, and probably also what you are looking for (may be the one about bank accounts).

Francesco

|||

Hi, Francesco,

Thanks a lot for the very helpful and great paper.

With best regards,

Yours sincerely,

How to create many-to-many relationship between two different attributes within a cube?

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about how to create many-to-many relationship between two different attributes within a cube? (e.g. In insurance case, I want to create a many-to-many relationship between attribute broker and attribute assured company, as one broker may have many different assured companies insured under each of them, and each insurred company may be insured under different policies under different brokers). It seemed we could only create one way relationship between them, but cant make it another way around. But this is the common case in the data though. Could please any experts here shed me any light on it?

Thank you very much in advance and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

take a look at this great paper by Marco Russo:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

There are many examples in it, and probably also what you are looking for (may be the one about bank accounts).

Francesco

|||

Hi, Francesco,

Thanks a lot for the very helpful and great paper.

With best regards,

Yours sincerely,

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.

A question for you, are you going to be enforcing this relationship via a constraint?

Scenario 1 - You are going to be enforcing this relationship.

SQL Server requires that both columns in a referential constraint have the same data type and length (size). They are not required to have the same name. If you are going to do this my recommendation is to use a SQL Server Management Studio 2005 to create the relationship while using diagram view, it will be easiser for a person who is just starting with SQL Server.

Scenario 2 - You are not going to be enforcing this relationship, you just need to query data ...

In this case you could just write a fancy T-SQL Select statement to get the data from the Database regardles of DataType, Length (size), or Field Names . The SELECT statement you would have to write would use a JOIN statement to specify that you are going to be querying the database for the information on two tables, and you would specify the fields (condition) to join the information by on the ON statement.

Ex.

SELECT *
FROM table1
JOIN table2
ON table1.field1 = table2.field2

Hope this helps,

Roberto Hernández-Pou
http://www.rhpconsulting.net

|||

open the database diagram

add the tables that you need

drag the field from the parent table to the child table.

this will create a relationship

requirements. the field from the parent table

should either have unique index or is a primary key

|||

Hi,

The basic requirements for establishing relationship(s) among tables is to set a logical DB design and then identify the objects:

Tables and their names (ENTITIES)

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!There is a relationship:WHERE A.ID = B.ID.

Does the query below run and return what you are expecting?

Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)as SomeName
From A inner join B ON (A.ID = B.ID)
Group by A.ID

|||No, the result set will be wrong because there is no relationship for A and B. Only some records for ID are the same. If I use the SQL query as you mention :

Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) as SomeName

From A inner join B ON (A.ID = B.ID)

Group by A.ID

Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) will be always the minus because A will returns correct records, but B records are more than the correct ones if there is only one B.ID= A.ID. For examples, when ID = 2, there are 3 records for A as following and there is 1 record only for Table B when B.ID= 2. But you will see the query returns wrong result for B, there are 2 more. So the result for 'Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)' equal to 200, but in fact it should be 900.
A Table | B Table
ID A.APoints1 A.APoints2| Qty BPoints
2 100 200 1 300
2 100 300 1 300
2 100 400 1 300

That is why I can't directly use SQL query for it. Is there any way to use Dataset for it?|||But there is a relationship between A and B -- the ID.

You should be able to use a subquery and a LEFT OUTER JOIN to solve your problem within your query itself:

SELECT
 C.ID,
 C.SumAPoints - (ISNULL(B.Qty,0) * ISNULL(B.BPoints,0)) AS SomeName
FROM
(
SELECT
 A.ID,
 SUM(A.APoints1 + A.APoints2) AS SumAPoints
FROM
 A
GROUP BY
 A.ID
) AS C
LEFT OUTER JOIN B ON C.ID = B.ID

Terri|||Hi, if I use the SQL query as above, only C.SumAPoints is correct, but there are more than one records for each ID if the there are more than one records in Table B for the same ID. I need only one record for each ID as following :

ID Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)
1 10
2 20
3 40

but from you query, I get the result as :

ID SomeName
1 40
1 30
1 20 <-- Only this record is what I need to display

Do you think that I need to remove myself for all those records I don't need but inside the query from your script?

How to create a Database in SQL 2005 by using a txt file.

Good morning - I have a text file with the information on how to create a
database, the relationship between tables, etc. I am new to SQL 2005. What
I would like to do is to create the database and structure by using this
following file (MYFILE.SQL) as described below.
How can I do this?
Thank you in advance for your assistance.
CREATE TABLE [dbo].[DEPT] (
[Dept_ID] [numeric](10, 0) NOT NULL ,
[DeptName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ShortCutName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PART] (
[Part_ID] [numeric](10, 0) NOT NULL ,
[PartName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PartFriendlyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL ,
[GreenDays] [smallint] NOT NULL ,
[YellowDays] [smallint] NOT NULL ,
[RedDays] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTDEPT] (
[PartDept_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[ProcessOrder] [int] NOT NULL ,
[CompletesProcess] [bit] NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTDETAILLOG] (
[PartDetailLog_ID] [numeric](10, 0) NOT NULL ,
[PartMainLog_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[PartDept_ID] [numeric](10, 0) NOT NULL ,
[StartTime] [smalldatetime] NOT NULL ,
[CompleteTime] [smalldatetime] NULL ,
[Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTINSTRUCT] (
[PartInstruct_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[StepName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StepValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StepNumber] [smallint] NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL ,
[ImageFTPAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperShowImage] [bit] NOT NULL ,
[FTPPostedDate] [smalldatetime] NULL ,
[ExtInstructionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTMAINLOG] (
[PartMainLog_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[SerialNum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PartCompleted] [smalldatetime] NULL ,
[Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[REJECT] (
[Reject_ID] [numeric](10, 0) NOT NULL ,
[DateOfReject] [smalldatetime] NOT NULL ,
[Part_ID] [numeric](10, 0) NULL ,
[Dept_ID] [numeric](10, 0) NULL ,
[RootCause_ID] [numeric](10, 0) NOT NULL ,
[EnteredBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Revision] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[ROOTCAUSE] (
[RootCause_ID] [numeric](10, 0) NOT NULL ,
[RootCauseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[RootCauseDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DEPT] WITH NOCHECK ADD
CONSTRAINT [PK_DEPT] PRIMARY KEY CLUSTERED
(
[Dept_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PART] WITH NOCHECK ADD
CONSTRAINT [PK_PART] PRIMARY KEY CLUSTERED
(
[Part_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTDEPT] WITH NOCHECK ADD
CONSTRAINT [PK_PARTDEPT] PRIMARY KEY CLUSTERED
(
[PartDept_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTDETAILLOG] WITH NOCHECK ADD
CONSTRAINT [PK_PARTDETAILLOG] PRIMARY KEY CLUSTERED
(
[PartDetailLog_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTINSTRUCT] WITH NOCHECK ADD
CONSTRAINT [PK_PARTINSTRUCT] PRIMARY KEY CLUSTERED
(
[PartInstruct_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTMAINLOG] WITH NOCHECK ADD
CONSTRAINT [PK_PARTMAINLOG] PRIMARY KEY CLUSTERED
(
[PartMainLog_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[REJECT] WITH NOCHECK ADD
CONSTRAINT [PK_REJECT] PRIMARY KEY CLUSTERED
(
[Reject_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ROOTCAUSE] WITH NOCHECK ADD
CONSTRAINT [PK_ROOTCAUSE] PRIMARY KEY CLUSTERED
(
[RootCause_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DEPT] ADD
CONSTRAINT [DF_DEPT_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[PART] ADD
CONSTRAINT [DF_PART_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[PARTDEPT] ADD
CONSTRAINT [DF_PARTDEPT_CompletesProcess] DEFAULT (0) FOR [CompletesProcess],
CONSTRAINT [DF_PARTDEPT_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[PARTDETAILLOG] ADD
CONSTRAINT [DF_PARTDETAILLOG_StartTime] DEFAULT (getdate()) FOR [StartTime]
GO
ALTER TABLE [dbo].[PARTINSTRUCT] ADD
CONSTRAINT [DF_PARTINSTRUCT_DateAdded] DEFAULT (getdate()) FOR [DateAdded],
CONSTRAINT [DF_PARTINSTRUCT_OperShowImage] DEFAULT (1) FOR [OperShowImage]
GO
ALTER TABLE [dbo].[PARTMAINLOG] ADD
CONSTRAINT [DF_PARTMAINLOG_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[ROOTCAUSE] ADD
CONSTRAINT [DF_ROOTCAUSE_RootCauseDate] DEFAULT (getdate()) FOR
[RootCauseDate]
GO
ALTER TABLE [dbo].[PARTDEPT] ADD
CONSTRAINT [FK_PARTDEPT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTDEPT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[PARTDETAILLOG] ADD
CONSTRAINT [FK_PARTDETAILLOG_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTDETAILLOG_PARTMAINLOG] FOREIGN KEY
(
[PartMainLog_ID]
) REFERENCES [dbo].[PARTMAINLOG] (
[PartMainLog_ID]
)
GO
ALTER TABLE [dbo].[PARTINSTRUCT] ADD
CONSTRAINT [FK_PARTINSTRUCT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTINSTRUCT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[PARTMAINLOG] ADD
CONSTRAINT [FK_PARTMAINLOG_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[REJECT] ADD
CONSTRAINT [FK_REJECT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_REJECT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
),
CONSTRAINT [FK_REJECT_ROOTCAUSE] FOREIGN KEY
(
[RootCause_ID]
) REFERENCES [dbo].[ROOTCAUSE] (
[RootCause_ID]
)
GO
ALTER TABLE [dbo].[ROOTCAUSE] ADD
CONSTRAINT [FK_ROOTCAUSE_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
)
GODMI wrote:
> Good morning - I have a text file with the information on how to create a
> database, the relationship between tables, etc. I am new to SQL 2005. What
> I would like to do is to create the database and structure by using this
> following file (MYFILE.SQL) as described below.
> How can I do this?
>
Launch Management Studio, then do File -> Open -> File, navigate to your
MYFILE.SQL, load it, then click Execute

How to create a Database in SQL 2005 by using a txt file.

DMI wrote:
> Good morning - I have a text file with the information on how to create a
> database, the relationship between tables, etc. I am new to SQL 2005. Wh
at
> I would like to do is to create the database and structure by using this
> following file (MYFILE.SQL) as described below.
> How can I do this?
>
Launch Management Studio, then do File -> Open -> File, navigate to your
MYFILE.SQL, load it, then click ExecuteGood morning - I have a text file with the information on how to create a
database, the relationship between tables, etc. I am new to SQL 2005. What
I would like to do is to create the database and structure by using this
following file (MYFILE.SQL) as described below.
How can I do this?
Thank you in advance for your assistance.
CREATE TABLE [dbo].[DEPT] (
[Dept_ID] [numeric](10, 0) NOT NULL ,
[DeptName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[ShortCutName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PART] (
[Part_ID] [numeric](10, 0) NOT NULL ,
[PartName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[PartFriendlyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS NOT
NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL ,
[GreenDays] [smallint] NOT NULL ,
[YellowDays] [smallint] NOT NULL ,
[RedDays] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTDEPT] (
[PartDept_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[ProcessOrder] [int] NOT NULL ,
[CompletesProcess] [bit] NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTDETAILLOG] (
[PartDetailLog_ID] [numeric](10, 0) NOT NULL ,
[PartMainLog_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[PartDept_ID] [numeric](10, 0) NOT NULL ,
[StartTime] [smalldatetime] NOT NULL ,
[CompleteTime] [smalldatetime] NULL ,
[Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTINSTRUCT] (
[PartInstruct_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[StepName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[StepValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[StepNumber] [smallint] NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL ,
[ImageFTPAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_
AS NULL ,
[OperShowImage] [bit] NOT NULL ,
[FTPPostedDate] [smalldatetime] NULL ,
[ExtInstructionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTMAINLOG] (
[PartMainLog_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[SerialNum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PartCompleted] [smalldatetime] NULL ,
[Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[REJECT] (
[Reject_ID] [numeric](10, 0) NOT NULL ,
[DateOfReject] [smalldatetime] NOT NULL ,
[Part_ID] [numeric](10, 0) NULL ,
[Dept_ID] [numeric](10, 0) NULL ,
[RootCause_ID] [numeric](10, 0) NOT NULL ,
[EnteredBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Revision] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[ROOTCAUSE] (
[RootCause_ID] [numeric](10, 0) NOT NULL ,
[RootCauseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[RootCauseDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DEPT] WITH NOCHECK ADD
CONSTRAINT [PK_DEPT] PRIMARY KEY CLUSTERED
(
[Dept_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PART] WITH NOCHECK ADD
CONSTRAINT [PK_PART] PRIMARY KEY CLUSTERED
(
[Part_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTDEPT] WITH NOCHECK ADD
CONSTRAINT [PK_PARTDEPT] PRIMARY KEY CLUSTERED
(
[PartDept_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTDETAILLOG] WITH NOCHECK ADD
CONSTRAINT [PK_PARTDETAILLOG] PRIMARY KEY CLUSTERED
(
[PartDetailLog_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTINSTRUCT] WITH NOCHECK ADD
CONSTRAINT [PK_PARTINSTRUCT] PRIMARY KEY CLUSTERED
(
[PartInstruct_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTMAINLOG] WITH NOCHECK ADD
CONSTRAINT [PK_PARTMAINLOG] PRIMARY KEY CLUSTERED
(
[PartMainLog_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[REJECT] WITH NOCHECK ADD
CONSTRAINT [PK_REJECT] PRIMARY KEY CLUSTERED
(
[Reject_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ROOTCAUSE] WITH NOCHECK ADD
CONSTRAINT [PK_ROOTCAUSE] PRIMARY KEY CLUSTERED
(
[RootCause_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DEPT] ADD
CONSTRAINT [DF_DEPT_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[PART] ADD
CONSTRAINT [DF_PART_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[PARTDEPT] ADD
CONSTRAINT [DF_PARTDEPT_CompletesProcess] DEFAULT (0) FOR [Completes
Process],
CONSTRAINT [DF_PARTDEPT_DateAdded] DEFAULT (getdate()) FOR [DateAdde
d]
GO
ALTER TABLE [dbo].[PARTDETAILLOG] ADD
CONSTRAINT [DF_PARTDETAILLOG_StartTime] DEFAULT (getdate()) FOR [Sta
rtTime]
GO
ALTER TABLE [dbo].[PARTINSTRUCT] ADD
CONSTRAINT [DF_PARTINSTRUCT_DateAdded] DEFAULT (getdate()) FOR [Date
Added],
CONSTRAINT [DF_PARTINSTRUCT_OperShowImage] DEFAULT (1) FOR [OperShow
Image]
GO
ALTER TABLE [dbo].[PARTMAINLOG] ADD
CONSTRAINT [DF_PARTMAINLOG_DateAdded] DEFAULT (getdate()) FOR [DateA
dded]
GO
ALTER TABLE [dbo].[ROOTCAUSE] ADD
CONSTRAINT [DF_ROOTCAUSE_RootCauseDate] DEFAULT (getdate()) FOR
[RootCauseDate]
GO
ALTER TABLE [dbo].[PARTDEPT] ADD
CONSTRAINT [FK_PARTDEPT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTDEPT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[PARTDETAILLOG] ADD
CONSTRAINT [FK_PARTDETAILLOG_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTDETAILLOG_PARTMAINLOG] FOREIGN KEY
(
[PartMainLog_ID]
) REFERENCES [dbo].[PARTMAINLOG] (
[PartMainLog_ID]
)
GO
ALTER TABLE [dbo].[PARTINSTRUCT] ADD
CONSTRAINT [FK_PARTINSTRUCT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTINSTRUCT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[PARTMAINLOG] ADD
CONSTRAINT [FK_PARTMAINLOG_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[REJECT] ADD
CONSTRAINT [FK_REJECT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_REJECT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
),
CONSTRAINT [FK_REJECT_ROOTCAUSE] FOREIGN KEY
(
[RootCause_ID]
) REFERENCES [dbo].[ROOTCAUSE] (
[RootCause_ID]
)
GO
ALTER TABLE [dbo].[ROOTCAUSE] ADD
CONSTRAINT [FK_ROOTCAUSE_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
)
GO|||DMI wrote:
> Good morning - I have a text file with the information on how to create a
> database, the relationship between tables, etc. I am new to SQL 2005. Wh
at
> I would like to do is to create the database and structure by using this
> following file (MYFILE.SQL) as described below.
> How can I do this?
>
Launch Management Studio, then do File -> Open -> File, navigate to your
MYFILE.SQL, load it, then click Execute