… to avoid “Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths”

In most cases if you need to support linking of more than one ID from one table into another table, you would use an intermediary table, this allows any number of links to be established and details of each link can be supplemented with additional details.

However when maintaining and upgrading an existing database that has been around for many years, or even designing a new one, were there will only ever be a need to link in a specific number of IDs, it may be the case that the IDs are placed within the other table directly. 

Doing this prevents the ability for SQL to use the ON DELETE CASCADE and ON DELETE SET NULL from working.

Indeed even if there is only the one ID linked in, but there is more than one round table link you cannot have multiple ON DELETE CASCADE foreign keys established between the tables because it will also fail. Both these examples give the error “Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths”.

I recently embarked on upgrading an existing database to include foreign keys relationships (previously controlled by software only), so that we could introduce full support for merge replication.

The database includes over 360 tables with around 860 relationships to be establish between tables, it is used by over 4500 users in a large number of organisations, with around 70 modules (or applications) accessing the data.

As the applications have been designed and written over the past 10 years by several developers there is no way to guarantee that all the relationships between tables are being enforced correctly… Leaving aside the creation of the FKs and the verification of existing data for another blog, I needed a way to maintain compatibility with all the applications currently using the database. The only way to do this while properly enforcing the foreign keys would be to delete or set to NULL the appropriate linked data on deletion of a row, rather than rely on the application to perform the action.

Having searched for quite a while I found that there was many examples of people recommending using an INSTEAD OF DELETE TRIGGER, to accomplish it, though I could not find any examples. After searching for some time the only examples I found contain hard coded references to linked tables. This is no way ideal and would be a nightmare to maintain with over 860 foreign key links between over 360 tables, which are added to and modified on a regular basis as the applications continue to grow.

Anyway to cut a long story short and get to the code I wrote a TRIGGER that would satisfy my needs, based on a few assumptions.

1. If a Foreign Key column is nullable I am assuming it is not dependant on the Primary Key data and should be set to NULL rather than deleted.
2. If the Foreign Key does not support NULLs then the data must be dependant on a valid link and will be deleted… obviously in this case I am assuming that all of the FKs that do not support NULLs are not to be set to a default value from the PK table.
3. Within the TRIGGER I am will be checking if the FK constraint has a referential action, is disable, is trusted (enforced) and also isn’t a link back to the PK table itself.
4. The Trigger is designed to fail if fired against a table that has a multi-column Primary Key.

It is worth noting when using a trigger it can be run on multiple rows and is not run for each row deleted, so must handle this

Now I am a bit short of time tonight so I am just going to just show the source of what I have done and assume you will be able to interpret it….

The trigger will cycle through all Foreign keys based on the tables name and the primary key column name. It then deletes or updates the dependant data as appropriate, as it does so (assuming the same trigger is on the dependant tables) if the dependant tables have an update or delete trigger on them it will in turn trigger that causing a cascade effect… As a result of this it could potentially result in a long loop so needs to be used with caution and the database’s foreign keys need to be established carefully.

You should not use this trigger if you are unsure, and I bear no responsibility for any data that is lost as a result of it’s usage! The trigger is based on T-SQL for MS SQL Server 2005, INSTEAD OF DELETE does not work in MS SQL 2000 and I have not tested it on MS SQL 2008.

The trigger looks something like the following; it is based on a table called [Table_1] with a Primary Key column that is either an integer or varchar called [ID], I will be adding it to all tables I’ll be writing a script that just runs through all the tables adding it: –

Code revised 31st Jan. 2009

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIG_PK_Table_1_Delete_FKs]’))

DROP TRIGGER [dbo].[TRIG_PK_Table_1_Delete_FKs]

GO

/**

INSTEAD OF DELETE

AS

BEGIN

    SET NOCOUNT ON

      PRINT ‘***************************************************’

      PRINT ‘RUNNING – TRIG_PK_Table_1_Delete_FKs’
 
      — before doing anything lets check we
      — actually have something to delete!
      DECLARE @Counter_DELETED_ROWS INT
      SELECT @Counter_DELETED_ROWS = COUNT([EQUIPMENTID]) FROM DELETED

      IF (@Counter_DELETED_ROWS <= 0)
      BEGIN
          PRINT ”
          PRINT ‘0 records deleted’
          PRINT ”
      END
      ELSE
      BEGIN   

    — 1) we only wish to deal with tables that have one Primary Key…

    —    so lets check that this table has only one Primary Key

    DECLARE @Counter_PKs INT

    SELECT @Counter_PKs = COUNT(Col.Column_Name) FROM
            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col

            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab

            ON  Col.Constraint_Name = Tab.Constraint_Name AND
            Col.Table_Name = Tab.Table_Name

      WHERE Tab.Constraint_Type = ‘PRIMARY KEY’ AND
            Col.Table_Name = ‘Table_1’

      — 2) Verify only 1 found

      PRINT ‘  Found ‘ + CONVERT(VARCHAR(255), @Counter_PKs) +
            ‘ primary keys in table’

      IF (NOT (@Counter_PKs = 1))

      BEGIN

            — Lets throw an error if this trigger has been established 

            — against a table with more than one Primary Key, could

            — alter this later to handle them but no requirement to

            — do so at the moment

            DECLARE @tn varchar(4000)

            SELECT @tn = object_name(parent_obj)
            FROM sysobjects WHERE id = @@procid

            SET @tn = ‘Casscade deletes not allowed for this ‘ +
                   ‘table due to multiple primary key columns: ‘ + @tn

            PRINT @tn

            IF EXISTS(SELECT TOP(1) * from deleted) 

                  RAISERROR (@tn, 16, 1)   

      END

      ELSE

      BEGIN

            — 3) Lets get all the Foreign Keys assosiated with this table

            DECLARE @ForeignKeys TABLE (TableTempID bigint
               IDENTITY(1,1) PRIMARY KEY CLUSTERED,

               [Name] varchar(2000),[OBJECT_ID] int, [is_disabled] bit,
               [is_not_for_replication] bit,
               [delete_referential_action] int,
               [update_referential_action] int,
               [Fk_table_name] varchar(2000),
               [Fk_table_schema] varchar(2000),
               [Pk_table_name] varchar(2000),
               [Pk_table_schema] varchar(2000),
               [Fk_col_name] varchar(2000),
               [Pk_col_name] varchar(2000),
               [constraint_column_id] int,
               [is_not_trusted] bit)

            INSERT INTO @ForeignKeys
               ( [Name], [OBJECT_ID], [is_disabled],
                 [is_not_for_replication], [delete_referential_action],

                 [update_referential_action], [Fk_table_name],
                 [Fk_table_schema], [Pk_table_name], [Pk_table_schema],

                 [Fk_col_name], [Pk_col_name], [constraint_column_id],
                 [is_not_trusted] )

               ( select Fk.name, Fk.object_id, Fk.is_disabled, 
                  Fk.is_not_for_replication, Fk.delete_referential_action,

                  Fk.update_referential_action,
                  object_name(Fk.parent_object_id) as Fk_table_name,

                  schema_name(Fk.schema_id) as Fk_table_schema,
                  TbR.name as Pk_table_name,
                  schema_name(TbR.schema_id) Pk_table_schema,

                  col_name(Fk.parent_object_id,
                  Fk_Cl.parent_column_id) as Fk_col_name,

                  col_name(Fk.referenced_object_id,
                  Fk_Cl.referenced_column_id) as Pk_col_name,
                  Fk_Cl.constraint_column_id,

                  Fk.is_not_trusted

                 from sys.foreign_keys Fk
                  left outer join sys.tables TbR

                 on TbR.object_id = Fk.referenced_object_id
                  inner join sys.foreign_key_columns Fk_Cl

                 on Fk_Cl.constraint_object_id = Fk.object_id

                 where

                  ( ([TbR].[name] = ‘Table_1’) AND
                    (col_name(Fk.referenced_object_id, 
                        Fk_Cl.referenced_column_id) = ‘ID’) AND
                    ([Fk].[delete_referential_action] = 0) AND

                    ([Fk].[is_disabled] = 0) AND
                    ([Fk].[is_not_trusted] = 0) ) )

            — 4) Lets see how many Foreign Keys we are dealing with

            DECLARE @Counter bigint

            DECLARE @MaxTableCount BIGINT

            SELECT @MaxTableCount = max(TableTempID) from @ForeignKeys

            SET @Counter = 1

            PRINT ‘  Found ‘ + CONVERT(VARCHAR(255), @MaxTableCount) +
                 ‘ foreign keys links for this table’

            DECLARE @Fk_table_name VARCHAR(2000)

            DECLARE @Pk_table_name VARCHAR(2000)

            DECLARE @Fk_col_name VARCHAR(2000)

            DECLARE @Pk_col_name VARCHAR(2000)

            DECLARE @is_not_trusted BIT

            DECLARE @is_disabled BIT

            DECLARE @delete_referential_action INT

            DECLARE @Fk_col_IsNullable BIT

            DECLARE @sqlQuery nVarchar(4000)

            — 5) Lets cycle through all the Foreign Keys

            WHILE (@Counter <= @MaxTableCount)

            BEGIN

                 SELECT @Fk_table_name = ltrim(rtrim(Fk_table_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Pk_table_name = ltrim(rtrim(Pk_table_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Fk_col_name = ltrim(rtrim(Fk_col_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Pk_col_name = ltrim(rtrim(Pk_col_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @is_not_trusted = ltrim(rtrim(is_not_trusted))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @is_disabled = ltrim(rtrim(is_disabled))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @delete_referential_action =
                   ltrim(rtrim(delete_referential_action))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Fk_col_IsNullable = c.IsNullable
                  FROM syscolumns c WHERE c.id =
                   OBJECT_ID(@Fk_table_name) AND
                   c.NAME = @Fk_col_name

                  PRINT ‘—————————————‘

                  PRINT ‘  Processing key ‘ +
                    CONVERT(VARCHAR(255), @Counter) + ‘ of ‘ +
                    CONVERT(VARCHAR(255), @MaxTableCount)

                  PRINT ‘  @Fk_table_name "’ + 
                    CONVERT(VARCHAR(255), @Fk_table_name) +
                    ‘" ~ @Fk_col_name – "’ +
                    CONVERT(VARCHAR(255), @Fk_col_name) + ‘"’

                  PRINT ”

                  — 6) Lets verify that there is no CASCADE or
                  —    UPDATE already on the Key link and that
                  —    it is not a system table… also

                  —    need to verify that the Foreign Key Table
                  —    is not the same as the current table and
                  —    that Key should be enforced.

                  IF (

                              (@delete_referential_action = 0) AND

                              (@is_not_trusted = 0) AND

                              (@is_disabled = 0) AND

                              (@Fk_table_name <> ‘dtproperties’) AND

                              (@Fk_table_name <> ‘sysdiagrams’) AND

                              (NOT (@Fk_table_name = @Pk_table_name))

                        )

                  BEGIN

                        — 7) Because we need to execute deletes with
                        —    a "EXECUTE sp_executesql…" statement the

                        —    DELETED table is not available so we must
                        —    cycle through it in case it is a batch
                        —    delete.

                        —

                        —    We convert the values to Varchar here to
                        —   account for PKs that are text and integer,
                        —   plus we need it as text later

                        —

                        —    This is because we do not know what all
                        —    the FK column names will be so we need

                        —    to build the query

                        DECLARE @DELETED_ROWS TABLE
                          (TableTempID bigint IDENTITY(1,1)
                            PRIMARY KEY CLUSTERED,

                           [ValueINNER] varchar(4000))

                        INSERT INTO @DELETED_ROWS

                          ( [ValueINNER] )

                          ( SELECT CONVERT(VARCHAR(4000), [ID])
                            from DELETED )

                        DECLARE @CounterINNER bigint

                        DECLARE @MaxTableCountINNER BIGINT

                        DECLARE @ValueINNER VARCHAR(4000)

                        SELECT @MaxTableCountINNER = max(TableTempID)
                          from @DELETED_ROWS

                        SET @CounterINNER = 1

                        — 8) Lets cycle through all the ROWS to be deleted

                        WHILE (@CounterINNER <= @MaxTableCountINNER)

                        BEGIN

                              SELECT @ValueINNER =
                                 ltrim(rtrim([ValueINNER])) 
                              FROM @DELETED_ROWS WHERE
                                TableTempID = @CounterINNER

                              — 9) Lets see if the Foreign Key
                              —    column allows nulls… we will
                              —    use this to guess if the linked
                              —    table is dependant on the
                              —    link (i.e. a link between

                              —    an email and it’s attachments)
                              —    or if it is only a lookup link
                              —    (i.e. a link between a staff 
                              —    member and the email they edited…
                              —    may want to delete the staff
                              —    member but don’t want their
                              —    emails deleted)

                              PRINT ‘====’

                              IF (@Fk_col_IsNullable = 1)

                              BEGIN

                                    — 10a) Set LINKED values to NULL
                                    —      (Using the like statement here
                                    —      should account for INT and
                                    —      VARCHAR PKs)

                                    PRINT ‘  UPDATE [‘ + @Fk_table_name
                                     + ‘] SET [‘ + @Fk_col_name 
                                     + ‘] = NULL WHERE [‘ + @Fk_col_name
                                     + ‘] LIKE ”’ + @ValueINNER + ””

                                    SET @sqlQuery = ‘

SET NOCOUNT OFF

UPDATE [‘ + @Fk_table_name + ‘] SET [‘ + @Fk_col_name + ‘] = NULL WHERE [‘ + @Fk_col_name + ‘] LIKE ”’ + @ValueINNER + ”’

SET NOCOUNT ON’

                                    –PRINT @sqlQuery

                                    EXECUTE sp_executesql @sqlQuery

                              END

                              ELSE

                              BEGIN

                                    — 10b) Delete DEPENDANT values
                                    —      (Using the like statement
                                    —      here should account for INT
                                    —      and VARCHAR PKs)

                                    PRINT ‘  DELETE FROM [‘
                                     + @Fk_table_name + ‘] WHERE [‘
                                     + @Fk_col_name + ‘] LIKE ”’
                                     + @ValueINNER + ””

                                    SET @sqlQuery = ‘

SET NOCOUNT OFF

DELETE FROM [‘ + @Fk_table_name + ‘] WHERE [‘ + @Fk_col_name + ‘] LIKE ”’ + @ValueINNER + ”’

SET NOCOUNT ON’

                                    –PRINT @sqlQuery

                                    EXECUTE sp_executesql @sqlQuery

                              END

                              PRINT ‘====’

                              SET @CounterINNER = @CounterINNER + 1

                        END 

                  END

                  SET @Counter = @Counter + 1

            END

            PRINT ‘—————————————‘

            — 11) Proceed with the delete…

            SET NOCOUNT OFF

            DELETE FROM [Table_1]

                  WHERE [ID] in (SELECT [ID] FROM DELETED)

            SET NOCOUNT ON

            –PRINT ‘DELETE FROM [Table_1]

            —    WHERE [ID] in (SELECT [ID] FROM DELETED)’

      END

      END 

      PRINT ‘COMPLETED – TRIG_PK_Table_1_Delete_FKs’

      PRINT ‘***************************************************’

      SET NOCOUNT OFF

END

GO

It is often necessary to associate multiple rows within one table to a single row within another table, for example several contacts associated with one company, or several categories mapped to one company or contact.

This raises the problem of how to allow your end users to search on multiple items rather than just one, and only display the main route item once.

Let me explain a scenario that you might encounter: –

You have a contacts table, which is linked to a companies table, which is again liked to a keywords table. Any one company can have multiple contacts and multiple keywords associated with it.

Now say you want to allow a multi keyword search against the keywords table, but the search must contain both keywords for the same company and you only want one row per contact to be returned. You may also simple want to display all the keywords associated with a company when viewing a list of contacts.

Traditionally this would have been quite complex to achieve. However with Microsoft SQL 2005 and above’s support for the CROSS APPLY syntax, this becomes a whole lot easier to achieve.

CROSS APPLY

Put simply CROSS APPLY is an INNER or OUTER JOIN between a table and a table-valued function.

A simple example is one commonly shown for the Microsoft AdventureWorks smaple database: –

USE

AdventureWorks
GO

CREATE

FUNCTION
    Sales.fnTopNOrders(
        @CustomerID AS int, @n AS INT) RETURNS TABLE AS RETURN

    SELECT TOP(@n) SalesOrderID, ShipDate = convert(char(10), ShipDate,112),
        TotalDue=convert(varchar,TotalDue,1) 
    FROM AdventureWorks.Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID ORDER BY TotalDue DESC
GO

SELECT
   

StoreName=s.Name, [Top].ShipDate, [Top].SalesOrderID, 
    TotalDue=‘$’+[Top].TotalDue
FROM AdventureWorks.Sales.Store AS s
JOIN AdventureWorks.Sales.Customer AS c
ON s.CustomerID = c.CustomerID
CROSS APPLY
    AdventureWorks.Sales.fnTopNOrders(c.CustomerID, 5) AS [Top]
WHERE CustomerType=‘S’
ORDER BY StoreName, convert(money,TotalDue) DESC
GO

In this sample the table valued function returns the top ‘n’ (largest) orders for a store. The SELECT statement would normally be limited to the stores, but with the CROSS APPLY join in place the store information can be combined (joined) with the results for the top orders (in this case the top 5 orders).

The same result could have been achieved previously by using a temporary table and joining with that, but that can be tedious to code and maintain. Using CROSS APPLY to join the table to the function’s results is much neater and can be quite powerful.

Back to the problem…

So how does CROSS APPLY help us list all our keywords in one column for our company?

Well the answer comes from SQL’s XML features… which can return results as a table-value, with just one row and one column… and we can manipulate the format of the output so we have something nice and neat to work with.

FOR XML Clause

The FOR XML Clause has several modes that can run, which will determine the shape of that resulting XML. These modes are RAW, AUTO, EXPLICIT or PATH.

Without going into too much detail the mode we are interested in to help us with our problem is the PATH mode. So using the FOR XML PATH clause in our query.

The PATH mode provides a simple way to mix elements and attributes, in order to output some XML based on our data (for more in-depth details take a look at http://msdn2.microsoft.com/en-us/library/ms189885.aspx and http://msdn2.microsoft.com/en-us/library/ms190922.aspx)

Now in our problem we are looking to retrieve all the keywords for a company and display them in one column. So lets see what we can get from SQL using the FOR XML Clause

If we simply used the following: –

SELECT Keyword FROM cont_keyword WHERE CompanyID = 19
FOR XML PATH

We would be returned one table with one row that had one column. The contents of which would be something like: –

<

row>
    <Keyword>Reseller</Keyword>
</row>
<row>
    <Keyword>Ireland</Keyword>
</row>

This is not quite what we are looking for so we shall modify the PATH mode clause to exclude the row element: –

SELECT

Keyword FROM cont_keyword WHERE CompanyID = 19
FOR XML PATH(”)  

Now we get an output something like: –

<

Keyword>Reseller</Keyword>
<Keyword>Ireland</Keyword>

Which is getting much closer to something we can use. Now when outputting something as XML in SQL we can tell it to output any column as plain text without any element wrappers. This would be done as follows: –

SELECT

Keyword AS [text()] FROM cont_keyword
WHERE CompanyID = 19 FOR XML PATH(”)

But this isn’t much use as it would just return one string with every keywords joined together.

ResellerIreland

Modifying our query a little more will allow us to separate our keywords: –

SELECT

‘{’ + Keyword + ‘},’ AS [text()]
FROM cont_keyword WHERE CompanyID = 19
FOR XML PATH(”) 

I have chosen to surround my keywords with "{" and "}", this is to allow me to easily search for specific categories in a LIKE statement. If I simply separated them with commas then if there are categories that are similar (for example "Architect", "Structural Architect", etc), I would run into problems when constructing my LIKE clause. The query above would give a result looking something like: –

{Reseller},{Ireland},

Now this is something that we can use… well almost, you’ll notice if you run it, the table column is given a random XML name, as is the table the result is presented in.

SIDE NOTE: – You may have been tempted to use square brackets instead ("[" and "]") but we must remember that these are reserved characters in LIKE statements and would have to be escaped in any subsequent queries.

We could use something like: –

SELECT (SELECT ‘{’ + Keyword + ‘},’ AS [text()]
FROM cont_keyword WHERE CompanyID = 19
FOR XML PATH(”)) AS keywordlist  

If we just need the column to have a legible name, but for our purposes we also need it defined as a table, which is where part of the CROSS APPLY syntax comes in…

Back to the problem

Now we shall use a simple CROSS APPLY join to join our companies table and keywords table-value together.

SELECT
    cont_companies.*, 
    LEFT(kw.keywordlist, LEN(kw.keywordlist) – 1) AS keywords
FROM 
    cont_companies
CROSS APPLY
    (SELECT ‘{’ + Keyword + ‘},’ AS [text()]
        FROM cont_keyword
        WHERE COMPANYID = cont_companies.Company_ID
        FOR XML PATH(”)) kw(keywordlist)

Now in the code above you will notice a few things, first of, we only select all the columns from the companies table, then we actually take the output from our XML table and trim of the last comma character. The CROSS APPLY join is done based on the CompanyID filed, so we will get a list of keywords for each company.

In this sample the keyword output is put into a table called ‘kw’, with the column ‘keywordlist’. ‘keywordlist’ contains the full output from the XML query we built above, which includes an additional comma on the end. In our select statement we disregard this because it is not needed, and should not be displayed at anypoint to end users.

We only select all the columns from the companies table, plus the trimmed keywords column, because we are not interested in any of the other data that will be in the ‘kw’ temporary table, so why return it.

For my purposes I’d actually define this query as a view… you need to watch however, because SQL 2005 Management Studio will tell you that it cannot parse the query if you edit or create this through the designed… Along with a couple of other features (like ‘newsequentialid()’) the SQL 2005 Management Studio isn’t able to parse this type of query and will tell you so.

Because the FOR XML clause will only ever return one row an alternative to using the CROSS APPLY join would be to call the FOR XML clause SELECT twice, for example: –

SELECT

cont_companies.*,
    LEFT((
        SELECT ‘{’ + Keyword + ‘},’ AS [text()]
        FROM cont_keyword
        WHERE CompanyID = cont_companies.Company_ID
        FOR XML PATH(”))
        ,
        LEN((
            SELECT ‘{’ + Keyword + ‘},’ AS [text()]
            FROM cont_keyword
            WHERE CompanyID = cont_companies.Company_ID
            FOR XML PATH(”))
        ) –1 ) AS keywords
FROM cont_companies

Now this will give the same result but would run the FOR XML clause query twice, and if we have several thousand companies with several keywords associated with each, then the overhead of running the FOR XML twice as many times as is needed will soon add up.

Using the CROSS APPLY join should therefore be more efficient and easier to manage.

Update: – 22nd March 2008
======================

On further usage I found that the resulting "keywords" column from both the queries shown above would, on occasion, return the data as binary data. As such they should have a CONVERT statement applied to them in order to correctly return the required data as a TEXT data-type, i.e.: –

SELECT
    cont_companies.*, 
    CONVERT(TEXT,
    LEFT(kw.keywordlist, LEN(kw.keywordlist) – 1)
    ) AS keywords
FROM 
    cont_companies
CROSS APPLY
    (SELECT ‘{’ + Keyword + ‘},’ AS [text()]
        FROM cont_keyword
        WHERE COMPANYID = cont_companies.Company_ID
        FOR XML PATH(”)) kw(keywordlist)

or

SELECT

cont_companies.*,
    CONVERT(TEXT, LEFT((
        SELECT ‘{’ + Keyword + ‘},’ AS [text()]
        FROM cont_keyword
        WHERE CompanyID = cont_companies.Company_ID
        FOR XML PATH(”))
        ,
        LEN((
            SELECT ‘{’ + Keyword + ‘},’ AS [text()]
            FROM cont_keyword
            WHERE CompanyID = cont_companies.Company_ID
            FOR XML PATH(”))
        ) –1 )) AS keywords
FROM cont_companies