• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1395
  • Last Modified:

inheritance of tables in mssql

i am trying to create several tables,when 2 tables need to inherit (by the E/R Diagram) from a Father table.
the only command i found was "Inherit" , but its on Postgre SQL which is of now ehlp to me.
the query analyzer does not acknowledge this command and gives me erros mesages (obviously).

is there a command or a serious of operation i can do in order to  create this inheritance??
b.t.w.
this also should be consistent with Oracle...

yair
0
yabelson
Asked:
yabelson
  • 3
  • 3
1 Solution
 
rafranciscoCommented:
There is no exact equivalent of PostgreSQL's "Inherit" in MS SQL Server (and I think also with Oracle) but it can be simulated.

For example, let's say you have these tables in PostgreSQL:

CREATE TABLE products (
id serial NOT NULL PRIMARY KEY,
name varchar(64) NOT NULL,
price numeric(6,2)
);

CREATE TABLE pants (
waist smallint,
length smallint,
colour varchar(12)
) inherits (products);

CREATE TABLE computers (
cpu varchar(12),
mhz smallint,
) inherits (products);

To emulate this in SQL Server, we will be creating a table for the parent table and a view for the table that inherits, like this:

CREATE TABLE products (
id INT IDENTITY NOT NULL PRIMARY KEY,
name varchar(64) NOT NULL,
price numeric(6,2)
)
GO

CREATE TABLE PantsExt (
ID INT NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES Products (ID),
Waist smallint,
[length] smallint,
colour varchar(12)
)
GO

CREATE VIEW dbo.Pants
AS
SELECT A.ID, A.Name, A.Price, B.Waist, B.[Length], B.Colour
FROM Products A INNER JOIN PantsExt B
ON A.ID = B.ID
GO

CREATE TABLE ComputersExt(
ID INT NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES Products(ID),
CPU varchar(12),
MHz smallint)
GO

CREATE VIEW Computers
AS
SELECT A.ID, A.Name, A.Price, B.CPU, B.MHZ
FROM Products A INNER JOIN ComputersExt B
ON A.ID = B.ID


For you to be able to insert to the views, since it involves 2 tables, you have to create INSTEAD OF Triggers that will take care of inserting to the 2 tables.

Hope this helps in anyway.
0
 
yabelsonAuthor Commented:
how can i create this ?
ID INT NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES Products (ID)
can i define a key which is primary AND foreign??
0
 
rafranciscoCommented:
Yes, you can define a column that is a primary key as well as a foreign key at the same time.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
yabelsonAuthor Commented:
hmmm... and if i want to define two columns as a primary key??
i looked for something like that in the MSDN,and didnt find anything that can help.
i only managed to define one primary key,which consists of one column :-(
is there any syntax to define two columns as the primary key??
(like ,say, State and City at the same time,and not only one of them)
10x
yair
0
 
rafranciscoCommented:
To define a primary key consisting of two columns, you have to use the ALTER TABLE command:

ALTER TABLE YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED ( State, City )
0
 
yabelsonAuthor Commented:
thanks alot !!
still need to try this,but thanks.
yair
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now