0

I have a table type named EmpType with the following structure:

CREATE TYPE EmpType AS TABLE
(
ClientId UNIQUEIDENTIFIER,
EmployeeId INT,
Experience SMALLINT,
Age SMALLINT
);

This query ran successfully in the MS SQL Server.

Now that I try to construct another table using this table type it gives me an error.

CREATE TABLE EmpHashIndex2
(
HashIndex SMALLINT CONSTRAINT has_pk PRIMARY KEY,
TableName dbo.EmpType
);

After executing the above query it gives an error as:

The column "TableName" does not have a valid data type. A column cannot be of a user-defined table type.
sticky bit
  • 36,626
  • 12
  • 31
  • 42
Samarth
  • 36
  • 4
  • 4
    If you want a column of that type, then whats is unclear in the message "A column cannot be of a user-defined table type."? It's a resounding no, isn't it? If you want a table of that type however see ["create SQL Server table based on a user defined type"](https://stackoverflow.com/questions/22390358/create-sql-server-table-based-on-a-user-defined-type). – sticky bit Sep 04 '18 at 11:37
  • Looks like you're trying to store multiple rows in a single row. That's not how a DBMS works. you need to create 2 tables, and define your primary and foreign key so that you have a define many to one relationship. – Thom A Sep 04 '18 at 12:02
  • @Samarth please describe the task you're struggling with. What are you trying to achieve with such a model? – Ivan Starostin Sep 04 '18 at 12:12
  • Of course not!!! A table cannot have a table as a column. That is like asking if you can put an entire excel tab in a single cell. It makes no sense. – Sean Lange Sep 04 '18 at 13:49
  • I just want to each cell of the 'TableName' column to point to a different table which I can utilize elsewhere. – Samarth Sep 05 '18 at 12:22

1 Answers1

0

It seems to me that you are trying to apply OOP approaches to Relational DB. Something like "pointer"s to any data. That is not how it works in RDBMS.

Perhaps you need a relational schema like this:

Client <- EmployeePerClient -> Employee

None of them has to be a table type, all of them must be tables with foreign keys defining their dependencies.

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39