asked on

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

rafrancisco

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.

yabelson

ASKER

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??

rafrancisco

Yes, you can define a column that is a primary key as well as a foreign key at the same time.

yabelson

ASKER

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

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yabelson

ASKER

thanks alot !!
still need to try this,but thanks.
yair